Re: Query error: could not resize shared memory segment

Hi,
Here is the query plan of a query that causes above issue for any
random_page_cost < 3 (I keep the work_mem by default)

'Sort (cost=9441498.11..9542397.83 rows=40359886 width=64) (actual
time=33586.588..33586.590 rows=4 loops=1)'
' Sort Key: (to_char(b.week, 'dd-mm-yyyy'::text))'
' Sort Method: quicksort Memory: 25kB'
' CTE sumorder'
' -> GroupAggregate (cost=763614.25..775248.11 rows=513746 width=16)
(actual time=16587.507..17320.290 rows=4 loops=1)'
' Group Key: (date_trunc('month'::text,
to_timestamp("order".order_time)))'
' -> Sort (cost=763614.25..764923.47 rows=523689 width=14)
(actual time=16587.362..16913.230 rows=539089 loops=1)'
' Sort Key: (date_trunc('month'::text,
to_timestamp("order".order_time)))'
' Sort Method: quicksort Memory: 47116kB'
' -> Bitmap Heap Scan on "order" (cost=12679.94..713868.12
rows=523689 width=14) (actual time=516.465..15675.517 rows=539089 loops=1)'
' Recheck Cond: ((service_id = ANY
('{SGN-BIKE,SGN-POOL}'::text[])) AND (order_time >= '1509469200'::double
precision))'
' Heap Blocks: exact=242484'
' -> Bitmap Index Scan on
order_service_id_order_time_idx (cost=0.00..12549.02 rows=523689 width=0)
(actual time=425.697..425.697 rows=539089 loops=1)'
' Index Cond: ((service_id = ANY
('{SGN-BIKE,SGN-POOL}'::text[])) AND (order_time >= '1509469200'::double
precision))'
' CTE badorder'
' -> Finalize GroupAggregate (cost=993588.49..995549.11 rows=15712
width=16) (actual time=16257.720..16263.183 rows=13 loops=1)'
' Group Key: (date_trunc('month'::text,
to_timestamp(order_1.order_time)))'
' -> Gather Merge (cost=993588.49..995247.93 rows=13100
width=16) (actual time=16257.435..16263.107 rows=39 loops=1)'
' Workers Planned: 2'
' Workers Launched: 2'
' -> Partial GroupAggregate (cost=992588.46..992735.84
rows=6550 width=16) (actual time=16246.191..16251.348 rows=13 loops=3)'
' Group Key: (date_trunc('month'::text,
to_timestamp(order_1.order_time)))'
' -> Sort (cost=992588.46..992604.84 rows=6550
width=14) (actual time=16245.767..16248.316 rows=3715 loops=3)'
' Sort Key: (date_trunc('month'::text,
to_timestamp(order_1.order_time)))'
' Sort Method: quicksort Memory: 274kB'
' -> Parallel Seq Scan on "order" order_1
(cost=0.00..992173.28 rows=6550 width=14) (actual time=4.162..16230.174
rows=3715 loops=3)'
' Filter: ((order_time >=
'1483203600'::double precision) AND (service_id = ANY
('{SGN-BIKE,SGN-POOL}'::text[])) AND (rating_by_user < 5) AND
(rating_by_user > 0))'
' Rows Removed by Filter: 1801667'
' -> Merge Join (cost=60414.85..1271289.99 rows=40359886 width=64)
(actual time=33586.471..33586.503 rows=4 loops=1)'
' Merge Cond: (b.week = s.week)'
' -> Sort (cost=1409.33..1448.61 rows=15712 width=16) (actual
time=16263.259..16263.276 rows=13 loops=1)'
' Sort Key: b.week'
' Sort Method: quicksort Memory: 25kB'
' -> CTE Scan on badorder b (cost=0.00..314.24 rows=15712
width=16) (actual time=16257.737..16263.220 rows=13 loops=1)'
' -> Sort (cost=59005.52..60289.88 rows=513746 width=16) (actual
time=17320.506..17320.509 rows=4 loops=1)'
' Sort Key: s.week'
' Sort Method: quicksort Memory: 25kB'
' -> CTE Scan on sumorder s (cost=0.00..10274.92 rows=513746
width=16) (actual time=16587.532..17320.352 rows=4 loops=1)'
'Planning time: 3.202 ms'
'Execution time: 33589.971 ms'

On Wed, Jan 3, 2018 at 11:13 AM, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com
> wrote:

> On Wed, Jan 3, 2018 at 5:05 PM, Thuc Nguyen Canh
> <thucnguyencanh(at)gmail(dot)com> wrote:
> > The dynamic_shared_memory_type is posix, the before and after values for
> > work_mem are ~41MB and ~64MB.
> > I'm using a Digital Ocean vps of 16RAM 8 Cores.
> > For more information, I managed to reproduce this issue on a fresh vps
> after
> > I changed the random_page_cost from 4.0 to 1.1. So that said, I did
> reduce
> > the random_page_cost to 1.1, in order to optimize postgresql performance
> on
> > SSD (DO uses SSD) and got this issue.
>
> So you have 16GB of RAM and here we're failing to posix_fallocate()
> 50MB (actually we can't tell if it's the ftruncate() or
> posix_fallocate() call that failed, but the latter seems more likely
> since the former just creates a big hole in the underlying tmpfs
> file). Can you share the query plan (EXPLAIN SELECT ...)?
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>