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;


  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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to