Postgresql 9.4 is extremely slow while performing select. Example:
explain analyze select * from ntable order by id desc limit 20 offset 3000000;
QUERY PLAN:
Limit (cost=122484.28..122485.10 rows=20 width=43) (actual time=1132.152..1132.160 rows=20 loops=1)
-> Index Scan using ntable_id_desc_index on ntable (cost=0.43..220527.42 rows=5401373 width=43) (actual time=0.021..1007.365 rows=3000020 loops=1)
Planning time: 0.273 ms
Execution time: 1132.200 ms
Total number of records in table:
count
---------
5401521
(1 row)
Indexes:
"ntable_pkey" PRIMARY KEY, btree (id)
"ntable_id_desc_index" btree (id DESC)
Server has 24GB of ram, and 16 cores, settings in postgresql.conf:
max_connections = 400
shared_buffers = 3584MB
effective_cache_size = 10752MB
work_mem = 9175kB
maintenance_work_mem = 896MB
checkpoint_segments = 32
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
Please, help increase performance of such queries, thanks!
