Thread: Partial indices...

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

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@svana.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

Martijn van Oosterhout <kleptog@svana.org> writes:
> That's very strange.

Not really.  In the dummy example, the two indexes are exactly the same
size and so there is no reason for the planner to prefer one over the
other.  (Note that the two plans have exactly the same estimated cost.)

In a real example, with appropriate statistics from VACUUM or ANALYZE,
the planner would tend to prefer the smaller index.

If we had code to suppress the extra evaluation of the partial-index
condition (which we do not) then possibly the planner would be able
to favor the partial index on the grounds of less computation per row.
I don't see any simple way to suppress those clauses early enough to
let that cost difference figure into planning, though.

            regards, tom lane