Re: planner index choice

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/