On Wed, Jun 8, 2016 at 01:28:54PM -0400, Tom Lane wrote: > Bruce Momjian <br...@momjian.us> writes: > > 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. > > There's no such hard-and-fast rule. The cost estimate break point depends > greatly on the index order correlation (which is 100% in your example), > as well as some other factors like the index size versus > effective_cache_size. > > For randomly-ordered data I believe the cutover is actually well below 10%.
Ah, I had not considered the correlation order of the rows in the table. This test returns the sequential scan I expected by using floor(random() * 2): DROP TABLE IF EXISTS test; CREATE TABLE test (c1 INT, c2 INT, c3 INT); INSERT INTO test SELECT c1, floor(random() * 2), 0 FROM generate_series(1, 10000) AS a(c1); INSERT INTO test SELECT c1, floor(random() * 2), 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; Thanks. -- Bruce Momjian <br...@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 + -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers