Re: ERROR: too many dynamic shared memory segments - Mailing list pgsql-general

On Tue, Nov 28, 2017 at 1:13 AM, Jakub Glapa <jakub.glapa@gmail.com> wrote:
> The queries are somehow special.
> We are still using the old style partitioning (list type) but we abuse it a
> bit when querying.
> When querying a set of partitions instead of doing it via parent table we
> stitch together the required tables with UNION ALL (this requires less
> locking) and was more performant in our benchmark (the new native
> partitioning might improve this but we didn't research that yet).
>
> The queries are in form of
> SELECT col1,col2,col3 FROM
> (SELECT *
>       FROM par1
>       WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend <=
> '2017-11-26 23:59:59.999'
>       UNION ALL SELECT *
>                 FROM par2
>                 WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend <=
> '2017-11-26 23:59:59.999'
>       UNION ALL SELECT *
>                 FROM  par2
>                 WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend <=
> '2017-11-26 23:59:59.999'
>
>       WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend <=
> '2017-11-26 23:59:59.999'
>
> UNION ALL
> ...
> ) unionalias
> WHERE ....
>
>
>
> and indeed the query planner shows the usage of Bitmap Heap Scan
> ....
>                                  ->  Subquery Scan on "*SELECT* 2"
> (cost=3068.58..19793.94 rows=1 width=1284)
>                                        ->  Gather  (cost=3068.58..19793.93
> rows=1 width=5014)
>                                              Workers Planned: 2
>                                              ->  Parallel Bitmap Heap Scan
> on par_6  (cost=2068.58..18793.83 rows=1 width=5014)
>                                                    Recheck Cond:
> <<CONDITION>>
>                                                    Filter: <<CONDITION>>
>                                                    ->  BitmapAnd
> (cost=2068.58..2068.58 rows=30955 width=0)
>                                                          ->  BitmapOr
> (cost=999.30..999.30 rows=42989 width=0)
>                                                                ->  Bitmap
> Index Scan on par_6_datasource  (cost=0.00..990.21 rows=42922 width=0)
>                                                                      Index
> Cond: ((datasource)::text = 'one'::text)
>                                                                ->  Bitmap
> Index Scan on par_6_datasource  (cost=0.00..4.30 rows=1 width=0)
>                                                                      Index
> Cond: ((datasource)::text = 'two'::text)
>                                                                ->  Bitmap
> Index Scan on par_6_datasource  (cost=0.00..4.79 rows=67 width=0)
>                                                                      Index
> Cond: ((datasource)::text = 'three'::text)
>                                                          ->  Bitmap Index
> Scan on par_6_rangestart  (cost=0.00..1069.02 rows=47564 width=0)
>                                                                Index Cond:
> (rangestart >= '2017-01-01 00:00:00+01'::timestamp with time zone)
>                                  ->  Subquery Scan on "*SELECT* 3"
> (cost=761.33..7944.99 rows=1 width=1086)
>                                        ->  Bitmap Heap Scan on par_7
> (cost=761.33..7944.98 rows=1 width=4816)
>                                                    Recheck Cond:
> <<CONDITION>>
>                                                    Filter: <<CONDITION>>
>                                              ->  BitmapAnd
> (cost=761.33..761.33 rows=7045 width=0)
>                                                    ->  Bitmap Index Scan on
> par_7_rangestart  (cost=0.00..380.35 rows=14942 width=0)
>                                                          Index Cond:
> (rangestart >= '2017-01-01 00:00:00+01'::timestamp with time zone)
>                                                    ->  BitmapOr
> (cost=380.72..380.72 rows=12248 width=0)
>                                                          ->  Bitmap Index
> Scan on par_7_datasource  (cost=0.00..372.00 rows=12228 width=0)
>                                                                Index Cond:
> ((datasource)::text = 'one'::text)
>                                                          ->  Bitmap Index
> Scan on par_7_datasource  (cost=0.00..4.36 rows=10 width=0)
>                                                                Index Cond:
> ((datasource)::text = 'two'::text)
>                                                          ->  Bitmap Index
> Scan on par_7_datasource  (cost=0.00..4.36 rows=10 width=0)
>                                                                Index Cond:
> ((datasource)::text = 'three'::text)
>
> ....
>
>
> In this particular query there were over _100_ partitions connected with the
> UNION ALL operator.

Ah, so you have many Gather nodes under Append?  That's one way to eat
arbitrarily many DSM slots.  We allow for 64 + 2 * max_backends.  Does
it help if you increase max_connections?  I am concerned about the
crash failure mode you mentioned in the first email though: we should
always be able to handle that condition gracefully.

-- 
Thomas Munro
http://www.enterprisedb.com