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: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

> Hrm ... are you *certain* that's an 8.4 server?


# psql -U postgres -d db
psql (8.4.4)

db=# select 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;
db=# create index attr_val_contextid on sq_ast_attr_val(contextid);
db=# analyze sq_ast_attr_val;
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-#    assetid, custom_val
db-# FROM
db-#    sq_ast_attr_val
db-# WHERE
db-#    assetid = '62321'
db-#    AND contextid = 0;

Without that index (again with an analyze after doing a rollback):

Postgresql & php tutorials

In response to


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-2017 The PostgreSQL Global Development Group