Reports use unoptimizable queries

Bug #1476429 reported by Ryan Finnie
This bug affects 1 person
Affects Status Importance Assigned to Milestone

Bug Description

The report indexes produce SQL which looks like: (individual columns excluded for brevity):

oops_tools=# EXPLAIN SELECT "oops_oops"."id", "oops_infestation"."id" FROM "oops_oops" INNER JOIN "oops_infestation" ON ("oops_oops"."oopsinfestation_id" = "oops_infestation"."id") WHERE "oops_oops"."prefix_id" IN (427, 428) ORDER BY "oops_oops"."date" DESC LIMIT 50;
                                                   QUERY PLAN
 Limit (cost=0.00..17883.34 rows=50 width=16)
   -> Nested Loop (cost=0.00..20191366.09 rows=56453 width=16)
         -> Index Scan Backward using oops_oops_date on oops_oops (cost=0.00..19678489.90 rows=56453 width=16)
               Filter: (prefix_id = ANY ('{427,428}'::integer[]))
         -> Index Scan using oops_infestation_pkey on oops_infestation (cost=0.00..9.07 rows=1 width=4)
               Index Cond: (id = oops_oops.oopsinfestation_id)

It appears postgres does not handle this well (, and the reports effectively don't come back.

For ORDER BY + LIMIT situations, it would be better to do the limit within the app:

oops_tools=# EXPLAIN ANALYZE SELECT "oops_oops"."id", "oops_infestation"."id" FROM "oops_oops" INNER JOIN "oops_infestation" ON ("oops_oops"."oopsinfestation_id" = "oops_infestation"."id") WHERE "oops_oops"."prefix_id" IN (427, 428) ORDER BY "oops_oops"."date" DESC;
                                                                       QUERY PLAN
 Sort (cost=726543.18..726684.31 rows=56453 width=16) (actual time=1248.383..1249.481 rows=12024 loops=1)
   Sort Key:
   Sort Method: quicksort Memory: 948kB
   -> Nested Loop (cost=1334.36..721121.69 rows=56453 width=16) (actual time=12.279..1237.952 rows=12024 loops=1)
         -> Bitmap Heap Scan on oops_oops (cost=1334.36..208245.51 rows=56453 width=16) (actual time=12.256..1143.180 rows=12024 loops=1)
               Recheck Cond: (prefix_id = ANY ('{427,428}'::integer[]))
               -> Bitmap Index Scan on oops_oops_prefix_id (cost=0.00..1320.24 rows=56453 width=0) (actual time=11.337..11.337 rows=12111 loops=1)
                     Index Cond: (prefix_id = ANY ('{427,428}'::integer[]))
         -> Index Scan using oops_infestation_pkey on oops_infestation (cost=0.00..9.07 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=12024)
               Index Cond: (id = oops_oops.oopsinfestation_id)
 Total runtime: 1251.054 ms

Revision history for this message
William Grant (wgrant) wrote :

I don't think it's quite that easy. The latter plan is faster in this case because the most recent oops_oops rows for those prefixes are quite old, which isn't the normal situation. Better would be to convince postgres to use an oops_oops(prefix_id, date) index, which would perform adequately in all cases.

Can we get an EXPLAIN ANALYZE from the first query? Or, better, a dump of the DB.

Revision history for this message
Ryan Finnie (fo0bar) wrote :

I've got an EXPLAIN ANALYZE running in a screen session, and will check back for the results tomorrow. A dump would not be feasible, as it's 135GB.

summary: - Reports use unoptimizable indexes
+ Reports use unoptimizable queries
Revision history for this message
Stuart Bishop (stub) wrote :

In a screen session:

CREATE INDEX CONCURRENTLY oops_oops_prefix_id_date ON oops_oops(prefix_id, date);
ANALYZE oops_oops;

(CONCURRENTLY because this db is live?)

Per what William said, if we can get PostgreSQL to use this index it will work for all these queries, rather than making most of them slower in order to get this edge case working. At the cost of maintaining the index.

There are some other optimization fences we can use too, to avoid PostgreSQL serializing all 56k rows and your app deserializing all 56k rows and tossing most of them away (which is the under the hood behaviour if you are using Python). Is this raw SQL, or which ORM?

Changed in python-oops-tools:
status: New → Confirmed
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.