From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris <dmagick@gmail.com>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: planner index choice
Date: Wed, 28 Jul 2010 23:53:46 -0400
Message-ID: <628.1280375626@sss.pgh.pa.us> (raw)
In-Reply-To: <4C50D07D.4040004@gmail.com>
References: <4C50D07D.4040004@gmail.com>
Chris <dmagick@gmail.com> writes:
> 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
Hrm ... are you *certain* that's an 8.4 server? Because the bit with
Index Cond: (sq_ast_attr_val.attrid = "outer".attrid)
is a locution that EXPLAIN hasn't used since 8.1, according to a quick
check. More recent versions don't say "outer".
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...
regards, tom lane
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: tgl@sss.pgh.pa.us, dmagick@gmail.com
Subject: Re: planner index choice
In-Reply-To: <628.1280375626@sss.pgh.pa.us>
* 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