cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Level 2 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 ACCEPTED SOLUTION

Accepted Solutions
Level 2 Adventurer

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

...

3 REPLIES 3
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.

Level 2 Adventurer

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

Rahul,

 

Thank you for your suggestions. We went through the configuration exercises as you suggested with no meaningful results.

 

We were able to solve the problem by creating an additional composite index on the attr.numports and bo.id (FK) columns.

Our conclusion is that the ps query planner is not particularly good at using existign indexes for reverse sorts.

 

We intend to license Enterprise-DB and investigate the query hint capabilities.

 

In the meantime we have found a bit of a "silver-bullet" solution in a tool called "dexter" that monitors the pg log for long running queries, analyses them and automatically creates indexes as necessary. It is workign great for us at this time.

 

I take issue with your statement : "It would not be wise to comment on the comparison between Postgresql and Mysql over this query". 

Our experience, verified very carefully, and quite reproduceable, is that out of the box, after installation on identiacl hardware, with no config file changes and add-on tools, the finidng I reported is accurate.

Maybe a more productive approach would be to investigate why pg performs differently, (or IMHO underperforms) in this area and address it in the core product? 

Level 2 Adventurer

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

...