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

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
>> I can pretty much guarantee that the cost estimator will *not* get it
>> right --- it has no idea about partial indexes.

> See my other email. All I've done is let the normal estimator estimate the
> number of rows based on the whole table and then capped it if it comes out
> more than the number of rows in the index.

To put it politely, that's a crock; besides which it has no effect on
the estimation of the access costs for the index path. The correct fix
has to be in genericcostestimate() in selfuncs.c.

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.

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.

regards, tom lane