Re: Apparently useless bitmap scans

Am Mittwoch, 9. Mai 2007 16:29 schrieb Alvaro Herrera:
> Peter Eisentraut wrote:
> > There's another odd thing about this plan from yesterday.
>
> Is this still 8.2.1?  The logic to choose bitmap indexes was rewritten
> just before 8.2.4,

OK, upgrading to 8.2.4 fixes this odd plan choice.  The query does run
a bit faster too, but the cost estimate has actually gone up!

8.2.1:

                                                                                              QUERY PLAN
                                                                               

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=87142.18..87366.58 rows=11220 width=184) (actual time=7883.541..8120.647 rows=35000 loops=1)
   ->  Sort  (cost=87142.18..87170.23 rows=11220 width=184) (actual time=7883.471..7926.031 rows=35000 loops=1)
         Sort Key: eh_subj.header_body
         ->  Hash Join  (cost=46283.30..86387.42 rows=11220 width=184) (actual time=5140.182..7635.615 rows=35000
loops=1)
               Hash Cond: (eh_subj.email_id = email.email_id)
               ->  Bitmap Heap Scan on email_header eh_subj  (cost=11853.68..50142.87 rows=272434 width=104) (actual
time=367.956..1719.736rows=280989 loops=1) 
                     Recheck Cond: ((mime_part_id = 0) AND (header_name = 'subject'::text))
                     ->  BitmapAnd  (cost=11853.68..11853.68 rows=27607 width=0) (actual time=326.507..326.507 rows=0
loops=1)
                           ->  Bitmap Index Scan on idx__email_header__header_body_subject  (cost=0.00..5836.24
rows=272434width=0) (actual time=178.041..178.041 rows=280989 loops=1) 
                           ->  Bitmap Index Scan on idx__email_header__header_name  (cost=0.00..5880.97 rows=281247
width=0)(actual time=114.574..114.574 rows=280989 loops=1) 
                                 Index Cond: (header_name = 'subject'::text)
               ->  Hash  (cost=34291.87..34291.87 rows=11020 width=120) (actual time=4772.148..4772.148 rows=35000
loops=1)
                     ->  Hash Join  (cost=24164.59..34291.87 rows=11020 width=120) (actual time=3131.067..4706.997
rows=35000loops=1) 
                           Hash Cond: (mime_part.email_id = email.email_id)
                           ->  Seq Scan on mime_part  (cost=0.00..8355.81 rows=265804 width=12) (actual
time=0.038..514.291rows=267890 loops=1) 
                                 Filter: (mime_part_id = 0)
                           ->  Hash  (cost=24025.94..24025.94 rows=11092 width=112) (actual time=3130.982..3130.982
rows=35000loops=1) 
                                 ->  Hash Join  (cost=22244.54..24025.94 rows=11092 width=112) (actual
time=996.556..3069.280rows=35000 loops=1) 
                                       Hash Cond: (eh_from.email_id = email.email_id)
                                       ->  Bitmap Heap Scan on email_header eh_from  (cost=15576.58..16041.55
rows=107156width=104) (actual time=569.762..1932.017 rows=280990 loops=1) 
                                             Recheck Cond: ((mime_part_id = 0) AND (header_name = 'from'::text))
                                             ->  BitmapAnd  (cost=15576.58..15576.58 rows=160 width=0) (actual
time=532.217..532.217rows=0 loops=1) 
                                                   ->  Bitmap Index Scan on dummy_index  (cost=0.00..3724.22
rows=107156width=0) (actual time=116.386..116.386 rows=280990 loops=1) 
                                                   ->  Bitmap Index Scan on idx__email_header__from_local
(cost=0.00..5779.24rows=107156 width=0) (actual time=174.883..174.883 rows=280990 loops=1) 
                                                   ->  Bitmap Index Scan on dummy2_index  (cost=0.00..5992.25
rows=107156width=0) (actual time=173.575..173.575 rows=280990 loops=1) 
                                       ->  Hash  (cost=6321.79..6321.79 rows=27694 width=8) (actual
time=426.739..426.739rows=35000 loops=1) 
                                             ->  Index Scan using idx__email__time on email  (cost=0.00..6321.79
rows=27694width=8) (actual time=50.000..375.021 rows=35000 loops=1) 
                                                   Index Cond: (("time" >= '2007-05-05 17:01:59'::timestamp without
timezone) AND ("time" < '2007-05-05 18:01:59'::timestamp without time zone)) 
 Total runtime: 8160.442 ms


8.2.4:

                                                                                            QUERY PLAN
                                                                          

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=100086.52..100658.46 rows=28597 width=182) (actual time=6063.766..6281.818 rows=35000 loops=1)
   ->  Sort  (cost=100086.52..100158.01 rows=28597 width=182) (actual time=6063.697..6105.215 rows=35000 loops=1)
         Sort Key: eh_subj.header_body
         ->  Hash Join  (cost=36729.27..97969.83 rows=28597 width=182) (actual time=3690.316..5790.094 rows=35000
loops=1)
               Hash Cond: (eh_subj.email_id = email.email_id)
               ->  Bitmap Heap Scan on email_header eh_subj  (cost=5903.20..63844.68 rows=267832 width=103) (actual
time=214.699..1564.804rows=280989 loops=1) 
                     Recheck Cond: ((mime_part_id = 0) AND (header_name = 'subject'::text))
                     ->  Bitmap Index Scan on idx__email_header__header_body_subject  (cost=0.00..5836.24 rows=267832
width=0)(actual time=172.188..172.188 rows=280989 loops=1) 
               ->  Hash  (cost=30468.98..30468.98 rows=28567 width=119) (actual time=3475.484..3475.484 rows=35000
loops=1)
                     ->  Hash Join  (cost=13773.73..30468.98 rows=28567 width=119) (actual time=1260.579..3409.443
rows=35000loops=1) 
                           Hash Cond: (eh_from.email_id = email.email_id)
                           ->  Index Scan using dummy_index on email_header eh_from  (cost=0.00..13286.00 rows=277652
width=103)(actual time=0.076..1391.974 rows=280990 loops=1) 
                           ->  Hash  (cost=13429.63..13429.63 rows=27528 width=20) (actual time=1260.422..1260.422
rows=35000loops=1) 
                                 ->  Hash Join  (cost=1799.41..13429.63 rows=27528 width=20) (actual
time=114.765..1206.500rows=35000 loops=1) 
                                       Hash Cond: (mime_part.email_id = email.email_id)
                                       ->  Seq Scan on mime_part  (cost=0.00..8355.81 rows=266589 width=12) (actual
time=0.036..407.539rows=267890 loops=1) 
                                             Filter: (mime_part_id = 0)
                                       ->  Hash  (cost=1454.07..1454.07 rows=27627 width=8) (actual
time=114.644..114.644rows=35000 loops=1) 
                                             ->  Index Scan using idx__email__time on email  (cost=0.00..1454.07
rows=27627width=8) (actual time=0.144..63.017 rows=35000 loops=1) 
                                                   Index Cond: (("time" >= '2007-05-05 17:01:59'::timestamp without
timezone) AND ("time" < '2007-05-05 18:01:59'::timestamp without time zone)) 
 Total runtime: 6320.790 ms
(21 Zeilen)


The only significant change is that the first Bitmap Heap Scan (line 6)
became more expensive.  You will notice that in the old plan, you had a
pretty good correspondence of 10 cost units to 1 millisecond throughout,
whereas in the new plan that does not apply to said Bitmap Heap Scan.
I'm not sure whether that is cause for concern.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/