Re: Reference to - BUG #18349: ERROR: invalid DSA memory alloc request size 1811939328, CONTEXT: parallel worker - Mailing list pgsql-bugs
| From | Craig Milhiser |
|---|---|
| Subject | Re: Reference to - BUG #18349: ERROR: invalid DSA memory alloc request size 1811939328, CONTEXT: parallel worker |
| Date | |
| Msg-id | CA+wnhO0VdTP9OVdZP9bmkavma2WkE-pYpP1yW8yDwRrJ97j-2g@mail.gmail.com Whole thread Raw |
| In response to | Re: Reference to - BUG #18349: ERROR: invalid DSA memory alloc request size 1811939328, CONTEXT: parallel worker (Andrei Lepikhov <lepihov@gmail.com>) |
| Responses |
Re: Reference to - BUG #18349: ERROR: invalid DSA memory alloc request size 1811939328, CONTEXT: parallel worker
|
| List | pgsql-bugs |
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, ' ') 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;
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, ' ') 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)
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)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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)
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
pgsql-bugs by date: