Use of index for 50% column restriction

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 +

Responses

Browse pgsql-hackers by date

  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 ?