Comment 5 for bug 1394426

PostgreSQL thinks that for an infestation_id this common, it will find the most recent one quicky by walking the date index backwards. This is something that PostgreSQL's query planner often gets wrong.

As Robert says, an index on (infestation_id, date) should give PostgreSQL what it needs to run this query efficiently. If we create this index, we can drop the existing oops_oops_oopsinfestation_id index.

Alternatively, we can also use the optimization fence of the WITH clause to rewrite the query and force it to choose a more stable plan using the existing oopsinfestation_id index :

WITH oops AS (
    SELECT * FROM oops_oops WHERE oops_oops.oopsinfestation_id = 6944977
)
SELECT * FROM oops ORDER BY date DESC LIMIT 1