Partial indices...

From: Dmitry Tkach <dmitry(at)openratings(dot)com>
To: pgsql-bugs(at)postgresql(dot)org, pg_general <pgsql-general(at)postgresql(dot)org>
Subject: Partial indices...
Date: 2003-10-10 19:26:13
Message-ID: 3F8707D5.2090401@openratings.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

Hi, everybody!

I am getting some weird behaviour trying to use a partial index in 7.3:

testdb=# create table a (x int, y int, z int);
CREATE
testdb=# create index a_idx on a(x,y) where z is null;
CREATE
testdb=# create index b_idx on a (x,y);
CREATE
testdb=# explain select * from a where x=1 and y=2 and z is null;
QUERY PLAN
----------------------------------------------------------------
Index Scan using b_idx on a (cost=0.00..4.83 rows=1 width=12)
Index Cond: ((x = 1) AND (y = 2))
Filter: (z IS NULL)
(3 rows)

Any idea, why is it using b_idx with a filter, instead of going straight
for a_idx?
Another thing is, if I drop b_idx, it then starts using a_idx, but
*still* has that 'Filter:' thing in the query plan...
I understand, that the latter doesn't hurt much... but the former
*does*, because in my "real life" app, (much) less then half of entries
are non-null.... :-(

Thanks!

Dima

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Reece Hart 2003-10-11 00:35:30 'pg_class_aclcheck: relation <oid> not found' with temp tables in plpgsql
Previous Message Gavin Scott 2003-10-10 18:49:29 ECPG - Keywords as variable names (7.4 regression)

Browse pgsql-general by date

  From Date Subject
Next Message Mike Leahy 2003-10-10 19:41:06 Unable to identify an operator '*=' for types 'character varying[]' and '"unknown"'
Previous Message Network Administrator 2003-10-10 19:07:30 Re: Interfaces that support cursors