WHERE clause not used when index is used
От | Tobias Florek |
---|---|
Тема | WHERE clause not used when index is used |
Дата | |
Msg-id | 20160301110427.29596.73021@piano обсуждение исходный текст |
Ответы |
Re: WHERE clause not used when index is used
|
Список | pgsql-novice |
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
Вложения
В списке pgsql-novice по дате отправления: