Re: bitmap index scan problem?

Tom Lane wrote:
> stig erikson <stigerikson_nospam_(at)yahoo(dot)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=506 loops=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=62 loops=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=506 loops=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.774 rows=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.564 rows=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