Re: BUG #15225: [XX000] ERROR: invalid DSA memory alloc request size 1073741824 / Where: parallel worker

Hi Thomas, thanks for your help.

The problem seems to be not very consistent. Without any changes to either
database or query I now get:
org.postgresql.util.PSQLException: ERROR: could not resize shared memory
segment "/PostgreSQL.347733883" to 2147483648 bytes: No space left on device
Just executing the statement WITHOUT the explain does work properly, so it
is the explain part that seems to trigger the issue.

I set enable_parallel_hash = off in the code and now the explain part
works properly. The plan XML looks like this:
<explain xmlns="http://www.postgresql.org/2009/explain">
<Query>
<Plan>
<Node-Type>Aggregate</Node-Type>
<Strategy>Sorted</Strategy>
<Partial-Mode>Simple</Partial-Mode>
<Parallel-Aware>false</Parallel-Aware>
<Startup-Cost>1524879.03</Startup-Cost>
<Total-Cost>1524919.24</Total-Cost>
<Plan-Rows>1462</Plan-Rows>
<Plan-Width>62</Plan-Width>
<Actual-Startup-Time>878413.051</Actual-Startup-Time>
<Actual-Total-Time>936007.744</Actual-Total-Time>
<Actual-Rows>677107</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Output>
<Item>(COALESCE(tijd.tijdkey, 'Unknown'::text))</Item>
<Item>(COALESCE(eenheid_pe.id_s, '-1'::integer))</Item>
<Item>(COALESCE(adres_pe.id_s, '-1'::integer))</Item>
<Item>(sum(prijscomponent_pe.bedrag))::numeric(23,2)</Item>
</Output>
<Group-Key>
<Item>(COALESCE(tijd.tijdkey, 'Unknown'::text))</Item>
<Item>(COALESCE(eenheid_pe.id_s, '-1'::integer))</Item>
<Item>(COALESCE(adres_pe.id_s, '-1'::integer))</Item>
</Group-Key>
<Shared-Hit-Blocks>2438</Shared-Hit-Blocks>
<Shared-Read-Blocks>322664</Shared-Read-Blocks>
<Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks>
<Shared-Written-Blocks>0</Shared-Written-Blocks>
<Local-Hit-Blocks>0</Local-Hit-Blocks>
<Local-Read-Blocks>0</Local-Read-Blocks>
<Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>
<Local-Written-Blocks>0</Local-Written-Blocks>
<Temp-Read-Blocks>12221239</Temp-Read-Blocks>
<Temp-Written-Blocks>12221247</Temp-Written-Blocks>
<Plans>
<Plan>
<Node-Type>Sort</Node-Type>
<Parent-Relationship>Outer</Parent-Relationship>
<Parallel-Aware>false</Parallel-Aware>
<Startup-Cost>1524879.03</Startup-Cost>
<Total-Cost>1524882.69</Total-Cost>
<Plan-Rows>1462</Plan-Rows>
<Plan-Width>44</Plan-Width>
<Actual-Startup-Time>878412.942</Actual-Startup-Time>
<Actual-Total-Time>915326.679</Actual-Total-Time>
<Actual-Rows>184162324</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Output>
<Item>(COALESCE(tijd.tijdkey, 'Unknown'::text))</Item>
<Item>(COALESCE(eenheid_pe.id_s, '-1'::integer))</Item>
<Item>(COALESCE(adres_pe.id_s, '-1'::integer))</Item>
<Item>prijscomponent_pe.bedrag</Item>
</Output>
<Sort-Key>
<Item>(COALESCE(tijd.tijdkey, 'Unknown'::text))</Item>
<Item>(COALESCE(eenheid_pe.id_s, '-1'::integer))</Item>
<Item>(COALESCE(adres_pe.id_s, '-1'::integer))</Item>
</Sort-Key>
<Sort-Method>external merge</Sort-Method>
<Sort-Space-Used>5464336</Sort-Space-Used>
<Sort-Space-Type>Disk</Sort-Space-Type>
<Shared-Hit-Blocks>2438</Shared-Hit-Blocks>
<Shared-Read-Blocks>322664</Shared-Read-Blocks>
<Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks>
<Shared-Written-Blocks>0</Shared-Written-Blocks>
<Local-Hit-Blocks>0</Local-Hit-Blocks>
<Local-Read-Blocks>0</Local-Read-Blocks>
<Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>
<Local-Written-Blocks>0</Local-Written-Blocks>
<Temp-Read-Blocks>12221239</Temp-Read-Blocks>
<Temp-Written-Blocks>12221247</Temp-Written-Blocks>
<Plans>
<Plan>
<Node-Type>Hash Join</Node-Type>
<Parent-Relationship>Outer</Parent-Relationship>
<Parallel-Aware>false</Parallel-Aware>
<Join-Type>Left</Join-Type>
<Startup-Cost>1363078.23</Startup-Cost>
<Total-Cost>1524802.18</Total-Cost>
<Plan-Rows>1462</Plan-Rows>
<Plan-Width>44</Plan-Width>
<Actual-Startup-Time>451502.428</Actual-Startup-Time>
<Actual-Total-Time>629056.050</Actual-Total-Time>
<Actual-Rows>184162324</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Output>
<Item>COALESCE(tijd.tijdkey, 'Unknown'::text)</Item>
<Item>COALESCE(eenheid_pe.id_s, '-1'::integer)</Item>
<Item>COALESCE(adres_pe.id_s, '-1'::integer)</Item>
<Item>prijscomponent_pe.bedrag</Item>
</Output>
<Inner-Unique>false</Inner-Unique>
<Hash-Cond>(l_adres_eenheid_pe.id_h_adres =
adres_pe.id_h_adres)</Hash-Cond>
<Join-Filter>((adres_pe.dv_start_dts &lt;= tijd.einddatum)
AND (adres_pe.dv_end_dts &gt; tijd.einddatum))</Join-Filter>
<Rows-Removed-by-Join-Filter>0</Rows-Removed-by-Join-Filter>
<Shared-Hit-Blocks>2432</Shared-Hit-Blocks>
<Shared-Read-Blocks>322664</Shared-Read-Blocks>
<Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks>
<Shared-Written-Blocks>0</Shared-Written-Blocks>
<Local-Hit-Blocks>0</Local-Hit-Blocks>
<Local-Read-Blocks>0</Local-Read-Blocks>
<Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>
<Local-Written-Blocks>0</Local-Written-Blocks>
<Temp-Read-Blocks>11538197</Temp-Read-Blocks>
<Temp-Written-Blocks>11538197</Temp-Written-Blocks>
<Plans>
<Plan>
<Node-Type>Hash Join</Node-Type>
<Parent-Relationship>Outer</Parent-Relationship>
<Parallel-Aware>false</Parallel-Aware>
<Join-Type>Left</Join-Type>
<Startup-Cost>1360706.69</Startup-Cost>
<Total-Cost>1522403.22</Total-Cost>
<Plan-Rows>1462</Plan-Rows>
<Plan-Width>48</Plan-Width>
<Actual-Startup-Time>451482.554</Actual-Startup-Time>
<Actual-Total-Time>604384.623</Actual-Total-Time>
<Actual-Rows>184162324</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Output>
<Item>tijd.tijdkey</Item>
<Item>tijd.einddatum</Item>
<Item>eenheid_pe.id_s</Item>
<Item>prijscomponent_pe.bedrag</Item>
<Item>l_adres_eenheid_pe.id_h_adres</Item>
</Output>
<Inner-Unique>false</Inner-Unique>
<Hash-Cond>(l_adres_eenheid_pe.id_l =
s_l_adres_eenheid_ssm_pe.id_l)</Hash-Cond>
<Join-Filter>((s_l_adres_eenheid_ssm_pe.dv_start_dts
&lt;= tijd.einddatum) AND (s_l_adres_eenheid_ssm_pe.dv_end_dts &gt;
tijd.einddatum))</Join-Filter>

