Hello,
I tested bitmap scan and maybe I didnt find good examples, but with bitmap
scan is slower than hashjoin. Only when I use non otiptimized SELECT bps
was little bit faster. All my SELECTs are equal.
bsp off bsp on (ms)
SELECT 1 39.798 37.993
SELECT 2 0.310 0.330
SELECT 3 0.387 0.416
SQL and query plans are in attachment.
Regards
Pavel Stehule
SET
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on sp_op_uct (cost=28.85..1612.12 rows=22644 width=382) (actual
time=1.283..39.484 rows=2 loops=1)
Filter: ((hashed subplan) OR (sp_op_id = 34123))
SubPlan
-> Index Scan using sp_op_product_bal_zak on sp_op_produkt
(cost=0.00..28.83 rows=7 width=4) (actual time=0.018..0.022 rows=1 loops=1)
Index Cond: (bal_zak = 34123)
Total runtime: 39.798 ms
(6 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=29.02..106.84 rows=17 width=382) (actual time=0.120..0.157
rows=2 loops=1)
-> HashAggregate (cost=29.02..29.02 rows=8 width=4) (actual
time=0.093..0.099 rows=2 loops=1)
-> Subquery Scan "IN_subquery" (cost=0.00..29.00 rows=8 width=4)
(actual time=0.028..0.073 rows=2 loops=1)
-> Append (cost=0.00..28.92 rows=8 width=4) (actual
time=0.023..0.058 rows=2 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..28.90 rows=7
width=4) (actual time=0.019..0.028 rows=1 loops=1)
-> Index Scan using sp_op_product_bal_zak on
sp_op_produkt (cost=0.00..28.83 rows=7 width=4) (actual time=0.013..0.017
rows=1 loops=1)
Index Cond: (bal_zak = 34123)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1
width=0) (actual time=0.008..0.015 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.003..0.006 rows=1 loops=1)
-> Index Scan using sp_op_uct_sp_op_id on sp_op_uct (cost=0.00..9.70
rows=2 width=382) (actual time=0.011..0.015 rows=1 loops=2)
Index Cond: (sp_op_uct.sp_op_id = "outer".sp_op_id)
Total runtime: 0.310 ms
(12 rows)
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=28.85..106.77 rows=13 width=382) (actual time=0.058..0.108
rows=2 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=28.85..97.05 rows=11 width=382)
(actual time=0.054..0.071 rows=1 loops=1)
-> Nested Loop (cost=28.85..96.94 rows=11 width=382) (actual
time=0.048..0.061 rows=1 loops=1)
-> HashAggregate (cost=28.85..28.85 rows=7 width=4) (actual
time=0.031..0.033 rows=1 loops=1)
-> Index Scan using sp_op_product_bal_zak on
sp_op_produkt (cost=0.00..28.83 rows=7 width=4) (actual time=0.010..0.013
rows=1 loops=1)
Index Cond: (bal_zak = 34123)
-> Index Scan using sp_op_uct_sp_op_id on sp_op_uct
(cost=0.00..9.70 rows=2 width=382) (actual time=0.008..0.011 rows=1 loops=1)
Index Cond: (sp_op_uct.sp_op_id = "outer".sp_op_id)
-> Subquery Scan "*SELECT* 2" (cost=0.00..9.72 rows=2 width=382) (actual
time=0.012..0.019 rows=1 loops=1)
-> Index Scan using sp_op_uct_sp_op_id on sp_op_uct (cost=0.00..9.70
rows=2 width=382) (actual time=0.007..0.010 rows=1 loops=1)
Index Cond: (sp_op_id = 34123)
Total runtime: 0.387 ms
(12 rows)
SET
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on sp_op_uct (cost=26.50..1609.78 rows=22644 width=382) (actual
time=1.206..37.903 rows=2 loops=1)
Filter: ((hashed subplan) OR (sp_op_id = 34123))
SubPlan
-> Bitmap Heap Scan on sp_op_produkt (cost=2.02..26.49 rows=7 width=4)
(actual time=0.037..0.040 rows=1 loops=1)
Recheck Cond: (bal_zak = 34123)
-> Bitmap Index Scan on sp_op_product_bal_zak (cost=0.00..2.02
rows=7 width=0) (actual time=0.026..0.026 rows=1 loops=1)
Index Cond: (bal_zak = 34123)
Total runtime: 37.993 ms
(8 rows)
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=26.68..104.49 rows=17 width=382) (actual time=0.148..0.186
rows=2 loops=1)
-> HashAggregate (cost=26.68..26.68 rows=8 width=4) (actual
time=0.124..0.129 rows=2 loops=1)
-> Subquery Scan "IN_subquery" (cost=2.02..26.66 rows=8 width=4)
(actual time=0.063..0.108 rows=2 loops=1)
-> Append (cost=2.02..26.58 rows=8 width=4) (actual
time=0.058..0.093 rows=2 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=2.02..26.56 rows=7
width=4) (actual time=0.053..0.060 rows=1 loops=1)
-> Bitmap Heap Scan on sp_op_produkt
(cost=2.02..26.49 rows=7 width=4) (actual time=0.048..0.050 rows=1 loops=1)
Recheck Cond: (bal_zak = 34123)
-> Bitmap Index Scan on sp_op_product_bal_zak
(cost=0.00..2.02 rows=7 width=0) (actual time=0.036..0.036 rows=1 loops=1)
Index Cond: (bal_zak = 34123)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1
width=0) (actual time=0.008..0.015 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.003..0.005 rows=1 loops=1)
-> Index Scan using sp_op_uct_sp_op_id on sp_op_uct (cost=0.00..9.70
rows=2 width=382) (actual time=0.010..0.014 rows=1 loops=2)
Index Cond: (sp_op_uct.sp_op_id = "outer".sp_op_id)
Total runtime: 0.330 ms
(14 rows)
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=26.50..104.42 rows=13 width=382) (actual time=0.086..0.135
rows=2 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=26.50..94.70 rows=11 width=382)
(actual time=0.081..0.099 rows=1 loops=1)
-> Nested Loop (cost=26.50..94.59 rows=11 width=382) (actual
time=0.075..0.089 rows=1 loops=1)
-> HashAggregate (cost=26.50..26.50 rows=7 width=4) (actual
time=0.055..0.058 rows=1 loops=1)
-> Bitmap Heap Scan on sp_op_produkt (cost=2.02..26.49
rows=7 width=4) (actual time=0.036..0.039 rows=1 loops=1)
Recheck Cond: (bal_zak = 34123)
-> Bitmap Index Scan on sp_op_product_bal_zak
(cost=0.00..2.02 rows=7 width=0) (actual time=0.024..0.024 rows=1 loops=1)
Index Cond: (bal_zak = 34123)
-> Index Scan using sp_op_uct_sp_op_id on sp_op_uct
(cost=0.00..9.70 rows=2 width=382) (actual time=0.010..0.013 rows=1 loops=1)
Index Cond: (sp_op_uct.sp_op_id = "outer".sp_op_id)
-> Subquery Scan "*SELECT* 2" (cost=0.00..9.72 rows=2 width=382) (actual
time=0.012..0.020 rows=1 loops=1)
-> Index Scan using sp_op_uct_sp_op_id on sp_op_uct (cost=0.00..9.70
rows=2 width=382) (actual time=0.007..0.010 rows=1 loops=1)
Index Cond: (sp_op_id = 34123)
Total runtime: 0.416 ms
(14 rows)
set enable_bitmapscan to off;
explain analyze select * from sp_op_uct where sp_op_id in
(select sp_op_id from sp_op_produkt where bal_zak = 34123) or sp_op_id =
34123;
explain analyze select * from sp_op_uct where sp_op_id in
(select sp_op_id from sp_op_produkt where bal_zak = 34123 union all select
34123);
explain analyze select * from sp_op_uct where sp_op_id in
(select sp_op_id from sp_op_produkt where bal_zak = 34123)
union all select * from sp_op_uct where sp_op_id = 34123;
set enable_bitmapscan to on;
explain analyze select * from sp_op_uct where sp_op_id in
(select sp_op_id from sp_op_produkt where bal_zak = 34123) or sp_op_id =
34123;
explain analyze select * from sp_op_uct where sp_op_id in
(select sp_op_id from sp_op_produkt where bal_zak = 34123 union all select
34123);
explain analyze select * from sp_op_uct where sp_op_id in
(select sp_op_id from sp_op_produkt where bal_zak = 34123)
union all select * from sp_op_uct where sp_op_id = 34123;
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
