Comment 6 for bug 1394426

Revision history for this message
William Grant (wgrant) wrote : Re: [Bug 1394426] Re: oopses part of large infestations take forever to load

On 20/11/14 22:25, Stuart Bishop wrote:
> 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

That will perform terribly for very common infestations (eg. Launchpad
timeouts).