<Rows-Removed-by-Join-Filter>0</Rows-Removed-by-Join-Filter>
<Shared-Hit-Blocks>2432</Shared-Hit-Blocks>
<Shared-Read-Blocks>321535</Shared-Read-Blocks>
<Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks>
<Shared-Written-Blocks>0</Shared-Written-Blocks>
<Local-Hit-Blocks>0</Local-Hit-Blocks>
<Local-Read-Blocks>0</Local-Read-Blocks>
<Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>
<Local-Written-Blocks>0</Local-Written-Blocks>
<Temp-Read-Blocks>11538197</Temp-Read-Blocks>
<Temp-Written-Blocks>11538197</Temp-Written-Blocks>
<Plans>
<Plan>
<Node-Type>Hash Join</Node-Type>
<Parent-Relationship>Outer</Parent-Relationship>
<Parallel-Aware>false</Parallel-Aware>
<Join-Type>Left</Join-Type>
<Startup-Cost>1359001.01</Startup-Cost>
<Total-Cost>1520670.13</Total-Cost>
<Plan-Rows>1462</Plan-Rows>
<Plan-Width>52</Plan-Width>
<Actual-Startup-Time>451465.428</Actual-Startup-Time>
<Actual-Total-Time>580081.413</Actual-Total-Time>
<Actual-Rows>184162324</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Output>
<Item>tijd.tijdkey</Item>
<Item>tijd.einddatum</Item>
<Item>eenheid_pe.id_s</Item>
<Item>prijscomponent_pe.bedrag</Item>
<Item>l_adres_eenheid_pe.id_l</Item>
<Item>l_adres_eenheid_pe.id_h_adres</Item>
</Output>
<Inner-Unique>false</Inner-Unique>
<Hash-Cond>(eenheid_pe.id_h_eenheid =
l_adres_eenheid_pe.id_h_eenheid)</Hash-Cond>
<Shared-Hit-Blocks>2432</Shared-Hit-Blocks>
<Shared-Read-Blocks>321074</Shared-Read-Blocks>
<Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks>
<Shared-Written-Blocks>0</Shared-Written-Blocks>
<Local-Hit-Blocks>0</Local-Hit-Blocks>
<Local-Read-Blocks>0</Local-Read-Blocks>
<Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>
<Local-Written-Blocks>0</Local-Written-Blocks>
<Temp-Read-Blocks>11538197</Temp-Read-Blocks>
<Temp-Written-Blocks>11538197</Temp-Written-Blocks>
<Plans>
<Plan>
<Node-Type>Hash Join</Node-Type>
<Parent-Relationship>Outer</Parent-Relationship>
<Parallel-Aware>false</Parallel-Aware>
<Join-Type>Inner</Join-Type>
<Startup-Cost>1357349.34</Startup-Cost>
<Total-Cost>1518998.35</Total-Cost>
<Plan-Rows>1462</Plan-Rows>
<Plan-Width>48</Plan-Width>

