BUG #2716: performance problem with enable_bitmapscan - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      2716
Logged by:          Pavel
Email address:      pavel@aeccom.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