Re: bitmap index scan problem? - Mailing list pgsql-bugs

Tom Lane wrote:
> stig erikson <stigerikson_nospam_@yahoo.se> writes:
>> the question is simply why the planner is not smart enough to skip the bitmap scan if normal operation is faster.
>
> Probably because it hasn't got good statistics about the distribution of
> "bid":
>
>>                 ->  Bitmap Index Scan on bid_index  (cost=0.00..44.51 rows=7576 width=0) (actual time=0.137..0.137
rows=506loops=1) 
>>                       Index Cond: (bid = 17675)
>
> When the rowcount estimate is off by more than a factor of 10, the costs
> are going to be wrong too.  Try increasing the statistics target for this
> table.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

Hi.
thank you for your answer.
the last example was not 100% according to what my intention was, even though it shows the problem.
here is a better example.
i have not yet changed the default statistics target, how do i find out what i should change it to?

again i am quite confused that PG does not use the bid_index when enable_bitmapscan is true.

some other information:
- there are around 30 000 000 rows in total.

- there are usually 28-35 different values for ct at any given time, the number of times each value occurs varies from
   less then 10 to over 10 000 000.

- there are usually 23 000 different values for bid at any given time. the number of times each value occurs varies
from
   100 to 20 000.




stat=# show default_statistics_target;
  default_statistics_target
---------------------------
  10
(1 row)

stat=# VACUUM FULL ANALYZE stats ;
VACUUM

stat=# set enable_bitmapscan to 1;
SET
stat=# explain analyze select aid, ct, sum(total) from stats where (ct='90' OR ct='212') and bid=17675 GROUP BY aid,
ct;
                                                                         QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------
  HashAggregate  (cost=18149.28..18149.94 rows=53 width=16) (actual time=14458.638..14458.644 rows=3 loops=1)
    ->  Bitmap Heap Scan on stats  (cost=15786.50..18144.74 rows=605 width=16) (actual time=14033.693..14458.260
rows=62loops=1) 
          Recheck Cond: ((bid = 17675) AND ((ct = 90) OR (ct = 212)))
          ->  BitmapAnd  (cost=15786.50..15786.50 rows=608 width=0) (actual time=14026.953..14026.953 rows=0 loops=1)
                ->  Bitmap Index Scan on bid_index  (cost=0.00..44.10 rows=7456 width=0) (actual time=79.293..79.293
rows=506loops=1) 
                      Index Cond: (bid = 17675)
                ->  BitmapOr  (cost=15742.16..15742.16 rows=2766331 width=0) (actual time=13947.348..13947.348 rows=0
loops=1)
                      ->  Bitmap Index Scan on ct_index  (cost=0.00..14675.92 rows=2579119 width=0) (actual
time=13526.774..13526.774rows=2563790 loops=1) 
                            Index Cond: (ct = 90)
                      ->  Bitmap Index Scan on ct_index  (cost=0.00..1066.24 rows=187212 width=0) (actual
time=420.564..420.564rows=374354 loops=1) 
                            Index Cond: (ct = 212)
  Total runtime: 14458.747 ms
(12 rows)



stat=# set enable_bitmapscan to 0;
SET
stat=# explain analyze select aid, ct, sum(total) from stats where (ct='90' OR ct='212') and bid=17675 GROUP BY aid,
ct;
                                                           QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------
  HashAggregate  (cost=28152.82..28153.48 rows=53 width=16) (actual time=7.759..7.768 rows=3 loops=1)
    ->  Index Scan using bid_index on stats  (cost=0.00..28148.28 rows=605 width=16) (actual time=0.100..7.483 rows=62
loops=1)
          Index Cond: (bid = 17675)
          Filter: ((ct = 90) OR (ct = 212))
  Total runtime: 7.858 ms
(5 rows)




/stig