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
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) |
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 |