From: Chris <dmagick@gmail.com>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: planner index choice
Date: Thu, 29 Jul 2010 10:51:09 +1000
Message-ID: <4C50D07D.4040004@gmail.com> (raw)
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dmagick@gmail.com
Subject: Re: planner index choice
In-Reply-To: <4C50D07D.4040004@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox