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
INDEXi_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.29rows=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@momjian.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 +