Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-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

pgsql-bugs by date

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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group