Re: dsa_allocate() faliure - Mailing list pgsql-performance

Hi, I'm also experiencing the problem: dsa_allocate could not find 7 free pages CONTEXT: parallel worker

I'm running: PostgreSQL 10.5 (Ubuntu 10.5-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit

query plan: (select statement over parent table to many partitions):

select ... 

from fa 

where c_id in (<ID_LIST>) and 

datetime >= '2018/01/01' 

and ((dims ? 'p' and dims ? 'mcp') 

or (datasource in (FA', 'GA'))) 

and not datasource = 'm' 

GROUP BY datasource, dims ->'ct', dims ->'mcp', dims -> 'p', dims -> 'sp':

Finalize GroupAggregate  (cost=31514757.77..31519357.77 rows=40000 width=223)
   Group Key: fa.datasource, ((fa.dims -> 'ct'::text)), ((fa.dims -> 'mcp'::text)), ((fa.dims -> 'p'::text)), ((fa.dims -> 'sp'::text))
   ->  Sort  (cost=31514757.77..31515057.77 rows=120000 width=223)
         Sort Key: fa.datasource, ((fa.dims -> 'ct'::text)), ((fa.dims -> 'mcp'::text)), ((fa.dims -> 'p'::text)), ((fa.dims -> 'sp'::text))
         ->  Gather  (cost=31491634.17..31504634.17 rows=120000 width=223)
               Workers Planned: 3
               ->  Partial HashAggregate  (cost=31490634.17..31491634.17 rows=40000 width=223)
                     Group Key: fa.datasource, (fa.dims -> 'ct'::text), (fa.dims -> 'mcp'::text), (fa.dims -> 'p'::text), (fa.dims -> 'sp'::text)
                     ->  Result  (cost=0.00..31364713.39 rows=5596479 width=175)
                           ->  Append  (cost=0.00..31252783.81 rows=5596479 width=659)
                                 ->  Parallel Seq Scan on fa  (cost=0.00..0.00 rows=1 width=580)
                                       Filter: ((datetime >= '2018-01-01 00:00:00+01'::timestamp with time zone) AND ((datasource)::text <> 'M'::text) AND (((dims ? 'p'::text) AND (dims ? 'mcp'::text)) OR ((datasource)::text = 
ANY ('{"FA","GA"}'::text[]))) AND (c_id = ANY ('{<ID_LIST>}'::bigint[])))
                                 ->  Parallel Bitmap Heap Scan on fa_10  (cost=1226.36..53641.49 rows=1 width=1290)
                                       Recheck Cond: (datetime >= '2018-01-01 00:00:00+01'::timestamp with time zone)
                                       Filter: (((datasource)::text <> 'M'::text) AND (((dims ? 'p'::text) AND (dims ? 'mcp'::text)) OR ((datasource)::text = ANY ('<ID_LIST>'::bigint[])))
                                       ->  Bitmap Index Scan on fa_10_rangestart  (cost=0.00..1226.36 rows=32259 width=0)
                                             Index Cond: (datetime >= '2018-01-01 00:00:00+01'::timestamp with time zone)
                                 ->  Parallel Seq Scan on fa_105  (cost=0.00..11.99 rows=1 width=580)
                                       Filter: ((datetime >= '2018-01-01 00:00:00+01'::timestamp with time zone) AND ((datasource)::text <> 'M'::text) AND (((dims ? 'p'::text) AND (dims ? 'mcp'::text)) OR ((datasource)::text = 
ANY ('{"FA","GA"}'::text[]))) AND (c_id = ANY ('<ID_LIST>'::bigint[])))
                                 ->  Parallel Seq Scan on fa_106  (cost=0.00..11.99 rows=1 width=580)
                                       Filter: ((datetime >= '2018-01-01 00:00:00+01'::timestamp with time zone) AND ((datasource)::text <> 'M'::text) AND (((dims ? 'p'::text) AND (dims ? 'mcp'::text)) OR ((datasource)::text = 
ANY ('{"FA","GA"}'::text[]))) AND (c_id = ANY ('<ID_LIST>..........

--
regards,
Jakub Glapa