From: | Anthony Presley <anthony(at)resolution(dot)com> |
---|---|
To: | "Pgsql-Performance ((((E-mail))))" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Why is it choosing a different plan? |
Date: | 2006-09-22 22:59:16 |
Message-ID: | 1158965956.8070.12.camel@puma |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi all, I'm having some confusion with the 7.4 query planner.
I have two identical queries, whereby the passed (varchar) parameter
appears to be the deciding factor between a sequential or an index scan.
IE, This query:
explain SELECT DISTINCT (a1.ENTRY_ID) AS retrieved FROM OS_CURRENTSTEP
AS a1 , OS_CURRENTSTEP AS a2 WHERE a1.ENTRY_ID = a1.ENTRY_ID AND
a1.ENTRY_ID = a2.ENTRY_ID AND ( a1.OWNER = 'p1' AND a2.STEP_ID =
1 );
NOTICE: QUERY PLAN:
Unique (cost=1175.88..1175.88 rows=1 width=16)
-> Sort (cost=1175.88..1175.88 rows=1 width=16)
-> Nested Loop (cost=0.00..1175.87 rows=1 width=16)
-> Index Scan using idx_9 on os_currentstep a1
(cost=0.00..1172.45 rows=1 width=8)
-> Index Scan using idx_8 on os_currentstep a2
(cost=0.00..3.41 rows=1 width=8)
However, this query:
explain SELECT DISTINCT (a1.ENTRY_ID) AS retrieved FROM OS_CURRENTSTEP
AS a1 , OS_CURRENTSTEP AS a2 WHERE a1.ENTRY_ID = a1.ENTRY_ID AND
a1.ENTRY_ID = a2.ENTRY_ID AND ( a1.OWNER = 'GIL' AND a2.STEP_ID =
1 );
NOTICE: QUERY PLAN:
Unique (cost=3110.22..3110.22 rows=1 width=16)
-> Sort (cost=3110.22..3110.22 rows=1 width=16)
-> Nested Loop (cost=0.00..3110.21 rows=1 width=16)
-> Seq Scan on os_currentstep a1 (cost=0.00..3106.78
rows=1 width=8)
-> Index Scan using idx_8 on os_currentstep a2
(cost=0.00..3.41 rows=1 width=8)
Thoughts about why changing OWNER from 'p1' to 'GIL' would go from an
Index Scan to a Sequential?
[There is an index on os_currentstep, and it was vacuum analyze'd
recently.]
Running version 7.4 (working on upgrading to 8.0 soon). Thanks!
--
Anthony
From | Date | Subject | |
---|---|---|---|
Next Message | Anthony Presley | 2006-09-22 23:58:53 | Re: Why is it choosing a different plan? |
Previous Message | mark | 2006-09-22 22:36:17 | Re: Opteron vs. Xeon "benchmark" |