WHERE clause not used when index is used (9.5)

Placing this specific message onto -bugs while keeping -hackers and
removing -novice.

Editing subject to include version and remove list identifiers.

There is continuing discussion on -hackers though mostly about how to do
this right in the future. The specific problem stems from an attempted
performance improvement which is likely to be reverted.

David J.

On Tue, Mar 1, 2016 at 8: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.)
>
>
> > 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
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>