select * from "JOB_MEMORY_STORAGE" st inner join "JOB_MEMORY" s on s.fk_id_storage = st.id where st.fk_id_client = 20045 order by s.id asc limit 50
which takes 90 seconds to finish. JOB_MEMORY has 45 million rows, JOB_MEMORY_STORAGE has 50 000 rows.
Query plan:
Limit (cost=0.98..1971.04 rows=50 width=394) (actual time=93357.197..93357.654 rows=50 loops=1)
-> Nested Loop (cost=0.98..344637384.09 rows=8746875 width=394) (actual time=93357.194..93357.584 rows=50 loops=1)
-> Index Scan Backward using "JOB_MEMORY_id_desc" on "JOB_MEMORY" s (cost=0.56..113858938.25 rows=45452112 width=164) (actual time=0.059..18454.332 rows=18883917 loops=1)
-> Index Scan using "JOB_MEMORY_STORAGE_pkey" on "JOB_MEMORY_STORAGE" st (cost=0.41..5.07 rows=1 width=222) (actual time=0.002..0.002 rows=0 loops=18883917)
Index Cond: (id = s.fk_id_storage)
Filter: (fk_id_client = 20045)
Rows Removed by Filter: 1
Planning time: 1.932 ms
Execution time: 93357.745 ms
As you can see, it is indeed using an index JOB_MEMORY_id_desc in a backward direction, but it is very slow.
When I change ordering to desc in the query, the query finishes immediately and the query plan is
Limit (cost=0.98..1981.69 rows=50 width=394) (actual time=37.577..37.986 rows=50 loops=1)
-> Nested Loop (cost=0.98..344613154.25 rows=8699235 width=394) (actual time=37.575..37.920 rows=50 loops=1)
-> Index Scan using "JOB_MEMORY_id_desc" on "JOB_MEMORY" s (cost=0.56..113850978.19 rows=45448908 width=165) (actual time=0.013..5.117 rows=6610 loops=1)
-> Index Scan using "JOB_MEMORY_STORAGE_pkey" on "JOB_MEMORY_STORAGE" st (cost=0.41..5.07 rows=1 width=221) (actual time=0.003..0.003 rows=0 loops=6610)
Index Cond: (id = s.fk_id_storage)
Filter: (fk_id_client = 20045)
Rows Removed by Filter: 1
Planning time: 0.396 ms
Execution time: 38.058 ms
There is also an index on JOB_MEMORY.id. I also tried a composite index on (fk_id_storage, id), but it did not help (and was not actually used).
I ran ANALYZE on both tables.
Postgres 9.6.2, Ubuntu 14.04, 192 GB RAM, SSD, shared_buffers = 8196 MB.
How can I help Postgres execute the query with asc ordering as fast as the one with desc?
Thank you.
View this message in context: Huge difference between ASC and DESC ordering
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
