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.mu...@enterprisedb.com > wrote: > On Wed, Jan 3, 2018 at 5:22 PM, Thuc Nguyen Canh > <thucnguyenc...@gmail.com> 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. > > -- > Thomas Munro > http://www.enterprisedb.com >