cancel
Showing results for 
Search instead for 
Did you mean: 

Query Performance 1000x slower with SORT DES (reverse sort) on primary key

Adventurer

Query Performance 1000x slower with SORT DES (reverse sort) on primary key

I have a very simple query:

 

select *
from
bo_instance i
left join attrib_touchstone attr on i.id = attr.bo_instance_id
where
i.is_deleted = false
and i.bo_class_id = 34
and attr.num_ports = 3
order by i.ID
limit 80;

The queryplan:

Limit (cost=0.86..7063.06 rows=80 width=3438) (actual time=0.457..0.819 rows=80 loops=1)
-> Nested Loop (cost=0.86..11873941.84 rows=134507 width=3438) (actual time=0.456..0.783 rows=80 loops=1)
-> Index Scan using attrib_touchstone_bo_instance_id_idx on attrib_touchstone attr (cost=0.43..10863529.48 rows=138582 width=1664) (actual time=0.444..0.498 rows=80 loops=1)
Filter: (num_ports = 3)
Rows Removed by Filter: 393
-> Index Scan using bo_instance_pkey on bo_instance i (cost=0.43..7.29 rows=1 width=1766) (actual time=0.002..0.002 rows=1 loops=80)
Index Cond: (id = attr.bo_instance_id)
Filter: ((NOT is_deleted) AND (bo_class_id = 34))
Planning time: 0.420 ms
Execution time: 0.883 ms

The two tables have less than 5M records each and this executes in less than 0.2 seconds. All fields in teh where clause are indexed, and of course the PKs and FKs are indexed.

However - when the SAME query is executed with only the "order by" switched to "DESC" - the query takes 15 minutes to execute.

 

select *
from
bo_instance i
left join attrib_touchstone attr on i.id = attr.bo_instance_id
where
i.is_deleted = false
and i.bo_class_id = 34
and attr.num_ports = 3
order by i.ID desc
limit 80;

and the analyze:

Limit (cost=0.86..7063.06 rows=80 width=3438) (actual time=775250.581..775260.877 rows=80 loops=1)
-> Nested Loop (cost=0.86..11873941.84 rows=134507 width=3438) (actual time=775250.095..775260.336 rows=80 loops=1)
-> Index Scan Backward using attrib_touchstone_bo_instance_id_idx on attrib_touchstone attr (cost=0.43..10863529.48 rows=138582 width=1664) (actual time=775233.936..775242.162 rows=80 loops=1)
Filter: (num_ports = 3)
Rows Removed by Filter: 5110058
-> Index Scan using bo_instance_pkey on bo_instance i (cost=0.43..7.29 rows=1 width=1766) (actual time=0.165..0.165 rows=1 loops=80)
Index Cond: (id = attr.bo_instance_id)
Filter: ((NOT is_deleted) AND (bo_class_id = 34))
Planning time: 0.481 ms
Execution time: 775262.180 ms




 This problem does NOT exist in mySQL - we laoded the same data in a mySQL instance and the perofrmance regardless of sort order is essetially identical.

 

Tags (2)
1 REPLY 1
Highlighted
EDB Team Member

Re: Query Performance 1000x slower with SORT DES (reverse sort) on primary key

Hi dejannenov,

 

Based on your query and the explain, analyse, our comments are as below

Can you check the work_mem and the version of Postgresql.
We assumed that generation of temp files are high. Hence, we request to gradually increasing the work_mem at session level and verifying the temp file generation. Also check if all the stats are updated for the table along with the bloat.
There is difference in the rows filtered in both the case. So this suggests indexes to be created on the columns from where clause, which are created as stated.

It would not be wise to comment on the comparison between Postgresql and Mysql over this query as there will be multiple factors that can affect query performance.

Kindly let us know the query performance once above testing is completed.