<Actual-Startup-Time>451449.831</Actual-Startup-Time>
<Actual-Total-Time>557115.926</Actual-Total-Time>
<Actual-Rows>184162324</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Output>
<Item>tijd.tijdkey</Item>
<Item>tijd.einddatum</Item>
<Item>eenheid_pe.id_s</Item>
<Item>eenheid_pe.id_h_eenheid</Item>
<Item>prijscomponent_pe.bedrag</Item>
</Output>
<Inner-Unique>false</Inner-Unique>
<Hash-Cond>(prijscomponent_pe.id_h_prijscomponent
= l_prijscomponent_eenheid_pe.id_h_prijscomponent)</Hash-Cond>
<Join-Filter>((prijscomponent_pe.dv_start_dts
&lt;= tijd.einddatum) AND (prijscomponent_pe.dv_end_dts &gt;
tijd.einddatum))</Join-Filter>

<Rows-Removed-by-Join-Filter>0</Rows-Removed-by-Join-Filter>
<Shared-Hit-Blocks>2430</Shared-Hit-Blocks>
<Shared-Read-Blocks>320669</Shared-Read-Blocks>
<Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks>
<Shared-Written-Blocks>0</Shared-Written-Blocks>
<Local-Hit-Blocks>0</Local-Hit-Blocks>
<Local-Read-Blocks>0</Local-Read-Blocks>
<Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>
<Local-Written-Blocks>0</Local-Written-Blocks>
<Temp-Read-Blocks>11538197</Temp-Read-Blocks>

<Temp-Written-Blocks>11538197</Temp-Written-Blocks>
<Plans>
<Plan>
<Node-Type>Seq Scan</Node-Type>

<Parent-Relationship>Outer</Parent-Relationship>
<Parallel-Aware>false</Parallel-Aware>

<Relation-Name>s_h_prijscomponent_ssm</Relation-Name>
<Schema>datavault</Schema>
<Alias>prijscomponent_pe</Alias>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>156580.02</Total-Cost>
<Plan-Rows>1299083</Plan-Rows>
<Plan-Width>24</Plan-Width>

