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/