On Wed, Jun 8, 2016 at 05:07:34PM -0400, Bruce Momjian wrote: > > 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.
Just a follow-up, but even with a randomized correlation order, it seems 25% restrictivity generates a Bitmap Index Scan: DROP TABLE IF EXISTS test; CREATE TABLE test (c1 INT, c2 INT, c3 INT); INSERT INTO test SELECT c1, abs(floor(random() * 4)-1), abs(floor(random() * 4)-1) FROM generate_series(1, 10000) AS a(c1); INSERT INTO test SELECT c1, abs(floor(random() * 4)-1), abs(floor(random() * 4)-1) FROM generate_series(10001, 15000) AS a(c1); INSERT INTO test SELECT c1, abs(floor(random() * 4)-1), abs(floor(random() * 4)-1) FROM generate_series(15001, 20000) AS a(c1); CREATE INDEX i_test_c2 ON test (c2); ANALYZE test; SELECT c2, COUNT(*) FROM test GROUP BY c2 ORDER BY 1; c2 | count ----+------- 0 | 5020 25% 1 | 10006 50% 2 | 4974 25% EXPLAIN SELECT * FROM TEST WHERE c2 = 1; QUERY PLAN ----------------------------------------------------------- Seq Scan on test (cost=0.00..359.00 rows=10006 width=12) Filter: (c2 = 1) EXPLAIN SELECT * FROM TEST WHERE c2 = 0; QUERY PLAN ---------------------------------------------------------------------------- Bitmap Heap Scan on test (cost=99.19..270.94 rows=5020 width=12) Recheck Cond: (c2 = 0) -> Bitmap Index Scan on i_test_c2 (cost=0.00..97.94 rows=5020 width=0) Index Cond: (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: http://www.postgresql.org/mailpref/pgsql-hackers