From: | Joseph Shraibman <jks(at)selectacast(dot)net> |
---|---|
To: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Performance degradation 8.4 -> 9.1 |
Date: | 2011-11-17 20:52:53 |
Message-ID: | 4EC57425.5020803@selectacast.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 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;
>
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)
From | Date | Subject | |
---|---|---|---|
Next Message | J.V. | 2011-11-17 21:32:22 | Re: monitoring sql queries |
Previous Message | Michael Glaesemann | 2011-11-17 20:30:44 | Re: Performance degradation 8.4 -> 9.1 |