From: | Steve Singer <ssinger(at)ca(dot)afilias(dot)info> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | slow bitmap heap scans on pg 9.2 |
Date: | 2013-04-10 13:49:55 |
Message-ID: | 51656E03.7000701@ca.afilias.info |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
Attachment | Content-Type | Size |
---|---|---|
hashjoin.txt | text/plain | 3.6 KB |
nestedloop.txt | text/plain | 3.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | ktm@rice.edu | 2013-04-10 13:56:57 | Re: slow bitmap heap scans on pg 9.2 |
Previous Message | Nik Tek | 2013-04-09 18:42:19 | Re: Find how much memory is postgres using |