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

* 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.)

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