BUG #2716: performance problem with enable_bitmapscan

The following bug has been logged online:

Bug reference: 2716
Logged by: Pavel
Email address: pavel(at)aeccom(dot)com
PostgreSQL version: 8.1.4
Operating system: Linux Redhat
Description: performance problem with enable_bitmapscan
Details:

Hi,

i have a following performance problem by Postgresql 8.1.4.
The Optimizer join the tables wrong.

My settings:
enable_bitmapscan=on

If I "set enable_bitmapscan=off;" the optimizer will be join a right
column.

any ideas?
is that a bug?

----------------------------------------------------------------------
Query

SELECT ft.val_10
FROM dbflat AS ft
, bx
, en
, dbflat AS ft0,
(SELECT fts.val_1, max(fts.val_6) AS val_6
FROM dbflat AS fts, bx, en
WHERE (bx.mem=144134500 AND
bx.com=222492995 AND
bx.hide=FALSE AND
bx.en=fts.en AND
en.preview=FALSE AND
fts.en=en.id AND
fts.docstart=1) GROUP BY fts.val_1) AS sub
, dbflat AS ft1
, dbflat AS ft2
WHERE bx.mem=144134500 AND
bx.com=222492995 AND
bx.hide=FALSE AND
bx.en=ft.en AND
en.preview=FALSE AND
ft.en=en.id AND
ft0.flatid=ft.flatid AND
(ft0.val_9='1' OR ft0.val_9='2') AND
ft1.val_1=sub.val_1 AND
ft1.flatid=ft.flatid AND
ft2.val_6=sub.val_6 AND
ft2.flatid=ft.flatid AND
(((ft.docstart=1 OR ft.docstart=0) AND NOT ft.val_10 IS NULL) OR
(ft.docstart=1
AND ft.val_10 IS NULL))
GROUP BY ft.val_10
ORDER BY ft.val_10 ASC
LIMIT 200
;

---------------------------------------
EXPLAIN PLAN

Limit (cost=88.30..88.31 rows=1 width=8)
-> Group (cost=88.30..88.31 rows=1 width=8)
-> Sort (cost=88.30..88.31 rows=1 width=8)
Sort Key: ft.val_10
-> Nested Loop (cost=36.95..88.29 rows=1 width=8)
-> Nested Loop (cost=24.52..72.84 rows=1 width=53)
Join Filter: ("outer".en = "inner".en)
-> Nested Loop (cost=0.00..9.85 rows=1 width=8)
-> Index Scan using bx_j_index on bx
(cost=0.00..4.95 rows=1 width=4)
Index Cond: ((com = 222492995) AND
(mem = 144134500))
Filter: (NOT hide)
-> Index Scan using en_pk on en
(cost=0.00..4.88 rows=1 width=4)
Index Cond: (en.id = "outer".en)
Filter: (NOT preview)
-> Nested Loop (cost=24.52..62.94 rows=4
width=57)
-> Nested Loop (cost=22.49..41.62 rows=1
width=30)
-> Nested Loop (cost=17.44..25.47
rows=2 width=23)
-> HashAggregate
(cost=17.44..17.45 rows=1 width=16)
-> Nested Loop
(cost=0.00..17.43 rows=1 width=16)
-> Nested Loop
(cost=0.00..9.85 rows=1 width=8)
-> Index Scan
using bx_j_index on bx (cost=0.00..4.95 rows=1 width=4)
Index
Cond: ((com = 222492995) AND (mem = 144134500))
Filter:
(NOT hide)
-> Index Scan
using en_pk on en (cost=0.00..4.88 rows=1 width=4)
Index
Cond: (en.id = "outer".en)
Filter:
(NOT preview)
-> Index Scan using
dbflat_en on dbflat fts (cost=0.00..7.56 rows=2 width=20)
Index Cond:
(("outer".en = fts.en) AND (fts.docstart = 1))
-> Index Scan using
dbflat_val_1 on dbflat ft1 (cost=0.00..7.98 rows=2 width=23)
Index Cond: (ft1.val_1 =
"outer".val_1)
-> Bitmap Heap Scan on dbflat ft2
(cost=5.06..8.06 rows=1 width=23)
Recheck Cond: ((ft2.flatid =
"outer".flatid) AND (ft2.val_6 = "outer".val_6))
-> BitmapAnd (cost=5.06..5.06
rows=1 width=0)
-> Bitmap Index Scan on
dbflat_flatid (cost=0.00..2.03 rows=7 width=0)
Index Cond:
(ft2.flatid = "outer".flatid)
-> Bitmap Index Scan on
dbflat_val_6 (cost=0.00..2.78 rows=223 width=0)
Index Cond:
(ft2.val_6 = "outer".val_6)
-> Bitmap Heap Scan on dbflat ft
(cost=2.03..21.23 rows=7 width=27)
Recheck Cond: ("outer".flatid =
ft.flatid)
Filter: ((((docstart = 1) OR (docstart
= 0)) AND (NOT (val_10 IS NULL))) OR ((docstart = 1) AND (val_10 IS NULL)))
-> Bitmap Index Scan on dbflat_flatid
(cost=0.00..2.03 rows=7 width=0)
Index Cond: ("outer".flatid =
ft.flatid)
-> Bitmap Heap Scan on dbflat ft0 (cost=12.43..15.44
rows=1 width=15)
Recheck Cond: ((ft0.flatid = "outer".flatid) AND
((ft0.val_9 = '1'::text) OR (ft0.val_9 = '2'::text)))
-> BitmapAnd (cost=12.43..12.43 rows=1 width=0)
-> Bitmap Index Scan on dbflat_flatid
(cost=0.00..2.03 rows=7 width=0)
Index Cond: (ft0.flatid =
"outer".flatid)
-> BitmapOr (cost=10.15..10.15 rows=615
width=0)
-> Bitmap Index Scan on dbflat_val_9
(cost=0.00..3.54 rows=153 width=0)
Index Cond: (val_9 = '1'::text)
-> Bitmap Index Scan on dbflat_val_9
(cost=0.00..6.62 rows=462 width=0)
Index Cond: (val_9 = '2'::text)