<Actual-Startup-Time>1375.832</Actual-Startup-Time>

<Actual-Total-Time>1876.664</Actual-Total-Time>
<Actual-Rows>1235341</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Output>
<Item>prijscomponent_pe.id_s</Item>
<Item>prijscomponent_pe.dv_end_dts</Item>
<Item>prijscomponent_pe.dv_load_dts</Item>
<Item>prijscomponent_pe.dv_start_dts</Item>

<Item>prijscomponent_pe.id_h_prijscomponent</Item>
<Item>prijscomponent_pe.auditinfo</Item>
<Item>prijscomponent_pe.bedrag</Item>
<Item>prijscomponent_pe.begindatum</Item>
<Item>prijscomponent_pe.bronsysteem</Item>
<Item>prijscomponent_pe.btw</Item>
<Item>prijscomponent_pe.businesskey</Item>
<Item>prijscomponent_pe.code</Item>
<Item>prijscomponent_pe.detailsoort</Item>
<Item>prijscomponent_pe.einddatum</Item>

<Item>prijscomponent_pe.extraelementen</Item>
<Item>prijscomponent_pe.identificatie</Item>
<Item>prijscomponent_pe.organisatie</Item>

<Item>prijscomponent_pe.sat_attributes_concat</Item>
<Item>prijscomponent_pe.soort</Item>

<Item>prijscomponent_pe.wijzigingreden</Item>
</Output>
<Filter>(prijscomponent_pe.soort =
'NET'::text)</Filter>

<Rows-Removed-by-Filter>3430513</Rows-Removed-by-Filter>
<Shared-Hit-Blocks>168</Shared-Hit-Blocks>
<Shared-Read-Blocks>97965</Shared-Read-Blocks>

<Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks>

<Shared-Written-Blocks>0</Shared-Written-Blocks>
<Local-Hit-Blocks>0</Local-Hit-Blocks>
<Local-Read-Blocks>0</Local-Read-Blocks>
<Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>
<Local-Written-Blocks>0</Local-Written-Blocks>
<Temp-Read-Blocks>0</Temp-Read-Blocks>
<Temp-Written-Blocks>0</Temp-Written-Blocks>
</Plan>
<Plan>
<Node-Type>Hash</Node-Type>

<Parent-Relationship>Inner</Parent-Relationship>
<Parallel-Aware>false</Parallel-Aware>
<Startup-Cost>1356757.17</Startup-Cost>
<Total-Cost>1356757.17</Total-Cost>
<Plan-Rows>47373</Plan-Rows>
<Plan-Width>48</Plan-Width>

<Actual-Startup-Time>450073.694</Actual-Startup-Time>

<Actual-Total-Time>450073.694</Actual-Total-Time>
<Actual-Rows>731320569</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Output>
<Item>tijd.tijdkey</Item>
<Item>tijd.einddatum</Item>
<Item>eenheid_pe.id_s</Item>
<Item>eenheid_pe.id_h_eenheid</Item>

<Item>l_prijscomponent_eenheid_pe.id_h_prijscomponent</Item>
</Output>
<Hash-Buckets>33554432</Hash-Buckets>

<Original-Hash-Buckets>65536</Original-Hash-Buckets>
<Hash-Batches>32</Hash-Batches>

<Original-Hash-Batches>1</Original-Hash-Batches>
<Peak-Memory-Usage>1835009</Peak-Memory-Usage>
<Shared-Hit-Blocks>2262</Shared-Hit-Blocks>

<Shared-Read-Blocks>222704</Shared-Read-Blocks>

<Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks>

<Shared-Written-Blocks>0</Shared-Written-Blocks>
<Local-Hit-Blocks>0</Local-Hit-Blocks>
<Local-Read-Blocks>0</Local-Read-Blocks>
<Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>
<Local-Written-Blocks>0</Local-Written-Blocks>
<Temp-Read-Blocks>5965116</Temp-Read-Blocks>

<Temp-Written-Blocks>9770745</Temp-Written-Blocks>
<Plans>
<Plan>
<Node-Type>Hash Join</Node-Type>

