Re: planner index choice

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