planner index choice

Hi there,

I have a simple query where I don't understand the planner's choice to
use a particular index.

The main table looks like this:

# \d sq_ast_attr_val
                    Table "public.sq_ast_attr_val"
    Column    |         Type          |          Modifiers
-------------+-----------------------+------------------------------
  assetid     | character varying(15) | not null
  attrid      | integer               | not null
  contextid   | integer               | not null default 0
  custom_val  | text                  |
  use_default | character(1)          | not null default '1'::bpchar
Indexes:
     "ast_attr_val_pk" PRIMARY KEY, btree (assetid, attrid, contextid)
     "sq_ast_attr_val_assetid" btree (assetid)
     "sq_ast_attr_val_attrid" btree (attrid)
     "sq_ast_attr_val_concat" btree (((assetid::text || '~'::text) ||
attrid))
     "sq_ast_attr_val_contextid" btree (contextid)


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

I'm not sure why it's picking the sq_ast_attr_val_contextid index to do
the contextid = 0 check, the other parts (attrid/assetid) are much more
selective.

If I drop that particular index:

http://explain.depesz.com/s/zp


All (I hope) relevant postgres info:

Centos 5.5 x86_64 running pg8.4.4.

Server has 8gig memory.

# select name, setting, source from pg_settings where name in
('shared_buffers', 'effective_cache_size', 'work_mem');
          name         | setting
----------------------+--------
shared_buffers        | 262144
effective_cache_size  | 655360
work_mem              | 32768

All planner options are enabled:

# select name, setting, source from pg_settings where name like 'enable_%';
        name        | setting | source
-------------------+---------+---------
  enable_bitmapscan | on      | default
  enable_hashagg    | on      | default
  enable_hashjoin   | on      | default
  enable_indexscan  | on      | default
  enable_mergejoin  | on      | default
  enable_nestloop   | on      | default
  enable_seqscan    | on      | default
  enable_sort       | on      | default
  enable_tidscan    | on      | default

Any insights welcome - thanks!

--
Postgresql & php tutorials
http://www.designmagick.com/