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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