<Parent-Relationship>Outer</Parent-Relationship>
<Parallel-Aware>false</Parallel-Aware>
<Join-Type>Inner</Join-Type>
<Startup-Cost>1247323.72</Startup-Cost>
<Total-Cost>1356757.17</Total-Cost>
<Plan-Rows>47373</Plan-Rows>
<Plan-Width>48</Plan-Width>

<Actual-Startup-Time>171240.780</Actual-Startup-Time>

<Actual-Total-Time>336721.515</Actual-Total-Time>
<Actual-Rows>731320569</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Output>
<Item>tijd.tijdkey</Item>
<Item>tijd.einddatum</Item>
<Item>eenheid_pe.id_s</Item>
<Item>eenheid_pe.id_h_eenheid</Item>

<Item>l_prijscomponent_eenheid_pe.id_h_prijscomponent</Item>
</Output>
<Inner-Unique>false</Inner-Unique>

<Hash-Cond>(s_l_prijscomponent_eenheid_ssm_pe.id_l =
l_prijscomponent_eenheid_pe.id_l)</Hash-Cond>

<Join-Filter>((s_l_prijscomponent_eenheid_ssm_pe.dv_start_dts &lt;=
tijd.einddatum) AND (s_l_prijscomponent_eenheid_ssm_pe.dv_end_dts &gt;
tijd.einddatum))</Join-Filter>

<Rows-Removed-by-Join-Filter>0</Rows-Removed-by-Join-Filter>

<Shared-Hit-Blocks>2262</Shared-Hit-Blocks>

<Shared-Read-Blocks>222704</Shared-Read-Blocks>

<Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks>

<Shared-Written-Blocks>0</Shared-Written-Blocks>
<Local-Hit-Blocks>0</Local-Hit-Blocks>
<Local-Read-Blocks>0</Local-Read-Blocks>

<Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>

<Local-Written-Blocks>0</Local-Written-Blocks>

<Temp-Read-Blocks>5965116</Temp-Read-Blocks>

<Temp-Written-Blocks>5965116</Temp-Written-Blocks>
<Plans>
<Plan>
<Node-Type>Seq Scan</Node-Type>

<Parent-Relationship>Outer</Parent-Relationship>
<Parallel-Aware>false</Parallel-Aware>

<Relation-Name>s_l_prijscomponent_eenheid_ssm</Relation-Name>
<Schema>datavault</Schema>

<Alias>s_l_prijscomponent_eenheid_ssm_pe</Alias>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>85541.23</Total-Cost>
<Plan-Rows>4665823</Plan-Rows>
<Plan-Width>24</Plan-Width>

<Actual-Startup-Time>0.026</Actual-Startup-Time>

<Actual-Total-Time>308.939</Actual-Total-Time>
<Actual-Rows>4665854</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Output>

<Item>s_l_prijscomponent_eenheid_ssm_pe.id_s</Item>

<Item>s_l_prijscomponent_eenheid_ssm_pe.dv_end_dts</Item>

<Item>s_l_prijscomponent_eenheid_ssm_pe.dv_load_dts</Item>

<Item>s_l_prijscomponent_eenheid_ssm_pe.dv_start_dts</Item>

<Item>s_l_prijscomponent_eenheid_ssm_pe.id_l</Item>

<Item>s_l_prijscomponent_eenheid_ssm_pe.organisatie</Item>
</Output>

<Shared-Hit-Blocks>32</Shared-Hit-Blocks>

<Shared-Read-Blocks>38851</Shared-Read-Blocks>

<Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks>

<Shared-Written-Blocks>0</Shared-Written-Blocks>
<Local-Hit-Blocks>0</Local-Hit-Blocks>

<Local-Read-Blocks>0</Local-Read-Blocks>

<Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>

<Local-Written-Blocks>0</Local-Written-Blocks>
<Temp-Read-Blocks>0</Temp-Read-Blocks>

<Temp-Written-Blocks>0</Temp-Written-Blocks>
</Plan>
<Plan>
<Node-Type>Hash</Node-Type>

<Parent-Relationship>Inner</Parent-Relationship>
<Parallel-Aware>false</Parallel-Aware>

<Startup-Cost>1241994.21</Startup-Cost>
<Total-Cost>1241994.21</Total-Cost>
<Plan-Rows>426361</Plan-Rows>
<Plan-Width>52</Plan-Width>

<Actual-Startup-Time>171239.530</Actual-Startup-Time>