DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=ZqI7aBjcFCi3GxAtU4fzpmW8Q4vr0vw4+rnpGIYG9onlWWp3dy1TRddn3JVMdS8hGX
	psxsyRsK3qdEXkfEGQoZSBoFXgRR4dAywD8shzhIpJfDSizbyfYryY/XaVCxqhZhRq8b
	ti6ZcF5PJzJ93kodEA/Ik5FLOhi1VCTm+MGYQ=
Message-ID: <4C512DD3.9030201@gmail.com>
Date: Thu, 29 Jul 2010 17:29:23 +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: Tom Lane <tgl@sss.pgh.pa.us>
CC: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: planner index choice
References: <4C50D07D.4040004@gmail.com> <628.1280375626@sss.pgh.pa.us>
In-Reply-To: <628.1280375626@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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/