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

On Thu, Jul 05, 2001 at 10:44:18AM -0400, Tom Lane wrote:
> One possibility is for genericcostestimate() to multiply the initial
> indexSelectivity estimate (which considers only the WHERE clauses about
> the indexed variable) by index->tuples/rel->tuples (the selectivity of
> the index predicate). This would be fully correct only if the index
> predicate is independent of the indexed variable, which could be
> completely wrong. That could lead to underestimating the access costs
> for the index ... on the other hand, if a partial index is applicable,
> we probably want the system to use it, so underestimating is better than
> overestimating.

Ah, I see what you mean. I thought that the indexSelectivity referred to the
number of index tuples that matched as a proportion of all index tuples, not
the tuples in the main table. The rest of the code there definitly assumes
index->tuples == rel->tuples.

I'll look into it.

> Another approach would be to compute indexSelectivity based on the AND
> of the given indexQuals and the index predicate. That would rely on
> clause_selectivity to recognize overlapping/redundant conditions, which
> it is not very good at, but it'll get some simple cases right (and the
> infrastructure is there to get more cases right). Offhand this seems
> like the best way to go in the long term.

Yes, my test had the two columns independant, thus the where clause produced
correct results.

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