From: | Marko Tiikkaja <marko(at)joh(dot)to> |
---|---|
To: | Marti Raudsepp <marti(at)juffo(dot)org> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #11500: PRIMARY KEY index not being used |
Date: | 2014-09-26 12:54:19 |
Message-ID: | 542561FB.3050900@joh.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Nelson Page | 2014-09-26 15:36:49 | Re: BUG #11457: The below query crashes 9.3.5, but not 9.3.4 |
Previous Message | Marti Raudsepp | 2014-09-26 12:02:43 | Re: BUG #11500: PRIMARY KEY index not being used |