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

On 01/03/16 18:37, Tom Lane wrote:
> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
>> 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
>
> On looking at this, the problem seems pretty obvious: that commit simply
> fails to consider multi-column keys at all. (For that matter, it also
> fails to consider zero-column keys.) I think the logic might be all right
> if a test on so->numberOfKeys == 1 were added; I don't see how the
> optimization could work in multi-column cases.
>

It seems that way to me as well.

> However, I'm not sure that's 100% of the issue, because in playing around
> with this I was having a harder time reproducing the failure outside of
> Tobias' example than I expected. There may be more than one bug, or there
> may be other changes that sometimes mask the problem.
>

I can only get the issue when the sort order of the individual keys does
not correlate and the operator sorts according to the first column and
there are duplicate values for the first column. This makes sense to me
as we switch to SK_BT_MATCHED as soon as we hit first match, but because
there isn't correlation on the second column we get false positives
because while we are only scanning part of the btree where first column
matches, it does not necessary has to be true for second column. I don't
know our btree code to sufficient detail to be sure I didn't miss
anything though.

Quick fix to me seems what you said above, although it seems like we
could make it work even for multicolumn indexes if we checked that the
ordering of everything is correct. But I would refrain from adding the
complexity as part of a fix.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services