Re: Reference to - BUG #18349: ERROR: invalid DSA memory alloc request size 1811939328, CONTEXT: parallel worker

>
>
> On Oct 1, 2024 Andrei Lepikhov wrote
>
> Can you provide an explain of this query?

Apologies for the delay. I have been travelling since Wednesday night.
Thanks for your help and time with this issue.

Below is the query, with specific values redacted. An explain with
max_parallel_workers_per_gather = 2 and explain analyze
max_parallel_workers_per_gather = 0.

In this case, the number of rows from the users table based on account_id
is in the 99th percentile for this table and it is a long and sparse right
tail.

This is using V17.0 stock source code and stock configuration on linux.

The query

SELECT
    CF.NUMERIC_VALUE AS CF_COL,
    U.USERS_ID,
    U.OBJECT_ID,
    U.ACCOUNT_ID,
    U.EXTERNAL_ID,
    U.FIRST_NAME,
    U.MIDDLE_NAME,
    U.LAST_NAME,
    U.DISABLED,
    U.DEACTIVATED AS SUSPEND_DATE,
    U.CREATED,
    U.UPDATED,
    U.IS_BLE_TWO_FACTOR_EXEMPT,
    U.HAS_THUMBNAIL,
    U.USER_TYPE_ID,
    UI.USER_IMAGE_ID,
    UI.CONTENT_TYPE AS USER_IMAGE_CONTENT_TYPE,
    COUNT(*) OVER () AS TOTAL_USERS_COUNT,
    STRING_AGG(SG.object_ID::CHARACTER VARYING, ';') AS GROUPS,
    STRING_AGG(SG.NAME
<https://urldefense.proofpoint.com/v2/url?u=http-3A__SG.NAME&d=DwMGaQ&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=JZHDXmxC6C_GpXil_p_qZyChJLKMKUlbW9OutJroJT4&m=d1w9W1jfdQcXFikedJO9jjD5rMsB8hKCE9Ldj4R6QV_WdAoes0xhjdMdU0outkA7&s=ZMpYMOndqorTz75E_JF2rCvBKp40__QNQlw2rXVcw-k&e=>,
' ') AS GROUPNAMES
FROM
    USERS U
    LEFT JOIN USER_IMAGE UI ON U.USER_IMAGE_ID = UI.USER_IMAGE_ID
    LEFT JOIN SECURITY_GROUP_MEMBER SGM ON SGM.OBJECT_ID = U.OBJECT_ID
        AND SGM.OBJECT_ID = U.OBJECT_ID
    LEFT JOIN SECURITY_GROUP SG
        ON SGM.SECURITY_GROUP_ID = SG.SECURITY_GROUP_ID
        AND SG.DISABLED = 0
        AND SG.ACCOUNT_ID = U.ACCOUNT_ID
        AND SG.SECURITY_GROUP_TYPE_ID = 2
    LEFT JOIN CUSTOM_FIELD_VALUE CF
        ON U.USERS_ID = CF.USER_ID
        AND CF.CUSTOM_FIELD_ID = <craig redacted>
WHERE
    U.ACCOUNT_ID = <craig redacted>
    AND U.USER_TYPE_ID = 1
    AND U.DISABLED = 0
GROUP BY
    U.USERS_ID,
    UI.USER_IMAGE_ID,
    CF.NUMERIC_VALUE
ORDER BY
    U.LAST_NAME ASC,
    U.FIRST_NAME ASC,
    U.USERS_ID ASC
LIMIT
    20
OFFSET
    0;


Explain with stock configuration which is set
max_parallel_workers_per_gather = 2;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2529139.77..2529139.82 rows=20 width=187)
   ->  Sort  (cost=2529139.77..2530484.84 rows=538028 width=187)
         Sort Key: u.last_name, u.first_name, u.users_id
         ->  WindowAgg  (cost=2514822.88..2514823.03 rows=538028 width=187)
               ->  Finalize GroupAggregate  (cost=2432583.40..2508097.68
rows=538028 width=179)
                     Group Key: u.users_id, ui.user_image_id,
cf.numeric_value
                     ->  Gather Merge  (cost=2432583.40..2492181.03
rows=448356 width=179)
                           Workers Planned: 2
                           ->  Partial GroupAggregate
 (cost=2431583.37..2439429.60 rows=224178 width=179)
                                 Group Key: u.users_id, ui.user_image_id,
cf.numeric_value
                                 ->  Sort  (cost=2431583.37..2432143.82
rows=224178 width=140)
                                       Sort Key: u.users_id,
ui.user_image_id, cf.numeric_value
                                       ->  Parallel Hash Left Join
 (cost=1384936.37..2395567.35 rows=224178 width=140)
                                             Hash Cond: (u.users_id =
cf.user_id)
                                             ->  Hash Left Join
 (cost=1124308.04..2134350.56 rows=224178 width=134)
                                                   Hash Cond:
(sgm.security_group_id = sg.security_group_id)
                                                   ->  Nested Loop Left
Join  (cost=1119678.30..2129132.34 rows=224178 width=117)
                                                         ->  Parallel Hash
Right Join  (cost=1119677.73..1326436.98 rows=224178 width=109)
                                                               Hash Cond:
(ui.user_image_id = u.user_image_id)
                                                               ->  Parallel
Seq Scan on user_image ui  (cost=0.00..130846.12 rows=3533412 width=18)
                                                               ->  Parallel
Hash  (cost=1113372.50..1113372.50 rows=224178 width=99)
                                                                     ->
 Parallel Bitmap Heap Scan on users u  (cost=8824.42..1113372.50
rows=224178 width=99)

 Recheck Cond: ((account_id = <craig redacted>) AND (disabled = 0) AND
(user_type_id = 1))

 ->  Bitmap Index Scan on u_act_dis_type  (cost=0.00..8689.92 rows=538028
width=0)

     Index Cond: ((account_id = <craig redacted>) AND (disabled = 0) AND
(user_type_id = 1))
                                                         ->  Index Only
