From: | Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>, Zeugswetter Andreas IZ5 <Andreas(dot)Zeugswetter(at)telecom(dot)at>, pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: Selectivity of "=" (Re: [HACKERS] Index not used on simple se lect) |
Date: | 1999-07-29 13:46:27 |
Message-ID: | 199907291346.JAA07179@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1999-07-29 13:48:24 | Re: [BUGS] [Keystone Slip # 14] Building pristine source on RedHat Alpha6.0 |
Previous Message | The Hermit Hacker | 1999-07-29 12:52:49 | Re: [HACKERS] Off-topic: autoconf guru |