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
>
