Re: Why is it choosing a different plan? - Mailing list pgsql-performance

From Anthony Presley
Subject Re: Why is it choosing a different plan?
Date
Msg-id 1158969533.8070.19.camel@puma
Whole thread Raw
In response to Why is it choosing a different plan?  (Anthony Presley <anthony@resolution.com>)
Responses Re: Why is it choosing a different plan?
List pgsql-performance
I thought this was related to the TYPE (ie, I could cast it using
something like: attr1=1::int8).  However, I tried a few more values, and
the query planner is confusing me.

With these values, in the owner, I get a Seq Scan:
    'GIL', '1122', '2305'

With these values, in the owner, I get an Index Scan:
    'p1', 'p2', '2300', '8088', 'CHANGEINVENTION'

The os_currentstep table has about 119,700 rows in it -- and I can't do
too much to actually change the query, since it's coming from something
of a 'black box' application.

Thoughts?

--
Anthony

On Fri, 2006-09-22 at 17:59 -0500, Anthony Presley wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org


pgsql-performance by date:

Previous
From: Anthony Presley
Date:
Subject: Why is it choosing a different plan?
Next
From: Anthony Presley
Date:
Subject: Re: Why is it choosing a different plan?