Scan using security_group_obid_sgid_idx on security_group_member sgm
 (cost=0.57..3.57 rows=1 width=16)
                                                               Index Cond:
((object_id = u.object_id) AND (object_id = u.object_id))
                                                   ->  Hash
 (cost=4622.16..4622.16 rows=607 width=41)
                                                         ->  Index Scan
using account_security_group_fk_ind on security_group sg
 (cost=0.43..4622.16 rows=607 width=41)
                                                               Index Cond:
(account_id = <craig redacted>)
                                                               Filter:
((disabled = 0) AND (security_group_type_id = 2))
                                             ->  Parallel Hash
 (cost=259796.42..259796.42 rows=66553 width=14)
                                                   ->  Parallel Index Scan
using date_value_idx on custom_field_value cf  (cost=0.56..259796.42
rows=66553 width=14)
                                                         Index Cond:
(custom_field_id = <craig redacted>)
(34 rows)


explain (analyze) with set max_parallel_workers_per_gather = 0;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=4152596.58..4152596.63 rows=20 width=187) (actual
time=10192.249..10192.258 rows=20 loops=1)
   ->  Sort  (cost=4152596.58..4153941.65 rows=538028 width=187) (actual
time=10192.248..10192.255 rows=20 loops=1)
         Sort Key: u.last_name, u.first_name, u.users_id
         Sort Method: top-N heapsort  Memory: 32kB
         ->  WindowAgg  (cost=4138279.81..4138279.85 rows=538028 width=187)
(actual time=9748.632..9958.924 rows=904292 loops=1)
               ->  GroupAggregate  (cost=4112723.52..4131554.50 rows=538028
width=179) (actual time=8482.695..9389.560 rows=904292 loops=1)
                     Group Key: u.users_id, ui.user_image_id,
cf.numeric_value
                     ->  Sort  (cost=4112723.52..4114068.59 rows=538028
width=140) (actual time=8482.679..8655.695 rows=1720872 loops=1)
                           Sort Key: u.users_id, ui.user_image_id,
cf.numeric_value
                           Sort Method: external merge  Disk: 199104kB
                           ->  Hash Left Join  (cost=602312.67..3984272.46
rows=538028 width=140) (actual time=1955.881..7537.783 rows=1720872 loops=1)
                                 Hash Cond: (u.users_id = cf.user_id)
                                 ->  Hash Left Join
 (cost=340636.13..3721183.60 rows=538028 width=134) (actual
time=1806.879..6920.376 rows=1720872 loops=1)
                                       Hash Cond: (sgm.security_group_id =
sg.security_group_id)
                                       ->  Nested Loop Left Join
 (cost=336006.39..3715141.53 rows=538028 width=117) (actual
time=1804.650..6599.170 rows=1720872 loops=1)
                                             ->  Hash Left Join
 (cost=336005.82..1788669.80 rows=538028 width=109) (actual
time=1804.623..3537.213 rows=904292 loops=1)
                                                   Hash Cond:
(u.user_image_id = ui.user_image_id)
                                                   ->  Index Scan using
u_act_dis_type on users u  (cost=0.56..1384749.23 rows=538028 width=99)
(actual time=0.033..1133.900 rows=904292 loops=1)
                                                         Index Cond:
((account_id =  <craig redacted>) AND (disabled = 0) AND (user_type_id = 1))
                                                   ->  Hash
 (cost=180313.89..180313.89 rows=8480189 width=18) (actual
time=1804.516..1804.517 rows=8488571 loops=1)
                                                         Buckets: 131072
 Batches: 128  Memory Usage: 3986kB
                                                         ->  Seq Scan on
user_image ui  (cost=0.00..180313.89 rows=8480189 width=18) (actual
time=0.011..753.277 rows=8488571 loops=1)
                                             ->  Index Only Scan using
security_group_obid_sgid_idx on security_group_member sgm  (cost=0.57..3.57
rows=1 width=16) (actual time=0.003..0.003 rows=2 loops=904292)
                                                   Index Cond: ((object_id
= u.object_id) AND (object_id = u.object_id))
                                                   Heap Fetches: 0
                                       ->  Hash  (cost=4622.16..4622.16
rows=607 width=41) (actual time=2.219..2.220 rows=795 loops=1)
                                             Buckets: 1024  Batches: 1
 Memory Usage: 78kB
                                             ->  Index Scan using
account_security_group_fk_ind on security_group sg  (cost=0.43..4622.16
rows=607 width=41) (actual time=0.937..2.121 rows=795 loops=1)
                                                   Index Cond: (account_id
= <craig redacted>)
                                                   Filter: ((disabled = 0)
AND (security_group_type_id = 2))
                                                   Rows Removed by Filter:
764
                                 ->  Hash  (cost=260262.29..260262.29
rows=113140 width=14) (actual time=148.930..148.931 rows=125986 loops=1)
                                       Buckets: 131072  Batches: 1  Memory
Usage: 6931kB
                                       ->  Index Scan using date_value_idx
on custom_field_value cf  (cost=0.56..260262.29 rows=113140 width=14)
(actual time=0.021..132.508 rows=125986 loops=1)
                                             Index Cond: (custom_field_id
=  <craig redacted>)
 Planning Time: 0.983 ms
 Execution Time: 10233.621 ms
(37 rows)

I asked someone else to try to build artificial data for this query. Maybe
they will have a different take and be successful compared to me.

Thanks
Craig