Total runtime: 30.341 ms

SET enable_bitmapscan=off;

----------------------------------
EXPLAIN PLAN

Limit (cost=112.71..112.72 rows=1 width=8)
-> Group (cost=112.71..112.72 rows=1 width=8)
-> Sort (cost=112.71..112.72 rows=1 width=8)
Sort Key: ft.val_10
-> Nested Loop (cost=17.44..112.70 rows=1 width=8)
-> Nested Loop (cost=17.44..107.81 rows=1 width=16)
-> Nested Loop (cost=17.44..86.50 rows=1
width=61)
Join Filter: ("inner".flatid =
"outer".flatid)
-> Nested Loop (cost=17.44..78.49 rows=1
width=54)
Join Filter: ("outer".val_6 =
"inner"."?column2?")
-> Nested Loop (cost=0.00..61.02
rows=1 width=54)
-> Nested Loop
(cost=0.00..39.67 rows=1 width=31)
-> Index Scan using
bx_j_index on bx (cost=0.00..4.95 rows=1 width=4)
Index Cond: ((com =
222492995) AND (mem = 144134500))
Filter: (NOT hide)
-> Index Scan using
dbflat_en on dbflat ft (cost=0.00..34.68 rows=3 width=27)
Index Cond:
("outer".en = ft.en)
Filter: ((((docstart
= 1) OR (docstart = 0)) AND (NOT (val_10 IS NULL))) OR ((docstart = 1) AND
(val_10 IS NULL)))
-> Index Scan using
dbflat_flatid on dbflat ft2 (cost=0.00..21.26 rows=7 width=23)
Index Cond: (ft2.flatid =
"outer".flatid)
-> HashAggregate (cost=17.44..17.45
rows=1 width=16)
-> Nested Loop
(cost=0.00..17.43 rows=1 width=16)
-> Nested Loop
(cost=0.00..9.85 rows=1 width=8)
-> Index Scan using
bx_j_index on bx (cost=0.00..4.95 rows=1 width=4)
Index Cond:
((com = 222492995) AND (mem = 144134500))
Filter: (NOT
hide)
-> Index Scan using
en_pk on en (cost=0.00..4.88 rows=1 width=4)
Index Cond:
(en.id = "outer".en)
Filter: (NOT
preview)
-> Index Scan using
dbflat_en on dbflat fts (cost=0.00..7.56 rows=2 width=20)
Index Cond:
(("outer".en = fts.en) AND (fts.docstart = 1))
-> Index Scan using dbflat_val_1 on dbflat
ft1 (cost=0.00..7.98 rows=2 width=23)
Index Cond: (ft1.val_1 =
"outer".val_1)
-> Index Scan using dbflat_flatid on dbflat ft0
(cost=0.00..21.29 rows=1 width=15)
Index Cond: (ft0.flatid = "outer".flatid)
Filter: ((val_9 = '1'::text) OR (val_9 =
'2'::text))
-> Index Scan using en_pk on en (cost=0.00..4.88
rows=1 width=4)
Index Cond: ("outer".en = en.id)
Filter: (NOT preview)

Total runtime: 3.002 ms