Simplified Pull List Interface query is very slow

[Chris Sharp]

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

[Chris Sharp (chrissharp123)]

Attaching query.

[Ben Shum (bshum)]

This is what I get when I run that query on our DB: http://pastie.org/10266548

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.

[Mike Rylander (mrylander)]

Chris,

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)