Comment 3 for bug 1394426

There is an index on oopsinfestation_id already. The problem seems to be postgres ignores the index if things like LIMIT 1 are in place and it thinks the index is unnecessary for performance. From what I gather from that stackoverflow post, anyway.

Compare the following. LIMIT 1 and LIMIT 3 take ages, LIMIT 50 is almost immediate:

oops_tools=# EXPLAIN SELECT "oops_oops"."id", "oops_oops"."oopsinfestation_id", "oops_oops"."pathname", "oops_oops"."oopsid", "oops_oops"."prefix_id", "oops_oops"."pageid", "oops_oops"."date", "oops_oops"."url", "oops_oops"."http_method", "oops_oops"."duration", "oops_oops"."referrer", "oops_oops"."user_agent", "oops_oops"."most_expensive_statement", "oops_oops"."total_time", "oops_oops"."time_is_estimate", "oops_oops"."informational", "oops_oops"."appinstance_id", "oops_oops"."is_bot", "oops_oops"."is_local_referrer", "oops_oops"."classification_id", "oops_oops"."statements_count" FROM "oops_oops" WHERE "oops_oops"."oopsinfestation_id" = 6944977 ORDER BY "oops_oops"."date" DESC LIMIT 1;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Limit (cost=0.00..2625.76 rows=1 width=322)
   -> Index Scan Backward using oops_oops_date on oops_oops (cost=0.00..35846899.53 rows=13652 width=322)
         Filter: (oopsinfestation_id = 6944977)
(3 rows)

oops_tools=# EXPLAIN SELECT "oops_oops"."id", "oops_oops"."oopsinfestation_id", "oops_oops"."pathname", "oops_oops"."oopsid", "oops_oops"."prefix_id", "oops_oops"."pageid", "oops_oops"."date", "oops_oops"."url", "oops_oops"."http_method", "oops_oops"."duration", "oops_oops"."referrer", "oops_oops"."user_agent", "oops_oops"."most_expensive_statement", "oops_oops"."total_time", "oops_oops"."time_is_estimate", "oops_oops"."informational", "oops_oops"."appinstance_id", "oops_oops"."is_bot", "oops_oops"."is_local_referrer", "oops_oops"."classification_id", "oops_oops"."statements_count" FROM "oops_oops" WHERE "oops_oops"."oopsinfestation_id" = 6944977 ORDER BY "oops_oops"."date" DESC LIMIT 3;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Limit (cost=0.00..7877.29 rows=3 width=322)
   -> Index Scan Backward using oops_oops_date on oops_oops (cost=0.00..35846899.53 rows=13652 width=322)
         Filter: (oopsinfestation_id = 6944977)
(3 rows)

oops_tools=# EXPLAIN SELECT "oops_oops"."id", "oops_oops"."oopsinfestation_id", "oops_oops"."pathname", "oops_oops"."oopsid", "oops_oops"."prefix_id", "oops_oops"."pageid", "oops_oops"."date", "oops_oops"."url", "oops_oops"."http_method", "oops_oops"."duration", "oops_oops"."referrer", "oops_oops"."user_agent", "oops_oops"."most_expensive_statement", "oops_oops"."total_time", "oops_oops"."time_is_estimate", "oops_oops"."informational", "oops_oops"."appinstance_id", "oops_oops"."is_bot", "oops_oops"."is_local_referrer", "oops_oops"."classification_id", "oops_oops"."statements_count" FROM "oops_oops" WHERE "oops_oops"."oopsinfestation_id" = 6944977 ORDER BY "oops_oops"."date" DESC LIMIT 50;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Limit (cost=46878.53..46878.66 rows=50 width=322)
   -> Sort (cost=46878.53..46912.66 rows=13652 width=322)
         Sort Key: date
         -> Index Scan using oops_oops_oopsinfestation_id on oops_oops (cost=0.00..46425.02 rows=13652 width=322)
               Index Cond: (oopsinfestation_id = 6944977)
(5 rows)