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.

Agreed.
--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026