Thread: Re: Selectivity of "=" (Re: [HACKERS] Index not used on simple se lect)
Re: Selectivity of "=" (Re: [HACKERS] Index not used on simple se lect)
From
Zeugswetter Andreas IZ5
Date:
> BTW, this argument proves rigorously that the selectivity of a search > for any value other than the MFOV is not more than 0.5, so there is some > basis for my intuition that eqsel should not return a value above 0.5. > So, in the cases where eqsel does not know the exact value being > searched for, I'd still be inclined to cap its result at 0.5. > Yes, this is imho an easy and efficient fix. I would even use a lower value, like 0,3. Good database design would not create an index for such bad selectivity anyway. So if you have a performance problem because of so bad selectivity, the advice is to drop the index. If you plan to store explicit key values, I would do this in an extra statistic, that stores bunches of equally sized buckets, and distinct values for very badly scewed values. Example assuming int index column: from to nrow_estimate 1 100 10005 101 20000 9997 20001 100000 10014 badly scewed values (excluded in above table): val nrow_estimate 1 100000 5 1000000 But imho this is an overkill, and seldom useful. Andreas
> Example assuming int index column: > from to nrow_estimate > 1 100 10005 > 101 20000 9997 > 20001 100000 10014 > > badly scewed values (excluded in above table): > val nrow_estimate > 1 100000 > 5 1000000 > > But imho this is an overkill, and seldom useful. Yes, some commerical databases do this, though it is of questionable value. -- 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