Re: BUG #11500: PRIMARY KEY index not being used

On 9/26/14 2:02 PM, Marti Raudsepp wrote:
> On Fri, Sep 26, 2014 at 11:02 AM, <marko(at)joh(dot)to> wrote:
>> The statistics say that
>> there are no rows where processed=0 (and it's not far from the truth), but
>> it's still a risky plan compared to the PK lookup.
>
>> Any thoughts?
>
> PostgreSQL 9.0 introduced this optimization for greater/less operators:
>
>> When looking up statistics for greater/less-than comparisons, if the
>> comparison value is in the first or last histogram bucket, use an index
>> (if available) to fetch the current actual column minimum or maximum.
>> This greatly improves the accuracy of estimates for comparison values
>> near the ends of the data range, particularly if the range is constantly
>> changing due to addition of new data.
>
> Not sure whether it's a good idea a bad idea, but perhaps a solution
> is to expand this to equality lookups too?

I'm not sure that's the right idea to be honest. The problem is that
the planner is taking a risk by using an index which could contain
(theoretically) any number of matching rows, instead of using the
primary key which is guaranteed to only contain 0 or 1 rows. Sure,
peeking into the index to see that there are indeed some processed=0
rows would probably discourage the planner from using it, but why bother?

> Does using "WHERE processed <= 0" work around the problem? (Assuming
> you don't have any negative numbers in this column).

I unfortunately already dropped the problematic index, so I can't answer
that.

.marko