Обсуждение: WHERE clause not used when index is used

Hi,

I have the following strange phenomena using postgresql 9.5 using
official packages from both of
 * the debian repository
      http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg/main
 * and the centos repository
      http://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm


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.


Create enough test data for planer to use an index (if exists) for the
condition.

    CREATE TABLE "index_cond_test" AS
    SELECT
      (10 + random() * 10)::int AS "final_score",
      round((10 + random() * 10)::numeric, 5) "time_taken"
    FROM generate_series(1, 10000) s;


Run control query without an index (will be less than 10000 rows). Pay
attention to tuples of (20,a) with a > 11.

    SELECT *
    FROM "index_cond_test"
    WHERE (final_score, time_taken) < (20, 11)
    ORDER BY final_score DESC, time_taken ASC;


Or wrapped in count(*), to make it even more obvious

    SELECT count(*) FROM ( SELECT *
       FROM "index_cond_test"
       WHERE (final_score, time_taken) < (20, 11)
       ORDER BY final_score DESC, time_taken ASC) q;

Create the index

    CREATE INDEX "index_cond_test_ranking" ON "index_cond_test" USING btree (final_score DESC, time_taken ASC);

Run test query (will return all 10000 rows)

    SELECT *
    FROM "index_cond_test"
    WHERE (final_score, time_taken) < (20, 11)
    ORDER BY final_score DESC, time_taken ASC;

or wrapped

    SELECT count(*) FROM ( SELECT *
       FROM "index_cond_test"
       WHERE (final_score, time_taken) < (20, 11)
       ORDER BY final_score DESC, time_taken ASC) q;

Any ideas?

Thank you in advance,
 Tobias Florek

Tobias Florek <postgres@ibotty.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.)


> Create enough test data for planer to use an index (if exists) for the
> condition.

>     CREATE TABLE "index_cond_test" AS
>     SELECT
>       (10 + random() * 10)::int AS "final_score",
>       round((10 + random() * 10)::numeric, 5) "time_taken"
>     FROM generate_series(1, 10000) s;


> Run control query without an index (will be less than 10000 rows). Pay
> attention to tuples of (20,a) with a > 11.

>     SELECT *
>     FROM "index_cond_test"
>     WHERE (final_score, time_taken) < (20, 11)
>     ORDER BY final_score DESC, time_taken ASC;


> Or wrapped in count(*), to make it even more obvious

>     SELECT count(*) FROM ( SELECT *
>        FROM "index_cond_test"
>        WHERE (final_score, time_taken) < (20, 11)
>        ORDER BY final_score DESC, time_taken ASC) q;

> Create the index

>     CREATE INDEX "index_cond_test_ranking" ON "index_cond_test" USING btree (final_score DESC, time_taken ASC);

> Run test query (will return all 10000 rows)

>     SELECT *
>     FROM "index_cond_test"
>     WHERE (final_score, time_taken) < (20, 11)
>     ORDER BY final_score DESC, time_taken ASC;

> or wrapped

>     SELECT count(*) FROM ( SELECT *
>        FROM "index_cond_test"
>        WHERE (final_score, time_taken) < (20, 11)
>        ORDER BY final_score DESC, time_taken ASC) q;

            regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Tobias Florek <postgres@ibotty.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.)

Looks to have been introduced in 2ed5b87f.  Reverting that gets us back
to results which look correct.

> > Create enough test data for planer to use an index (if exists) for the
> > condition.
>
> >     CREATE TABLE "index_cond_test" AS
> >     SELECT
> >       (10 + random() * 10)::int AS "final_score",
> >       round((10 + random() * 10)::numeric, 5) "time_taken"
> >     FROM generate_series(1, 10000) s;
>
>
> > Run control query without an index (will be less than 10000 rows). Pay
> > attention to tuples of (20,a) with a > 11.
>
> >     SELECT *
> >     FROM "index_cond_test"
> >     WHERE (final_score, time_taken) < (20, 11)
> >     ORDER BY final_score DESC, time_taken ASC;
>
>
> > Or wrapped in count(*), to make it even more obvious
>
> >     SELECT count(*) FROM ( SELECT *
> >        FROM "index_cond_test"
> >        WHERE (final_score, time_taken) < (20, 11)
> >        ORDER BY final_score DESC, time_taken ASC) q;
>
> > Create the index
>
> >     CREATE INDEX "index_cond_test_ranking" ON "index_cond_test" USING btree (final_score DESC, time_taken ASC);
>
> > Run test query (will return all 10000 rows)
>
> >     SELECT *
> >     FROM "index_cond_test"
> >     WHERE (final_score, time_taken) < (20, 11)
> >     ORDER BY final_score DESC, time_taken ASC;
>
> > or wrapped
>
> >     SELECT count(*) FROM ( SELECT *
> >        FROM "index_cond_test"
> >        WHERE (final_score, time_taken) < (20, 11)
> >        ORDER BY final_score DESC, time_taken ASC) q;

Thanks!

Stephen

On Tue, Mar 1, 2016 at 7:40 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Tobias Florek <postgres@ibotty.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@2ndQuadrant.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