From: | Joseph Shraibman <jks(at)selectacast(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Performance degradation 8.4 -> 9.1 |
Date: | 2011-11-17 19:24:41 |
Message-ID: | ja3n1p$1bha$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Glaesemann | 2011-11-17 20:30:44 | Re: Performance degradation 8.4 -> 9.1 |
Previous Message | John R Pierce | 2011-11-17 18:53:52 | Re: connection manager pgpool... help |