Postgresql 带有偏移限制的 Select 查询太慢了

我从互联网资源中读到，当补偿增加时，查询速度会变慢.但就我而言，我觉得太慢了.我用postgres 9.3

以下是查询(id是主键):

select * from test_table offset 3900000 limit 100;

它会在大约10 seconds分钟内返回数据.我觉得太慢了.我在表中有大约4 million条记录.数据库的总大小为23GB.

机器配置:

RAM: 12 GB
CPU: 2.30 GHz
Core: 10

我更改的postgresql.conf文件中的几个值如下所示.其他都是默认的.

shared_buffers = 2048MB
temp_buffers = 512MB
work_mem = 1024MB
maintenance_work_mem = 256MB
dynamic_shared_memory_type = posix
default_statistics_target = 10000
autovacuum = on
enable_seqscan = off   ## its not making any effect as I can see from Analyze doing seq-scan

除此之外，我还try 了改变random_page_cost = 2.0和cpu_index_tuple_cost = 0.0005的值，结果是一样的.

Explain (analyze, buffers)查询结果如下:

"Limit  (cost=10000443876.02..10000443887.40 rows=100 width=1034) (actual time=12793.975..12794.292 rows=100 loops=1)"
"  Buffers: shared hit=26820 read=378984"
"  ->  Seq Scan on test_table  (cost=10000000000.00..10000467477.70 rows=4107370 width=1034) (actual time=0.008..9036.776 rows=3900100 loops=1)"
"        Buffers: shared hit=26820 read=378984"
"Planning time: 0.136 ms"
"Execution time: 12794.461 ms"

世界各地的人是如何在研究生中讨论这个问题的？任何替代方案都会对我有所帮助.

UPDATE::添加order by id(也try 了其他索引列)，下面是解释:

"Limit  (cost=506165.06..506178.04 rows=100 width=1034) (actual time=15691.132..15691.494 rows=100 loops=1)"
"  Buffers: shared hit=110813 read=415344"
"  ->  Index Scan using test_table_pkey on test_table  (cost=0.43..533078.74 rows=4107370 width=1034) (actual time=38.264..11535.005 rows=3900100 loops=1)"
"        Buffers: shared hit=110813 read=415344"
"Planning time: 0.219 ms"
"Execution time: 15691.660 ms"