Subject: Why is it choosing a different plan?
From: Anthony Presley <anthony@resolution.com>
To: "Pgsql-Performance ((((E-mail))))" <pgsql-performance@postgresql.org>
Content-Type: text/plain
Date: Fri, 22 Sep 2006 17:59:16 -0500
Message-Id: <1158965956.8070.12.camel@puma>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
X-resolutionsystems-MailScanner-Information: Please contact the ISP for more
	information
X-resolutionsystems-MailScanner: Found to be clean
X-resolutionsystems-MailScanner-SpamCheck: not spam (whitelisted),
	SpamAssassin (not cached, timed out)
X-resolutionsystems-MailScanner-From: anthony@resolution.com
X-PTI-MailScanner-Information: Please contact the ISP for more information
X-PTI-MailScanner: Found to be clean
X-PTI-MailScanner-SpamCheck: not spam (whitelisted),
	SpamAssassin (not cached, score=-1.799, required 4,
	autolearn=not spam, ALL_TRUSTED -1.80, BAYES_50 0.00,
	UPPERCASE_25_50 0.00)
X-MailScanner-From: anthony@resolution.com
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200609/312
X-Sequence-Number: 20998

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