From: Marko Tiikkaja <marko@joh.to>
To: Marti Raudsepp <marti@juffo.org>
Cc: pgsql-bugs@postgresql.org
Subject: Re: BUG #11500: PRIMARY KEY index not being used
Date: Fri, 26 Sep 2014 14:54:19 +0200
Message-ID: <542561FB.3050900@joh.to> (raw)
In-Reply-To: <CABRT9RB0_AhrhYQqDZtuvsqi22weHP9GcCKxLiwbOCR+Ymm1XQ@mail.gmail.com>
References: <20140926080211.7639.68358@wrigleys.postgresql.org>
<CABRT9RB0_AhrhYQqDZtuvsqi22weHP9GcCKxLiwbOCR+Ymm1XQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-bugs>
On 9/26/14 2:02 PM, Marti Raudsepp wrote:
> On Fri, Sep 26, 2014 at 11:02 AM, <marko@joh.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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: marko@joh.to, marti@juffo.org
Subject: Re: BUG #11500: PRIMARY KEY index not being used
In-Reply-To: <542561FB.3050900@joh.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox