I'm encountering an issue where PG 9.2.4 (we also see this with 9.2.3)
is picking a plan involving a bitmap heap scan that turns out to be much
slower than a nested-loop plan using indexes.
The planner picks the hashjoin plan by default (see attached files)
Bitmap Heap Scan on public.table_b_2 b (cost=172635.99..9800225.75
rows=8435754 width=10) (actual t
ime=9132.194..1785196.352 rows=9749680 loops=1)
Recheck Cond: ((b.organization_id = 3) AND
(b.year = 2013) AND (b.month = 3))
Rows Removed by Index Recheck: 313195667
Filter: (b.product_id = 2)
Is the part that seems be causing the problem (or at least taking most
of the time, other than the final aggregation)
If I set enable_hashjoin=false and enable_mergejoin=false I get the
nestedloop join plan.
table_b is 137 GB plus indexes each on is around 43 GB
table_a is 20 GB
random_page_cost = 2.0
effective_cache_size = 3500MB
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
work_mem = 64MB
shared_buffers = 300MB (for this output, I've also had it at 2GB)
If I bump cpu_tuple_cost to the 10-20 range it will pick the nested loop
join for some date ranges but not all. cpu_tuple_cost of 20 doesn't
sound like an sane value.
This database used to run 8.3 where it picked the nested-loop join. We
used pg_upgrade to migrate to 9.2
Any ideas why the bitmap heap scan is much slower than the planner expects?
Steve
HashAggregate (cost=11972282.27..11972448.32 rows=11070 width=51) (actual
time=3523526.572..3523526.646 rows=30 loops=1)
-> Hash Join (cost=1287232.78..11220656.06 rows=724459 width=51) (actual
time=1702760.590..1898522.706 rows=662583 loops=1)
Hash Cond: (a.product_operation = o.id)
-> Hash Right Join (cost=1287230.86..11210692.83 rows=724459
width=40) (actual time=1702760.538..1897144.746 rows=662583 loop
s=1)
Hash Cond: (b.a_id = a.id)
-> Append (cost=0.00..9800225.75 rows=8435755 width=10)
(actual time=9132.198..1788027.626 rows=9749680 loops=1)
-> Seq Scan on public.table_b b (cost=0.00..0.00 rows=1
width=22) (actual time=0.002..0.002 rows=0 loo
ps=1)
Filter: ((b.product_id = 2) AND (b.organization_id =
3) AND (b.year = 2013) AND (b.month = 3))
-> Bitmap Heap Scan on public.table_b_2 b
(cost=172635.99..9800225.75 rows=8435754 width=10) (actual t
ime=9132.194..1785196.352 rows=9749680 loops=1)
Recheck Cond: ((b.organization_id = 3) AND (b.year =
2013) AND (b.month = 3))
Rows Removed by Index Recheck: 313195667
Filter: (b.product_id = 2)
-> Bitmap Index Scan on table_b_2_orgid_ym_unq
(cost=0.00..170527.05 rows=8435754 width=0) (actual time=849
1.007..8491.007 rows=9749680 loops=1)
Index Cond: ((b.organization_id = 3) AND
(b.year = 2013) AND (b.month = 3))
-> Hash (cost=1285387.42..1285387.42 rows=147475 width=34)
(actual time=7291.015..7291.015 rows=662583 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 44000kB
-> Append (cost=0.00..1285387.42 rows=147475 width=34)
(actual time=521.262..6780.112 rows=662583 loops=1)
-> Seq Scan on public.table_a a (cost=0.00..0.00
rows=1 width=60) (actual time=0.001..0.001 row
s=0 loops=1)
Filter: ((a.twin_id IS NULL) AND
(a.transaction_date >= '2012-11-01 00:00:00+00'::timestamp with time z
one) AND (a.transaction_date < '2012-12-01 00:00:00+00'::timestamp with time
zone) AND (a.product_id = 2) AND ((a.trans_type_id = ANY ('
{2,3,4}'::integer[])) OR (a.trans_type_id IS NULL)))
-> Bitmap Heap Scan on public.table_a_2 a
(cost=16549.24..1285387.42 rows=147474 width=34) (act
ual time=521.260..6677.788 rows=662583 loops=1)
Recheck Cond: ((a.transaction_date >=
'2012-11-01 00:00:00+00'::timestamp with time zone) AND (a.transa
ction_date < '2012-12-01 00:00:00+00'::timestamp with time zone))
Filter: ((a.twin_id IS NULL) AND (a.product_id
= 2) AND ((a.trans_type_id = ANY ('{2,3,4}'::integer[]))
OR (a.trans_type_id IS NULL)))
Rows Removed by Filter: 411920
-> Bitmap Index Scan on
table_a_2_trans_date_idx (cost=0.00..16512.37 rows=1065722 width=0) (act
ual time=376.848..376.848 rows=1074503 loops=1)
Index Cond: ((a.transaction_date >=
'2012-11-01 00:00:00+00'::timestamp with time zone) AND (a.tr
ansaction_date < '2012-12-01 00:00:00+00'::timestamp with time zone))
-> Hash (cost=1.41..1.41 rows=41 width=19) (actual time=0.034..0.034
rows=41 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 3kB
-> Seq Scan on public.product_operations o (cost=0.00..1.41
rows=41 width=19) (actual time=0.006..0.016 rows=41 loops=1
)
Total runtime: 3523602.967 ms
(39 rows)
HashAggregate (cost=21527635.27..21527801.32 rows=11070 width=51) (actual
time=1578174.649..1578174.744 rows=30 loops=1)
-> Nested Loop Left Join (cost=0.00..20776009.06 rows=724459 width=51)
(actual time=598.332..54815.039 rows=662583 loops=1)
-> Nested Loop (cost=0.00..1376086.06 rows=147475 width=45) (actual
time=558.022..13917.873 rows=662583 loops=1)
Join Filter: (a.product_operation = o.id)
Rows Removed by Join Filter: 26503320
-> Append (cost=0.00..1285387.42 rows=147475 width=34) (actual
time=550.779..5709.779 rows=662583 loops=1)
-> Seq Scan on public.table_a a (cost=0.00..0.00 rows=1
width=60) (actual time=0.002..0.002 rows=0 lo
ops=1)
Filter: ((a.twin_id IS NULL) AND (a.transaction_date
>= '2012-11-01 00:00:00+00'::timestamp with time zone) A
ND (a.transaction_date < '2012-12-01 00:00:00+00'::timestamp with time zone)
AND (a.product_id = 2) AND ((a.trans_type_id = ANY ('{2,3,4
}'::integer[])) OR (a.trans_type_id IS NULL)))
-> Bitmap Heap Scan on public.table_a_2 a
(cost=16549.24..1285387.42 rows=147474 width=34) (actual ti
me=550.775..5551.190 rows=662583 loops=1)
t_operation
Recheck Cond: ((a.transaction_date >= '2012-11-01
00:00:00+00'::timestamp with time zone) AND (a.transaction_
date < '2012-12-01 00:00:00+00'::timestamp with time zone))
Filter: ((a.twin_id IS NULL) AND (a.product_id = 2)
AND ((a.trans_type_id = ANY ('{2,3,4}'::integer[])) OR (a
.trans_type_id IS NULL)))
Rows Removed by Filter: 411920
-> Bitmap Index Scan on table_a_2_trans_date_idx
(cost=0.00..16512.37 rows=1065722 width=0) (actual ti
me=386.455..386.455 rows=1074503 loops=1)
Index Cond: ((a.transaction_date >=
'2012-11-01 00:00:00+00'::timestamp with time zone) AND (a.transact
ion_date < '2012-12-01 00:00:00+00'::timestamp with time zone))
-> Materialize (cost=0.00..1.62 rows=41 width=19) (actual
time=0.000..0.005 rows=41 loops=662583)
-> Seq Scan on public.product_operations o
(cost=0.00..1.41 rows=41 width=19) (actual time=7.207..7.214 rows=41 l
oops=1)
-> Append (cost=0.00..131.48 rows=7 width=12) (actual
time=0.058..0.060 rows=1 loops=662583)
-> Seq Scan on public.table_b b (cost=0.00..0.00 rows=1
width=22) (actual time=0.000..0.000 rows=0 loops=662
583)
Filter: ((b.product_id = 2) AND (b.organization_id = 3)
AND (b.year = 2013) AND (b.month = 3) AND (a.id = b.a_id))
-> Index Scan using table_b_ptid_orgid_ym_unq on
public.table_b_2 b (cost=0.00..131.48 rows=6 width=10) (a
ctual time=0.055..0.056 rows=1 loops=662583)
Index Cond: ((a.id = b.a_id) AND (b.organization_id = 3)
AND (b.year = 2013) AND (b.month = 3))
Filter: (b.product_id = 2)
Total runtime: 1578194.532 ms
(32 rows)
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
