Simplified Pull List Interface query is very slow

Bug #1469758 reported by Chris Sharp
This bug affects 1 person
Affects Status Importance Assigned to Milestone

Bug Description

The query for the Simplified Pull List interface is running very slow on our system. I will begin with the assumption that this is not a local issue, but an Evergreen bug. I'm attaching the EXPLAIN ANALYZE query and output for analysis.

(Note: this does not appear to be helped by the fix to bug 971989)

Evergreen 2.7.2
OpenSRF 2.4.0
PostgreSQL 9.3
Ubuntu LTS

Tags: performance
Revision history for this message
Chris Sharp (chrissharp123) wrote :
Revision history for this message
Chris Sharp (chrissharp123) wrote :

Attaching query.

Revision history for this message
Ben Shum (bshum) wrote :

This is what I get when I run that query on our DB:

One difference I see between mine and csharp's right away is that he has "Seq Scan on copy acp" while I have "Index Scan using copy_pkey on copy acp". That alone takes up considerable time on his system as it polls through 11+ million items. There may be other cases where the query plan isn't using the indexes right.

Changed in evergreen:
status: New → Triaged
Revision history for this message
Mike Rylander (mrylander) wrote :


Ben's on the right track, but it's not your index definitions, just your stats. Your bad stats are telling PG that they'll get an order of magnitude (809k vs 60k) more rows via hold_request_current_copy_before_cap_idx than is true. Increasing the stats target on action.hold_request and forcing an analyze of the table may help.

For reference, here are the lines from your paste showing the bad estimate:

-> Bitmap Heap Scan on hold_request ahr (cost=13731.63..259724.63 rows=766057 width=189) (actual time=18.921..146.235 rows=38241 loops=1)
  Recheck Cond: ((capture_time IS NULL) AND (cancel_time IS NULL))
  Filter: ((expire_time IS NULL) OR (expire_time > now()))
  Rows Removed by Filter: 24
    -> Bitmap Index Scan on hold_request_current_copy_before_cap_idx (cost=0.00..13540.12 rows=809267 width=0) (actual time=16.097..16.097 rows=59865 loops=1)

tags: added: performance
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.