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

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

It is not a part of the code-base I'm familiar with, so it is unlikely
I can find the bug.

Cheers,

Jeff