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

> Thomas Lockhart <lockhart@alumni.caltech.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@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026