Re: [HACKERS] WHERE clause not used when index is used

On 1 March 2016 at 17:22, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Tue, Mar 1, 2016 at 7:40 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Tobias Florek <postgres(at)ibotty(dot)net> writes:
> >> When creating an index to use for an ORDER BY clause, a simple query
> >> starts to return more results than expected. See the following detailed
> >> log.
> >
> > Ugh. That is *badly* broken. I thought maybe it had something to do
> with
> > the "abbreviated keys" work, but the same thing happens if you change the
> > numeric column to integer, so I'm not very sure where to look. Who's
> > touched btree key comparison logic lately?
> >
> > (Problem is reproducible in 9.5 and HEAD, but not 9.4.)
>
>
> Bisects down to:
>
> 606c0123d627b37d5ac3f7c2c97cd715dde7842f is the first bad commit
> commit 606c0123d627b37d5ac3f7c2c97cd715dde7842f
> Author: Simon Riggs <simon(at)2ndQuadrant(dot)com>
> Date: Tue Nov 18 10:24:55 2014 +0000
>
> Reduce btree scan overhead for < and > strategies
>
> For <, <=, > and >= strategies, mark the first scan key
> as already matched if scanning in an appropriate direction.
> If index tuple contains no nulls we can skip the first
> re-check for each tuple.
>
> Author: Rajeev Rastogi
> Reviewer: Haribabu Kommi
> Rework of the code and comments by Simon Riggs
>

Mea culpa.

Looks like we'll need a new release as soon as we can lock down a fix.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services