To: Chris <dmagick@gmail.com>
cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: planner index choice 
In-reply-to: <4C50D07D.4040004@gmail.com> 
References: <4C50D07D.4040004@gmail.com>
Comments: In-reply-to Chris <dmagick@gmail.com>
	message dated "Thu, 29 Jul 2010 10:51:09 +1000"
Date: Wed, 28 Jul 2010 23:53:46 -0400
Message-ID: <628.1280375626@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Chris <dmagick@gmail.com> writes:
> The query:

> SELECT
>    assetid, custom_val
> FROM
>    sq_ast_attr_val
> WHERE
>    attrid IN (SELECT attrid FROM sq_ast_attr WHERE name = 
> 'is_contextable' AND (type_code = 'metadata_field_select' OR 
> owning_type_code = 'metadata_field'))
>    AND contextid = 0
> INTERSECT
> SELECT
>    assetid, custom_val
> FROM
>    sq_ast_attr_val
> WHERE
>    assetid = '62321'
>    AND contextid = 0;

> The explain analyze plan:
> http://explain.depesz.com/s/nWs

Hrm ... are you *certain* that's an 8.4 server?  Because the bit with

	Index Cond: (sq_ast_attr_val.attrid = "outer".attrid)

is a locution that EXPLAIN hasn't used since 8.1, according to a quick
check.  More recent versions don't say "outer".

The actual problem seems to be that choose_bitmap_and() is choosing to
add an indexscan on sq_ast_attr_val_contextid, even though this index
is a lot less selective than the sq_ast_attr_val_attrid scan it had
already picked.  I've seen that behavior before, and there were a series
of patches back in 2006-2007 that seem to have pretty much fixed it.
So that's another reason for suspecting you've got an old server version
there...

			regards, tom lane