From: | Tobias Florek <postgres(at)ibotty(dot)net> |
---|---|
To: | PGSQL-Novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | WHERE clause not used when index is used |
Date: | 2016-03-01 11:04:27 |
Message-ID: | 20160301110427.29596.73021@piano |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers 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
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2016-03-01 11:06:39 | Re: Relation extension scalability |
Previous Message | Pavel Stehule | 2016-03-01 10:09:08 | Re: Proposal: SET ROLE hook |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-03-01 15:40:47 | Re: [NOVICE] WHERE clause not used when index is used |
Previous Message | Frank Pinto | 2016-02-29 19:52:14 | Re: No password supplied error when running a batch script as a programmed task |