Re: Selectivity of "=" (Re: [HACKERS] Index not used on simple se lect)

Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
> Tom Lane wrote:
>> ... it falls down badly in others, such as where the
>> most common value is NULL or an empty string or some other indication
>> that there's no useful data. In that sort of situation it's actually
>> pretty unlikely that the user will be searching for field =
>> most-common-value ... but the system probably has no way to know that.

> This is exactly what a partial index is supposed to do. And then the
> system knows it...

I've heard a couple of people assert in this thread that partial indexes
are the answer, but I don't believe it. Two reasons:

(1) The system won't use a partial index *at all* unless it can prove
that the index's predicate (condition for including tuples) is implied
by the query's WHERE condition. So the predicate doesn't add a thing
to the system's knowledge about the query.

(2) The statistics that we have available are stats about a column.
Not stats about a column given the predicate of some index. So there's
no gain in our statistical knowledge either.

Partial indexes might be a component of a solution, but they are
very far from being a solution all by themselves.

regards, tom lane

PS: a quick glance at gram.y shows that we don't actually accept
partial-index predicates in CREATE INDEX, so Andreas was right that
the feature got ripped out at some point. I have no idea how much
work might be required to re-enable it... but I'll bet it's not
trivial.