The last query explain is with random_page_cost = 3.
Here is the query explain with random_page_cost = 2.5, that causes the 'shared memory segment' issue.
'Sort (cost=9255854.81..9356754.53 rows=40359886 width=64)'
' Sort Key: (to_char(b.week, 'dd-mm-yyyy'::text))'
' CTE sumorder'
' -> GroupAggregate (cost=692280.90..703914.76 rows=513746 width=16)'
' Group Key: (date_trunc('month'::text, to_timestamp("order".order_time)))'
' -> Sort (cost=692280.90..693590.12 rows=523689 width=14)'
' Sort Key: (date_trunc('month'::text, to_timestamp("order".order_time)))'
' -> Bitmap Heap Scan on "order" (cost=11461.44..642534.77 rows=523689 width=14)'
' Recheck Cond: ((service_id = ANY ('{SGN-BIKE,SGN-POOL}'::text[])) AND (order_time >= '1509469200'::double precision))'
' -> Bitmap Index Scan on order_service_id_order_time_idx (cost=0.00..11330.52 rows=523689 width=0)'
' Index Cond: ((service_id = ANY ('{SGN-BIKE,SGN-POOL}'::text[])) AND (order_time >= '1509469200'::double precision))'
' CTE badorder'
' -> Finalize GroupAggregate (cost=987667.04..989627.66 rows=15712 width=16)'
' Group Key: (date_trunc('month'::text, to_timestamp(order_1.order_time)))'
' -> Gather Merge (cost=987667.04..989326.48 rows=13100 width=16)'
' Workers Planned: 2'
' -> Partial GroupAggregate (cost=986667.01..986814.39 rows=6550 width=16)'
' Group Key: (date_trunc('month'::text, to_timestamp(order_1.order_time)))'
' -> Sort (cost=986667.01..986683.39 rows=6550 width=14)'
' Sort Key: (date_trunc('month'::text, to_timestamp(order_1.order_time)))'
' -> Parallel Bitmap Heap Scan on "order" order_1 (cost=35678.61..986251.83 rows=6550 width=14)'
' Recheck Cond: ((service_id = ANY ('{SGN-BIKE,SGN-POOL}'::text[])) AND (order_time >= '1483203600'::double precision))'
' Filter: ((rating_by_user < 5) AND (rating_by_user > 0))'
' -> Bitmap Index Scan on order_service_id_order_time_idx (cost=0.00..35674.67 rows=1740356 width=0)'
' Index Cond: ((service_id = ANY ('{SGN-BIKE,SGN-POOL}'::text[])) AND (order_time >= '1483203600'::double precision))'
' -> Merge Join (cost=60414.85..1271289.99 rows=40359886 width=64)'
' Merge Cond: (b.week = s.week)'
' -> Sort (cost=1409.33..1448.61 rows=15712 width=16)'
' Sort Key: b.week'
' -> CTE Scan on badorder b (cost=0.00..314.24 rows=15712 width=16)'
' -> Sort (cost=59005.52..60289.88 rows=513746 width=16)'
' Sort Key: s.week'
' -> CTE Scan on sumorder s (cost=0.00..10274.92 rows=513746 width=16)'
On Wed, Jan 3, 2018 at 11:43 AM, Thomas Munro <thomas.munro@xxxxxxxxxxxxxxxx> wrote:
On Wed, Jan 3, 2018 at 5:22 PM, Thuc Nguyen Canh
<thucnguyencanh@xxxxxxxxx> wrote:
> 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)'
I guess that must be EXPLAIN ANALYZE, because it includes "actual"
time, so it must be the plan when you set random_page_code >= 3,
right? Otherwise it would raise the error. Can you now set it to <
3 and do just EXPLAIN (no ANALYZE) so that we can see the failing plan
without trying to run it? I'm guessing it's different, because the
plan you showed doesn't look like it would want 50MB of DSM.
