Re: [PATCH] Partial indicies almost working (I think)

On Tue, Jul 03, 2001 at 11:36:39PM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > It just occured to me that there is an assumption there that there is no
> > point just trying a straight index scan with no constraints since a
> > sequential scan will always be faster in that case. But with partial
> > indicies that assumption is no longer valid.
>
> You're right, there is nothing that considers the possibility that the
> partial index condition itself might make the use of the index
> desirable. AFAIR, the index condition is only checked to see if the
> index can legally be used. Beyond that, the index subject variables
> (not the condition) have to match some aspect of the WHERE clause or
> query ordering before the planner will expend any cycles to consider
> the index further.

OK, so how to fix this. It seems to me that all that is required is that for
partial indicies it should try to add a path for a plain index scan over the
index and let the cost estimator decide if it's cheaper than a sequential
scan.

The cost would be negligable for the common case (no partial index). The
only thing is, will the cost estimator get it right. At least the partial
index has an accurate count of the number of matching rows.

We currently only have statistics by table, not statistics by index. This
could be problematic since the statistics for the whole table may not apply
to the section the predicate selects. (In fact, you can pretty much
guarentee it for the cases where the partial index is truly useful.)

Hmm, could take a while to get optimal use of these indicies.

--
Martijn van Oosterhout <kleptog(at)svana(dot)org>
http://svana.org/kleptog/
> It would be nice if someone came up with a certification system that
> actually separated those who can barely regurgitate what they crammed over
> the last few weeks from those who command secret ninja networking powers.