From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Use of index for 50% column restriction |
Date: | 2016-06-08 17:21:09 |
Message-ID: | 20160608172109.GA2152@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
As part of my research on the parsing/planning behavior of PREPARE, I
found a surprising behavior --- a WHERE clause that is 50% restrictive
is using an index. I thought only <10% restrictions used indexes. To
setup the test:
DROP TABLE IF EXISTS test;
CREATE TABLE test (c1 INT, c2 INT, c3 INT);
INSERT INTO test SELECT c1, 0, 0 FROM generate_series(1, 10000) AS a(c1);
INSERT INTO test SELECT c1, 1, 1 FROM generate_series(10001, 20000) AS a(c1);
CREATE INDEX i_test_c2 ON test (c2);
ANALYZE test;
EXPLAIN SELECT * FROM test WHERE c2 = 0;
The output is:
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using i_test_c2 on test (cost=0.29..349.29 rows=10000 width=12)
----------
Index Cond: (c2 = 0)
(2 rows)
\timing does show the optimizer is making the right decision to use the
index, and this behavior is the same back to at least 9.3. Setting
effective_cache_size = '8kB' does not change this behavior. What am I
missing? Is my 10% assumption wrong?
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-06-08 17:28:54 | Re: Use of index for 50% column restriction |
Previous Message | Tom Lane | 2016-06-08 17:12:00 | Re: Should phraseto_tsquery('simple', 'blue blue') @@ to_tsvector('simple', 'blue') be true ? |