Skip site navigation (1) Skip section navigation (2)

Re: planner index choice

From: Chris <dmagick(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: planner index choice
Date: 2010-07-29 07:29:23
Message-ID: 4C512DD3.9030201@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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/


In response to

Responses

pgsql-performance by date

Next:From: tvDate: 2010-07-29 11:14:54
Subject: Re: planner index choice
Previous:From: Tom LaneDate: 2010-07-29 03:53:46
Subject: Re: planner index choice

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group