Huge difference between ASC and DESC ordering

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux