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