Reports use unoptimizable queries

[Ryan Finnie]

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 (http://thebuild.com/blog/2014/11/18/when-limit-attacks/), 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: oops_oops.date
   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

[William Grant (wgrant)]

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.

[Ryan Finnie (fo0bar)]

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.

[Stuart Bishop (stub)]

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?