Thread: Performance degradation 8.4 -> 9.1
This query is taking much longer on 9.1 than it did on 8.4. Why is it
using a seq scan?
=> explain verbose SELECT status,EXISTS(SELECT 1 FROM eventlog e WHERE
e.uid = ml.uid AND e.jobid = ml.jobid AND type = 4),EXISTS(SELECT 1 FROM
eventlog e WHERE e.uid = ml.uid AND e.jobid = ml.jobid AND type = 1)
FROM maillog ml WHERE jobid IN(1132730);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using maillog_jobid_status_key on public.maillog ml
(cost=0.00..120373618.25 rows=338943 width=10)
Output: ml.status, (alternatives: SubPlan 1 or hashed SubPlan 2),
(SubPlan 3)
Index Cond: (ml.jobid = 1132730)
SubPlan 1
-> Index Scan using eventlog_uid_and_jobid_and_type_key on
public.eventlog e (cost=0.00..176.66 rows=1 width=0)
Index Cond: ((e.uid = ml.uid) AND (e.jobid = ml.jobid) AND
(e.type = 4))
SubPlan 2
-> Seq Scan on public.eventlog e (cost=0.00..32897949.27
rows=17535360 width=8)
Output: e.uid, e.jobid
Filter: (e.type = 4)
SubPlan 3
-> Index Scan using eventlog_uid_and_jobid_and_type_key on
public.eventlog e (cost=0.00..176.66 rows=1 width=0)
Index Cond: ((e.uid = ml.uid) AND (e.jobid = ml.jobid) AND
(e.type = 1))
(13 rows)
=> select version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
(1 row)
On Nov 17, 2011, at 14:24, 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?
Without seeing the table definition (including indexes) as well as the output of EXPLAIN for 8.4, it's kind of hard to
say.
Does this formulation of the query give you a different plan?
SELECT status,
e4.type IS NOT NULL,
e1.type IS NOT NULL
FROM maillog ml
LEFT JOIN eventlog e4 ON (e4.uid, e4.jobid) = (ml.uid, ml.jobid)
AND e4.type = 4
LEFT JOIN eventlog e1 ON (e1.uid, e1.jobid) = (ml.uid, ml.jobid)
AND e1.type = 1
WHERE jobid = 1132730;
Michael Glaesemann
grzm seespotcode net
On 11/17/2011 03:30 PM, Michael Glaesemann wrote:
>
> On Nov 17, 2011, at 14:24, 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?
>
> Without seeing the table definition (including indexes) as well as the output of EXPLAIN for 8.4, it's kind of hard
tosay.
>
> Does this formulation of the query give you a different plan?
>
> SELECT status,
> e4.type IS NOT NULL,
> e1.type IS NOT NULL
> FROM maillog ml
> LEFT JOIN eventlog e4 ON (e4.uid, e4.jobid) = (ml.uid, ml.jobid)
> AND e4.type = 4
> LEFT JOIN eventlog e1 ON (e1.uid, e1.jobid) = (ml.uid, ml.jobid)
> AND e1.type = 1
> WHERE jobid = 1132730;
>
It does, but still not the right plan. I want pg to use the plan I
posted, minus the seqscan. It estimates that subplan 1 is faster than
subplan 2 and they both would give the same results, so why is it
running subplan 2?
BTW setting enable_seqscan = false on the original doens't solve my
problem, I get this instead which is still slow.
=> explain verbose
owl-> SELECT status , --dsn,servername,software,serverip,ip,pod,format,
owl-> EXISTS(SELECT 1 FROM eventlog e WHERE e.uid = ml.uid AND e.jobid =
ml.jobid AND type = 4),
owl-> EXISTS(SELECT 1 FROM eventlog e WHERE e.uid = ml.uid AND e.jobid =
ml.jobid AND type = 1) FROM maillog ml WHERE jobid IN(1132730);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using maillog_jobid_status_key on public.maillog ml
(cost=0.00..120407480.20 rows=338951 width=10)
Output: ml.status, (alternatives: SubPlan 1 or hashed SubPlan 2),
(SubPlan 3)
Index Cond: (ml.jobid = 1132730)
SubPlan 1
-> Index Scan using eventlog_uid_and_jobid_and_type_key on
public.eventlog e (cost=0.00..176.71 rows=1 width=0)
Index Cond: ((e.uid = ml.uid) AND (e.jobid = ml.jobid) AND
(e.type = 4))
SubPlan 2
-> Bitmap Heap Scan on public.eventlog e
(cost=21708484.94..43874627.61 rows=17541300 width=8)
Output: e.uid, e.jobid
Recheck Cond: (e.type = 4)
-> Bitmap Index Scan on eventlog_jobid_type_type
(cost=0.00..21704099.62 rows=17541300 width=0)
Index Cond: (e.type = 4)
SubPlan 3
-> Index Scan using eventlog_uid_and_jobid_and_type_key on
public.eventlog e (cost=0.00..176.71 rows=1 width=0)
Index Cond: ((e.uid = ml.uid) AND (e.jobid = ml.jobid) AND
(e.type = 1))
(15 rows)
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@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
On 18 Listopad 2011, 11:39, Greg Smith wrote: > 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 And (g) EXPLAIN ANALYZE plans for the queries - if possible from both versions. Use explain.depesz.com to post it. Tomas
More info: I upgraded the database from 8.4 to 9.1 using pg_upgrade, so I have no way of running explain using 8.4. I don't want to do an EXPLAIN ANALYZE because it would bog down the server for too long. I know what it is doing, it's doing a seqscan. This is a table with ~ 5.5 million rows and is ~100G in size. There are 4 indexes on this table, including one that matches what this query needs exactly. Pg finds this index, but goes with the other alternative (2), even though it thinks the index alternative (1) will be faster. I don't even know what that means. I've never seen an EXPLAIN like that before the 9.1 upgrade. I searched for "alternative" in the docs but didn't find anything.