* Tom Lane (t...@sss.pgh.pa.us) wrote: > Tobias Florek <postg...@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
signature.asc
Description: Digital signature
