Thread: Index scans when bitmap scans disabled
Is it expected that the planner in 8.1.3 (CVS) and HEAD doesn't use an index scan for the second query below when bitmap scans are disabled? Disabling sequential scans as well results in the planner using a bitmap scan. test=> CREATE TABLE foo (id integer PRIMARY KEY); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE test=> INSERT INTO foo SELECT * FROM generate_series(1, 100000); INSERT 0 100000 test=> ANALYZE foo; ANALYZE test=> SET enable_bitmapscan TO off; SET test=> EXPLAIN SELECT * FROM foo WHERE id IN (1); QUERY PLAN --------------------------------------------------------------------Index Scan using foo_pkey on foo (cost=0.00..3.01 rows=1width=4) Index Cond: (id = 1) (2 rows) test=> EXPLAIN SELECT * FROM foo WHERE id IN (1, 2); QUERY PLAN ------------------------------------------------------Seq Scan on foo (cost=0.00..2041.00 rows=2 width=4) Filter: ((id= 1) OR (id = 2)) (2 rows) test=> SET enable_seqscan TO off; SET test=> EXPLAIN SELECT * FROM foo WHERE id IN (1, 2); QUERY PLAN -----------------------------------------------------------------------------Bitmap Heap Scan on foo (cost=100000004.01..100000008.03rows=2 width=4) Recheck Cond: ((id = 1) OR (id = 2)) -> BitmapOr (cost=4.01..4.01 rows=2width=0) -> Bitmap Index Scan on foo_pkey (cost=0.00..2.00 rows=1 width=0) Index Cond: (id =1) -> Bitmap Index Scan on foo_pkey (cost=0.00..2.00 rows=1 width=0) Index Cond: (id = 2) (7 rows) Here's the same query in 8.0.7: test=> EXPLAIN SELECT * FROM foo WHERE id IN (1, 2); QUERY PLAN ------------------------------------------------------------------------------Index Scan using foo_pkey, foo_pkey on foo (cost=0.00..6.03 rows=2 width=4) Index Cond: ((id = 1) OR (id = 2)) (2 rows) -- Michael Fuhr
Michael Fuhr <mike@fuhr.org> writes: > Is it expected that the planner in 8.1.3 (CVS) and HEAD doesn't use > an index scan for the second query below when bitmap scans are > disabled? You mean this one? > test=> EXPLAIN SELECT * FROM foo WHERE id IN (1, 2); That's not an indexable condition, ie, in general it can't be satisfied with a single probe of the index. > Here's the same query in 8.0.7: > Index Scan using foo_pkey, foo_pkey on foo (cost=0.00..6.03 rows=2 width=4) > Index Cond: ((id = 1) OR (id = 2)) We removed the repeated-indexscan plan type in favor of bitmap scans. I'm not immediately aware of any reason why we shouldn't have ... if there is any case where the pre-8.1 plan style wins, it'd have to be a pretty narrow scenario. regards, tom lane
On Sun, Apr 23, 2006 at 11:12:23PM -0400, Tom Lane wrote: > We removed the repeated-indexscan plan type in favor of bitmap scans. > I'm not immediately aware of any reason why we shouldn't have ... if > there is any case where the pre-8.1 plan style wins, it'd have to be > a pretty narrow scenario. Thanks -- I was just wondering if the behavior was expected or not. -- Michael Fuhr