Re: Performance degradation 8.4 -> 9.1

On 11/17/2011 02:24 PM, Joseph Shraibman wrote:
> This query is taking much longer on 9.1 than it did on 8.4. Why is it
> using a seq scan?
>

To answer that question in all cases, it's necessary to know a) the
query, b) the PostgreSQL version, c) the table definitions including
what indexes exist, d) the statistics collected about each column, (e)
the sizes of all the indexes on any referenced table, and (f) the server
parameters. Sometimes you can get useful feedback from just the first
three of those, but no one call guess you why an index is or isn't being
used without at least knowing the indexes that are defined. For
example, it looks like the query is using an index on
(eventlog_uid,jobid,type). It probably wants an index on jobid instead,
but I can't tell whether you don't have one, or if one is there but it's
not being used for some reason.

How did you build the 9.1 system from the 8.4 data? There might just be
a physical difference between the two tables.

In addition to showing the table definition, two other suggestions:

-Show what the better plan on 8.4 looks like, we're just seeing the slow one

-Try running the individual EXISTS parts of this plan on both versions
and compare. You might be able to isolate which of them is the source
of the difference here.

There's a longer guide to the things people tend to find useful at
http://wiki.postgresql.org/wiki/SlowQueryQuestions ; this question might
get a better response on the lower volume pgsql-performance mailing list
too.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us