From: Chris <dmagick@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: planner index choice
Date: Thu, 29 Jul 2010 17:29:23 +1000
Message-ID: <4C512DD3.9030201@gmail.com> (raw)
In-Reply-To: <628.1280375626@sss.pgh.pa.us>
References: <4C50D07D.4040004@gmail.com>
<628.1280375626@sss.pgh.pa.us>
Hi,
> Hrm ... are you *certain* that's an 8.4 server?
Yep.
# psql -U postgres -d db
psql (8.4.4)
db=# select version();
version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit
(1 row)
> 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...
I just recreated the index and re-ran the explain analyze and it doesn't
give the "outer" bit any more - not sure how I got that before.
db=# begin;
BEGIN
db=# create index attr_val_contextid on sq_ast_attr_val(contextid);
CREATE INDEX
db=# analyze sq_ast_attr_val;
ANALYZE
db=# explain analyze SELECT
db-# assetid, custom_val
db-# FROM
db-# sq_ast_attr_val
db-# WHERE
db-# attrid IN (SELECT attrid FROM sq_ast_attr WHERE name =
db(# 'is_contextable' AND (type_code = 'metadata_field_select' OR
db(# owning_type_code = 'metadata_field'))
db-# AND contextid = 0
db-# INTERSECT
db-# SELECT
db-# assetid, custom_val
db-# FROM
db-# sq_ast_attr_val
db-# WHERE
db-# assetid = '62321'
db-# AND contextid = 0;
http://explain.depesz.com/s/br9
Without that index (again with an analyze after doing a rollback):
http://explain.depesz.com/s/gxH
--
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, tgl@sss.pgh.pa.us
Subject: Re: planner index choice
In-Reply-To: <4C512DD3.9030201@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