pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedslow bitmap heap scans on pg 9.2
13+ messages / 3 participants
[nested] [flat]
* slow bitmap heap scans on pg 9.2
@ 2013-04-10 13:49 Steve Singer <ssinger@ca.afilias.info>
2013-04-10 13:56 ` Re: slow bitmap heap scans on pg 9.2 ktm@rice.edu <ktm@rice.edu>
2013-04-10 18:06 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Steve Singer @ 2013-04-10 13:49 UTC (permalink / raw)
To: 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
hashjoin.txt (text/plain, 3.6K) download
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)
nestedloop.txt (text/plain, 3.0K) download
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)
* Re: slow bitmap heap scans on pg 9.2
2013-04-10 13:49 slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
@ 2013-04-10 13:56 ` ktm@rice.edu <ktm@rice.edu>
2013-04-10 15:56 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
1 sibling, 1 reply; 13+ messages in thread
From: ktm@rice.edu @ 2013-04-10 13:56 UTC (permalink / raw)
To: Steve Singer <ssinger@ca.afilias.info>; +Cc: pgsql-performance
On Wed, Apr 10, 2013 at 09:49:55AM -0400, Steve Singer wrote:
> 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
Hi Steve,
The one thing that stands out to me is that you are working with 200GB of
data on a machine with 4-8GB of ram and you have the random_page_cost set
to 2.0. That is almost completely uncached and I would expect a value of
10 or more to be closer to reality.
Regards,
Ken
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: slow bitmap heap scans on pg 9.2
2013-04-10 13:49 slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-10 13:56 ` Re: slow bitmap heap scans on pg 9.2 ktm@rice.edu <ktm@rice.edu>
@ 2013-04-10 15:56 ` Steve Singer <ssinger@ca.afilias.info>
2013-04-10 17:49 ` Re: slow bitmap heap scans on pg 9.2 ktm@rice.edu <ktm@rice.edu>
2013-04-10 18:15 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Steve Singer @ 2013-04-10 15:56 UTC (permalink / raw)
To: ktm@rice.edu <ktm@rice.edu>; +Cc: pgsql-performance
On 13-04-10 09:56 AM, ktm@rice.edu wrote:
> On Wed, Apr 10, 2013 at 09:49:55AM -0400, Steve Singer wrote:
>
> Hi Steve,
>
> The one thing that stands out to me is that you are working with 200GB of
> data on a machine with 4-8GB of ram and you have the random_page_cost set
> to 2.0. That is almost completely uncached and I would expect a value of
> 10 or more to be closer to reality.
Setting random_page_cost to 15 makes the planner choose the nested-loop
plan (at least the date range I tried).
I thought that the point of effective cache size was to tell the planner
high likely it is for a random page to be in cache. With 200GB of data
for this query and an effective cache size of 3.5 GB I would have
expected that to be accounted for.
>
> Regards,
> Ken
>
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: slow bitmap heap scans on pg 9.2
2013-04-10 13:49 slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-10 13:56 ` Re: slow bitmap heap scans on pg 9.2 ktm@rice.edu <ktm@rice.edu>
2013-04-10 15:56 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
@ 2013-04-10 17:49 ` ktm@rice.edu <ktm@rice.edu>
1 sibling, 0 replies; 13+ messages in thread
From: ktm@rice.edu @ 2013-04-10 17:49 UTC (permalink / raw)
To: Steve Singer <ssinger@ca.afilias.info>; +Cc: pgsql-performance
On Wed, Apr 10, 2013 at 11:56:32AM -0400, Steve Singer wrote:
> On 13-04-10 09:56 AM, ktm@rice.edu wrote:
> >On Wed, Apr 10, 2013 at 09:49:55AM -0400, Steve Singer wrote:
>
> >
> >Hi Steve,
> >
> >The one thing that stands out to me is that you are working with 200GB of
> >data on a machine with 4-8GB of ram and you have the random_page_cost set
> >to 2.0. That is almost completely uncached and I would expect a value of
> >10 or more to be closer to reality.
>
> Setting random_page_cost to 15 makes the planner choose the
> nested-loop plan (at least the date range I tried).
>
> I thought that the point of effective cache size was to tell the
> planner high likely it is for a random page to be in cache. With
> 200GB of data for this query and an effective cache size of 3.5 GB I
> would have expected that to be accounted for.
>
For random_page_cost to be that low, the database would need to be
mostly cached. 3.5GB is almost 100X too small to do that unless your
query exhibits a large amount of locality of reference. Values for
random_page_cost between 10 and 20 are very reasonable for disk-bound
I/O scenarios.
Regards,
Ken
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: slow bitmap heap scans on pg 9.2
2013-04-10 13:49 slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-10 13:56 ` Re: slow bitmap heap scans on pg 9.2 ktm@rice.edu <ktm@rice.edu>
2013-04-10 15:56 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
@ 2013-04-10 18:15 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Jeff Janes @ 2013-04-10 18:15 UTC (permalink / raw)
To: Steve Singer <ssinger@ca.afilias.info>; +Cc: ktm@rice.edu <ktm@rice.edu>; pgsql-performance
On Wed, Apr 10, 2013 at 8:56 AM, Steve Singer <ssinger@ca.afilias.info>wrote:
> On 13-04-10 09:56 AM, ktm@rice.edu wrote:
>
>> On Wed, Apr 10, 2013 at 09:49:55AM -0400, Steve Singer wrote:
>>
>
>
>> Hi Steve,
>>
>> The one thing that stands out to me is that you are working with 200GB of
>> data on a machine with 4-8GB of ram and you have the random_page_cost set
>> to 2.0. That is almost completely uncached and I would expect a value of
>> 10 or more to be closer to reality.
>>
>
> Setting random_page_cost to 15 makes the planner choose the nested-loop
> plan (at least the date range I tried).
>
> I thought that the point of effective cache size was to tell the planner
> high likely it is for a random page to be in cache.
e_c_s tells it how likely it is to still be in cache the second (and
subsequent) time the page is visited during the *same query*. It doesn't
tell it how likely it is to be in cache the first time it is needed in a
given query. (Also, e_c_s is irrelevant for bitmap scans, as they
inherently hit every block only once)
Also, it doesn't tell how expensive it is to bring it into cache when it is
needed. That is what random_page_cost is for. If you tell that those
fetches are going to be cheap, then it doesn't matter so much how many of
them it is going to have to do.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: slow bitmap heap scans on pg 9.2
2013-04-10 13:49 slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
@ 2013-04-10 18:06 ` Jeff Janes <jeff.janes@gmail.com>
2013-04-10 23:54 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
1 sibling, 1 reply; 13+ messages in thread
From: Jeff Janes @ 2013-04-10 18:06 UTC (permalink / raw)
To: Steve Singer <ssinger@ca.afilias.info>; +Cc: pgsql-performance
On Wed, Apr 10, 2013 at 6:49 AM, Steve Singer <ssinger@ca.afilias.info>wrote:
> 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)
>
I think the index recheck means your bitmap is overflowing (i.e. needing
more space than work_mem) and so keeping only the pages which have at least
one match, which means all rows in those pages need to be rechecked. How
many rows does the table have? You might be essentially doing a seq scan,
but with the additional overhead of the bitmap machinery. Could you do
"explain (analyze,buffers)", preferably with track_io_timing set to on?
Cheers,
Jeff
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: slow bitmap heap scans on pg 9.2
2013-04-10 13:49 slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-10 18:06 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
@ 2013-04-10 23:54 ` Steve Singer <ssinger@ca.afilias.info>
2013-04-11 14:20 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
0 siblings, 1 reply; 13+ messages in thread
From: Steve Singer @ 2013-04-10 23:54 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
On 13-04-10 02:06 PM, Jeff Janes wrote:
> On Wed, Apr 10, 2013 at 6:49 AM, Steve Singer <ssinger@ca.afilias.info
> <mailto:ssinger@ca.afilias.info>> wrote:
>
> I think the index recheck means your bitmap is overflowing (i.e. needing
> more space than work_mem) and so keeping only the pages which have at
> least one match, which means all rows in those pages need to be
> rechecked. How many rows does the table have? You might be essentially
> doing a seq scan, but with the additional overhead of the bitmap
> machinery. Could you do "explain (analyze,buffers)", preferably with
> track_io_timing set to on?
>
table_b has 1,530,710,469 rows
Attached is the output with track_io_timings and buffers.
> Cheers,
>
> Jeff
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
hashjoin_buffers.txt (text/plain, 4.2K) download
HashAggregate (cost=11768301.08..11770515.08 rows=147600 width=51) (actual time=3518538.111..3518539.201 rows=36 loops=1)
Buffers: shared hit=8892878 read=4997334
I/O Timings: read=1935289.861
-> Hash Join (cost=1282646.06..11041507.43 rows=700524 width=51) (actual time=94631.716..2065378.607 rows=543671 loops=1)
Hash Cond: (a.product_operation = o.id)
Buffers: shared hit=3419 read=4626531
I/O Timings: read=1738580.922
-> Hash Right Join (cost=1282644.14..11031873.30 rows=700524 width=40) (actual time=94631.646..2064079.068 rows=543671 l
oops=1)
Hash Cond: (b.a_id = a.id)
Buffers: shared hit=3418 read=4626531
I/O Timings: read=1738580.922
-> Bitmap Heap Scan on public.table_b_2 b (cost=173185.49..9820205.86 rows=8462538 width=10) (actual ti
me=10044.901..1970005.160 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)
Buffers: shared hit=232 read=4606751
I/O Timings: read=1726380.478
-> Bitmap Index Scan on rec_rev_2_orgid_ym_unq (cost=0.00..171069.85 rows=8462538 width=0) (actual time=9410
.598..9410.598 rows=9749680 loops=1)
Index Cond: ((b.organization_id = 3) AND (b.year = 2013) AND (b.month = 3))
Buffers: shared read=37360
I/O Timings: read=3460.299
-> Hash (cost=1107975.15..1107975.15 rows=118680 width=34) (actual time=13732.784..13732.784 rows=543671 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 37388kB
Buffers: shared hit=3186 read=19780
I/O Timings: read=12200.444
-> Append (cost=0.00..1107975.15 rows=118680 width=34) (actual time=935.605..13395.517 rows=543671 loops=1)
Buffers: shared hit=3186 read=19780
I/O Timings: read=12200.444
-> Seq Scan on public.table_a a (cost=0.00..0.00 rows=1 width=60) (actual time=0.001..0.00
1 rows=0 loops=1)
Filter: ((a.twin_id IS NULL) AND (a.transaction_date >= '2009-09-01 00:00:00+00'::timestamp with t
ime zone) AND (a.transaction_date < '2009-10-01 00:00:00+00'::timestamp with time zone) AND (a.product_id = 2) AND ((a.trans_type_i
d = ANY ('{2,3,4}'::integer[])) OR (a.trans_type_id IS NULL)))
-> Bitmap Heap Scan on public.table_a_2 a (cost=13335.33..1107975.15 rows=118679 width=34)
(actual time=935.603..13301.854 rows=543671 loops=1)
Recheck Cond: ((a.transaction_date >= '2009-09-01 00:00:00+00'::timestamp with time zone) AND (a.t
ransaction_date < '2009-10-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}'::intege
r[])) OR (a.trans_type_id IS NULL)))
Rows Removed by Filter: 302939
Buffers: shared hit=3186 read=19780
I/O Timings: read=12200.444
-> Bitmap Index Scan on table_a_2_trans_date_idx (cost=0.00..13305.66 rows=858651 width=0)
(actual time=303.756..303.756 rows=846610 loops=1)
Index Cond: ((a.transaction_date >= '2009-09-01 00:00:00+00'::timestamp with time zone) AND
(a.transaction_date < '2009-10-01 00:00:00+00'::timestamp with time zone))
Buffers: shared read=2317
I/O Timings: read=170.564
-> Hash (cost=1.41..1.41 rows=41 width=19) (actual time=0.036..0.036 rows=41 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 3kB
Buffers: shared hit=1
-> Seq Scan on public.product_operations o (cost=0.00..1.41 rows=41 width=19) (actual time=0.009..0.018 rows=41 lo
ops=1)
Buffers: shared hit=1
Total runtime: 3518541.146 ms
(55 rows)
steve_test=#
* Re: slow bitmap heap scans on pg 9.2
2013-04-10 13:49 slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-10 18:06 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
2013-04-10 23:54 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
@ 2013-04-11 14:20 ` Steve Singer <ssinger@ca.afilias.info>
2013-04-13 01:20 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Steve Singer @ 2013-04-11 14:20 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
On 13-04-10 07:54 PM, Steve Singer wrote:
> On 13-04-10 02:06 PM, Jeff Janes wrote:
>> On Wed, Apr 10, 2013 at 6:49 AM, Steve Singer <ssinger@ca.afilias.info
>> <mailto:ssinger@ca.afilias.info>> wrote:
>>
>
>> I think the index recheck means your bitmap is overflowing (i.e. needing
>> more space than work_mem) and so keeping only the pages which have at
>> least one match, which means all rows in those pages need to be
>> rechecked. How many rows does the table have? You might be essentially
>> doing a seq scan, but with the additional overhead of the bitmap
>> machinery. Could you do "explain (analyze,buffers)", preferably with
>> track_io_timing set to on?
>>
>
> table_b has 1,530,710,469 rows
>
> Attached is the output with track_io_timings and buffers.
>
I've done some more testing with a random_page_cost=20.
This gives me the nested-loop plan for the various date ranges I've tried.
However table_a_2 and table_b_2 are actually partition tables. This
query only needs to look at a single partition. When I run this same
query against a different partition (a smaller partition, but still
bigger than cache) it picks hash join plan involving a seq scan of
table_b but no bitmap index scan. On this partition the hash-join
plans tend to take 15 minutes versus 2 minutes when I disable hashjoin
plans. Bumping random_page_cost higher doesn't fix this.
I think the reason why it is picking the hash join based plans is
because of
Index Scan using table_b_1_ptid_orgid_ym_unq on table_b_1 b
(cost=0.00..503.86 rows=1 width=10) (actual time=0.016..0.017 rows=1
loops=414249)
Index Cond: ((a.id = a_id) AND (organization_id =
2) AND (year = 2013) AND (month = 3))
Filter: (product_id = 1)
I think we are over-estimating the cost of the index scans in the inner
loop. This seems similar to what was discussed a few months ago
http://www.postgresql.org/message-id/092a01cdd230$ff6143c0$fe23cb40$@foo.me.uk
This version of PG should have 3e9960e9d935e7e applied. I am trying to
get the database copied to a machine where I can easily switch PG
versions and test this against something prior to that commit and also
against a 9.3 build.
Steve
>
>
>> Cheers,
>>
>> Jeff
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
nestedloop_1.txt (text/plain, 3.6K) download
reset enable_hashjoin=false;
set enable_mergejoin=false;
set random_page_cost=20;
HashAggregate (cost=126233158.01..126233163.21 rows=347 width=53) (actual time=82471.963..82471.983 rows=26 loops=1)
-> Nested Loop (cost=0.00..125974847.48 rows=248974 width=53) (actual time=473.558..39846.799 rows=414249 loops=1)
Join Filter: (a.product_operation = o.id)
Rows Removed by Join Filter: 16569960
-> Nested Loop Left Join (cost=0.00..125821726.96 rows=248974 width=42) (actual time=473.500..35090.816 rows=414249 loops=1)
-> Index Scan using trans_posted_1_trans_date_idx on table_a_1 a (cost=0.00..371973.35 rows=248974 width=36) (actual time=81.080..27242.825 rows=414249 loops=1)
Index Cond: ((transaction_date >= '2013-01-01 00:00:00+00'::timestamp with time zone) AND (transaction_date < '2013-02-01 00:00:00+00'::timestamp with time zone))
Filter: ((twin_id IS NULL) AND (product_id = 1) AND ((trans_type_id = ANY ('{2,3,4}'::integer[])) OR (trans_type_id IS NULL)))
Rows Removed by Filter: 546641
-> Index Scan using table_b_1_ptid_orgid_ym_unq on table_b_1 b (cost=0.00..503.86 rows=1 width=10) (actual time=0.016..0.017 rows=1 loops=414249)
Index Cond: ((a.id = a_id) AND (organization_id = 2) AND (year = 2013) AND (month = 3))
Filter: (product_id = 1)
-> Materialize (cost=0.00..1.62 rows=41 width=19) (actual time=0.000..0.004 rows=41 loops=414249)
-> Seq Scan on product_operations o (cost=0.00..1.41 rows=41 width=19) (actual time=0.026..0.036 rows=41 loops=1)
Total runtime: 82472.294 ms
(15 rows)
set enable_hashjoin=true;
set enable_mergejoin=true;
set random_page_cost=20;
HashAggregate (cost=19998189.54..19998194.75 rows=347 width=53) (actual time=825795.403..825795.420 rows=26 loops=1)
-> Nested Loop (cost=375085.52..19739879.02 rows=248974 width=53) (actual time=727075.300..782232.417 rows=414249 loops=1)
Join Filter: (a.product_operation = o.id)
Rows Removed by Join Filter: 16569960
-> Hash Right Join (cost=375085.52..19586758.50 rows=248974 width=42) (actual time=727075.034..777530.559 rows=414249 loops=1)
Hash Cond: (b.a_id = a.id)
-> Seq Scan on table_b_1 b (cost=0.00..19153996.20 rows=4605486 width=10) (actual time=193813.645..733574.903 rows=6416840 loops=1)
Filter: ((product_id = 1) AND (organization_id = 2) AND (year = 2013) AND (month = 3))
Rows Removed by Filter: 596869132
-> Hash (cost=371973.35..371973.35 rows=248974 width=36) (actual time=23994.832..23994.832 rows=414249 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 27509kB
-> Index Scan using table_a_1_trans_date_idx on table_a_1 a (cost=0.00..371973.35 rows=248974 width=36) (actual time=1.051..23615.761 rows=414249 loops=1)
Index Cond: ((transaction_date >= '2013-01-01 00:00:00+00'::timestamp with time zone) AND (transaction_date < '2013-02-01 00:00:00+00'::timestamp with time zone))
Filter: ((twin_id IS NULL) AND (product_id = 1) AND ((trans_type_id = ANY ('{2,3,4}'::integer[])) OR (trans_type_id IS NULL)))
Rows Removed by Filter: 546641
-> Materialize (cost=0.00..1.62 rows=41 width=19) (actual time=0.000..0.004 rows=41 loops=414249)
-> Seq Scan on product_operations o (cost=0.00..1.41 rows=41 width=19) (actual time=0.175..0.189 rows=41 loops=1)
Total runtime: 825802.496 ms
* Re: slow bitmap heap scans on pg 9.2
2013-04-10 13:49 slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-10 18:06 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
2013-04-10 23:54 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-11 14:20 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
@ 2013-04-13 01:20 ` Jeff Janes <jeff.janes@gmail.com>
2013-04-13 16:14 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
0 siblings, 1 reply; 13+ messages in thread
From: Jeff Janes @ 2013-04-13 01:20 UTC (permalink / raw)
To: Steve Singer <ssinger@ca.afilias.info>; +Cc: pgsql-performance
On Thursday, April 11, 2013, Steve Singer wrote:
>
> I think the reason why it is picking the hash join based plans is because
> of
>
> Index Scan using table_b_1_ptid_orgid_ym_unq on table_b_1 b
> (cost=0.00..503.86 rows=1 width=10) (actual time=0.016..0.017 rows=1
> loops=414249)
> Index Cond: ((a.id = a_id) AND (organization_id = 2)
> AND (year = 2013) AND (month = 3))
> Filter: (product_id = 1)
>
Trying to reason about how the planner estimates costs for the inner side
of nested loops makes my head hurt.
So before doing that, could you run explain (analyze,buffers) on both of
these much simpler (but hopefully morally equivalent to this planner node)
sql:
select * from table_b_1_b where a_id = <some plausible value> and
organization_id=2 and year=2013 and month=3
select * from table_b_1_b where a_id = <some plausible value> and
organization_id=2 and year=2013 and month=3 and product_id=1
Of particular interest here is whether the estimate of 1 row is due to the
specificity of the filter, or if the index clauses alone are specific
enough to drive that estimate. (If you get many rows without the
product_id filter, that would explain the high estimate.).
Please run with the default cost parameters, or if you can't get the right
plan with the defaults, specify what the used parameters were.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: slow bitmap heap scans on pg 9.2
2013-04-10 13:49 slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-10 18:06 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
2013-04-10 23:54 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-11 14:20 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-13 01:20 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
@ 2013-04-13 16:14 ` Steve Singer <ssinger@ca.afilias.info>
2013-04-13 20:54 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Steve Singer @ 2013-04-13 16:14 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
On 13-04-12 09:20 PM, Jeff Janes wrote:
> On Thursday, April 11, 2013, Steve Singer wrote:
>
>
> I think the reason why it is picking the hash join based plans is
> because of
>
> Index Scan using table_b_1_ptid_orgid_ym_unq on table_b_1 b
> (cost=0.00..503.86 rows=1 width=10) (actual time=0.016..0.017 rows=1
> loops=414249)
> Index Cond: ((a.id <http://a.id; = a_id) AND
> (organization_id = 2) AND (year = 2013) AND (month = 3))
> Filter: (product_id = 1)
>
>
>
> Trying to reason about how the planner estimates costs for the inner
> side of nested loops makes my head hurt.
> So before doing that, could you run explain (analyze,buffers) on both of
> these much simpler (but hopefully morally equivalent to this planner
> node) sql:
>
> select * from table_b_1_b where a_id = <some plausible value> and
> organization_id=2 and year=2013 and month=3
>
> select * from table_b_1_b where a_id = <some plausible value> and
> organization_id=2 and year=2013 and month=3 and product_id=1
>
table_b_1 is a partition of table_b on product_id so when querying
table table_b_1 directly they are equivalent
explain (analyze,buffers) select * FROM table_b_1 where a_id=1128944 and
organization_id=2 and year=2013 and month=3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
-----------
Index Scan using table_b_1_ptid_orgid_ym_unq on table_b_1
(cost=0.00..50.73 rows=1 width=56) (actual time=60.328..60.330 rows=
1 loops=1)
Index Cond: ((a_id = 1128944) AND (organization_id = 2) AND (year =
2013) AND (month = 3))
Buffers: shared hit=1 read=5
Total runtime: 60.378 ms
(4 rows)
The plans are the same if I do or do not specify the product_id in the
where clause (if I query the parent table and neglect to query the query
clause it of course queries all the other partitions)
>
> Of particular interest here is whether the estimate of 1 row is due to
> the specificity of the filter, or if the index clauses alone are
> specific enough to drive that estimate. (If you get many rows without
> the product_id filter, that would explain the high estimate.).
The index clauses alone , we normally expect 1 row back for a query like
that.
>
> Please run with the default cost parameters, or if you can't get the
> right plan with the defaults, specify what the used parameters were.
indexTotalCost += index->pages * spc_random_page_cost / 100000.0;
Is driving my high costs on the inner loop. The index has 2-5 million
pages depending on the partition . If I run this against 9.2.2 with /
10000.0 the estimate is even higher.
If I try this with this with the
*indexTotalCost += log(1.0 + index->pages / 10000.0) * spc_random_page_cost;
from 9.3 and I play I can make this work I can it pick the plan on some
partitions with product_id=2 but not product_id=1. If I remove the
fudge-factor cost adjustment line I get the nested-loop plan always.
Breaking the index into smaller partial indexes for each year seems to
be giving me the plans I want with random_page_cost=2 (I might also try
partial indexes on the month).
Even with the 9.3 log based fudge-factor we are seeing the fudge-factor
being big enough so that the planner is picking a table scan over the
index. A lot of loop iterations can be satisfied by cached pages of the
index the fudge-factor doesn't really account for this.
>
> Cheers,
>
> Jeff
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: slow bitmap heap scans on pg 9.2
2013-04-10 13:49 slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-10 18:06 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
2013-04-10 23:54 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-11 14:20 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-13 01:20 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
2013-04-13 16:14 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
@ 2013-04-13 20:54 ` Jeff Janes <jeff.janes@gmail.com>
2013-04-15 00:06 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
0 siblings, 1 reply; 13+ messages in thread
From: Jeff Janes @ 2013-04-13 20:54 UTC (permalink / raw)
To: Steve Singer <ssinger@ca.afilias.info>; +Cc: pgsql-performance
On Sat, Apr 13, 2013 at 9:14 AM, Steve Singer <ssinger@ca.afilias.info>wrote:
>
>>
>>
> indexTotalCost += index->pages * spc_random_page_cost / 100000.0;
>
> Is driving my high costs on the inner loop. The index has 2-5 million
> pages depending on the partition . If I run this against 9.2.2 with /
> 10000.0 the estimate is even higher.
>
> If I try this with this with the
>
> *indexTotalCost += log(1.0 + index->pages / 10000.0) *
> spc_random_page_cost;
>
> from 9.3 and I play I can make this work I can it pick the plan on some
> partitions with product_id=2 but not product_id=1. If I remove the
> fudge-factor cost adjustment line I get the nested-loop plan always.
>
That was only temporarily the formula during 9.3dev. Tom re-did that
entire part of the code rather substantially in the current tip of 9.3
(commit 31f38f28b00cbe2b). Now it is based on the number of tuples, and
the height, rather than pages, and is multiplied by the cpu_operator_cost
not the random_page_cost.
descentCost = ceil(log(index->tuples) / log(2.0)) * cpu_operator_cost;
...
descentCost = (index->tree_height + 1) * 50.0 * cpu_operator_cost;
>
> Breaking the index into smaller partial indexes for each year seems to be
> giving me the plans I want with random_page_cost=2 (I might also try
> partial indexes on the month).
>
> Even with the 9.3 log based fudge-factor we are seeing the fudge-factor
> being big enough so that the planner is picking a table scan over the index.
Have you tried it under 9.3 HEAD, rather than just back-porting the
temporary
*indexTotalCost += log(1.0 + index->pages / 10000.0) * spc_random_page_cost;
code into 9.2?
If you are trying to make your own private copy of 9.2, then removing the
fudge factor altogether is probably the way to go. But if you want to help
improve future versions, you probably need to test with the most up-to-date
dev version.
> A lot of loop iterations can be satisfied by cached pages of the index
> the fudge-factor doesn't really account for this.
>
Setting random_page_cost to 2 is already telling it that most of fetches
are coming from the cache. Of course for the upper blocks of an index even
more than "most" are likely to be, but the latest dev code takes care of
that.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: slow bitmap heap scans on pg 9.2
2013-04-10 13:49 slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-10 18:06 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
2013-04-10 23:54 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-11 14:20 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-13 01:20 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
2013-04-13 16:14 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-13 20:54 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
@ 2013-04-15 00:06 ` Steve Singer <ssinger@ca.afilias.info>
2013-04-19 17:04 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
0 siblings, 1 reply; 13+ messages in thread
From: Steve Singer @ 2013-04-15 00:06 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
On 13-04-13 04:54 PM, Jeff Janes wrote:
> On Sat, Apr 13, 2013 at 9:14 AM, Steve Singer <ssinger@ca.afilias.info
> <mailto:ssinger@ca.afilias.info>> wrote:
>
>
> indexTotalCost += index->pages * spc_random_page_cost / 100000.0;
>
> Is driving my high costs on the inner loop. The index has 2-5
> million pages depending on the partition . If I run this against
> 9.2.2 with / 10000.0 the estimate is even higher.
>
> If I try this with this with the
>
> *indexTotalCost += log(1.0 + index->pages / 10000.0) *
> spc_random_page_cost;
>
> from 9.3 and I play I can make this work I can it pick the plan on
> some partitions with product_id=2 but not product_id=1. If I
> remove the fudge-factor cost adjustment line I get the nested-loop
> plan always.
>
>
> That was only temporarily the formula during 9.3dev. Tom re-did that
> entire part of the code rather substantially in the current tip of 9.3
> (commit 31f38f28b00cbe2b). Now it is based on the number of tuples, and
> the height, rather than pages, and is multiplied by the
> cpu_operator_cost not the random_page_cost.
>
> descentCost = ceil(log(index->tuples) / log(2.0)) * cpu_operator_cost;
>
> ...
>
> descentCost = (index->tree_height + 1) * 50.0 * cpu_operator_cost;
>
>
> Breaking the index into smaller partial indexes for each year seems
> to be giving me the plans I want with random_page_cost=2 (I might
> also try partial indexes on the month).
>
> Even with the 9.3 log based fudge-factor we are seeing the
> fudge-factor being big enough so that the planner is picking a table
> scan over the index.
>
>
> Have you tried it under 9.3 HEAD, rather than just back-porting the
> temporary
> *indexTotalCost += log(1.0 + index->pages / 10000.0) * spc_random_page_cost;
> code into 9.2?
>
> If you are trying to make your own private copy of 9.2, then removing
> the fudge factor altogether is probably the way to go. But if you want
> to help improve future versions, you probably need to test with the most
> up-to-date dev version.
I will do that in a few days. I don't have enough disk space on this
dev server to have a 9.2 datadir and a 9.3 one for this database. Once
I have a solution that I can use with 9.2 firmed up I can upgrade the
datadir to 9.3 and test this. I am hoping I can get a set of partial
indexes that will give good results with an unmodified 9.2, so far that
looks promising but I still have more cases to verify (these indexes
take a while to build).
>
> A lot of loop iterations can be satisfied by cached pages of the
> index the fudge-factor doesn't really account for this.
>
>
>
> Setting random_page_cost to 2 is already telling it that most of fetches
> are coming from the cache. Of course for the upper blocks of an index
> even more than "most" are likely to be, but the latest dev code takes
> care of that.
>
> Cheers,
>
> Jeff
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: slow bitmap heap scans on pg 9.2
2013-04-10 13:49 slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-10 18:06 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
2013-04-10 23:54 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-11 14:20 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-13 01:20 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
2013-04-13 16:14 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-13 20:54 ` Re: slow bitmap heap scans on pg 9.2 Jeff Janes <jeff.janes@gmail.com>
2013-04-15 00:06 ` Re: slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
@ 2013-04-19 17:04 ` Steve Singer <ssinger@ca.afilias.info>
0 siblings, 0 replies; 13+ messages in thread
From: Steve Singer @ 2013-04-19 17:04 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
On 13-04-14 08:06 PM, Steve Singer wrote:
> On 13-04-13 04:54 PM, Jeff Janes wrote:
>>
>> If you are trying to make your own private copy of 9.2, then removing
>> the fudge factor altogether is probably the way to go. But if you want
>> to help improve future versions, you probably need to test with the most
>> up-to-date dev version.
>
> I will do that in a few days. I don't have enough disk space on this
> dev server to have a 9.2 datadir and a 9.3 one for this database. Once
> I have a solution that I can use with 9.2 firmed up I can upgrade the
> datadir to 9.3 and test this. I am hoping I can get a set of partial
> indexes that will give good results with an unmodified 9.2, so far that
> looks promising but I still have more cases to verify (these indexes
> take a while to build).
>
I've run these queries against a recent master/9.3 and the planner is
picking the nested-loop plan for using the full index. This is with
random_page_cost=2.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2013-04-19 17:04 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-04-10 13:49 slow bitmap heap scans on pg 9.2 Steve Singer <ssinger@ca.afilias.info>
2013-04-10 13:56 ` ktm@rice.edu <ktm@rice.edu>
2013-04-10 15:56 ` Steve Singer <ssinger@ca.afilias.info>
2013-04-10 17:49 ` ktm@rice.edu <ktm@rice.edu>
2013-04-10 18:15 ` Jeff Janes <jeff.janes@gmail.com>
2013-04-10 18:06 ` Jeff Janes <jeff.janes@gmail.com>
2013-04-10 23:54 ` Steve Singer <ssinger@ca.afilias.info>
2013-04-11 14:20 ` Steve Singer <ssinger@ca.afilias.info>
2013-04-13 01:20 ` Jeff Janes <jeff.janes@gmail.com>
2013-04-13 16:14 ` Steve Singer <ssinger@ca.afilias.info>
2013-04-13 20:54 ` Jeff Janes <jeff.janes@gmail.com>
2013-04-15 00:06 ` Steve Singer <ssinger@ca.afilias.info>
2013-04-19 17:04 ` Steve Singer <ssinger@ca.afilias.info>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox