I have the following query
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.