Re: planner index choice

Chris <dmagick(at)gmail(dot)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