Re: [GENERAL] Partial indices...

That's very strange. However, since the tables in question are empty you
could get strange results like this. How many rows does your real app have.
Can you show the explain for each index on your real app?

Hope this helps,

On Fri, Oct 10, 2003 at 03:26:13PM -0400, Dmitry Tkach wrote:
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato