DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject
	:content-type:content-transfer-encoding;
	b=vpU9ZqiP6y3IIgPmreqbJh+U2qGHQaFrTHI+TU6zGFukXzXZIKlOjS9OCwhd4tqCqm
	tTnfYn0LJCGooKEbmhWVm2zKXLsP+yubhAiuPjQaDBE06tmOHOC9WpMDzLAvHRswzX7n
	fiJoW30Fow2U8pXiHwIyACz8wJ9ixXNOAOoEk=
Message-ID: <4C50D07D.4040004@gmail.com>
Date: Thu, 29 Jul 2010 10:51:09 +1000
From: Chris <dmagick@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.11) Gecko/20100713 Thunderbird/3.0.6
MIME-Version: 1.0
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: planner index choice
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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/