amazon-redshift - 在 Redshift 上运行具有多个 CTE 的查询时可能出现内存错误
问题描述
我正在运行一个包含多个 CTE 的查询，并引发以下错误：
ERROR: Assert
DETAIL:
-----------------------------------------------
error: Assert
code: 1000
context: query->a_last_plan()->m_locus == LocusXNode -
query: 105350
location: <----->
process: padbmaster [pid=18065]
-----------------------------------------------
我的研究表明，这对应于内存错误，但也可能是其他问题。因此，我调整了运行此查询的集群的大小，现在使用的磁盘空间低于 2.5%，CPU 利用率约为 60%。但我仍然继续收到此错误。
有人对如何解决这个问题有任何想法吗？
这是查询的部分EXPLAIN输出：
-> XN Network (cost=2217335550055.32..2217335550055.33 rows=5 width=68)
Send to leader
-> XN Sort (cost=2217335550055.32..2217335550055.33 rows=5 width=68)
Sort Key: event_no
-> XN Subquery Scan derived_table1 (cost=1217335550055.02..1217335550055.26 rows=5 width=68)
-> XN Window (cost=1217335550055.02..1217335550055.13 rows=5 width=52)
Order: event_no
-> XN Sort (cost=1217335550055.02..1217335550055.03 rows=5 width=52)
Sort Key: event_no
-> XN Network (cost=334239012.02..217335550054.96 rows=5 width=52)
Send to slice 0
-> XN Subquery Scan events_agg_ring_users (cost=334239012.02..217335550054.96 rows=5 width=52)
-> XN Unique (cost=334239012.02..217335550054.91 rows=5 width=36)
-> XN Append (cost=334239012.02..217335550054.86 rows=5 width=36)
-> XN Network (cost=334239012.02..334239012.04 rows=1 width=16)
Distribute Round Robin
-> XN Subquery Scan "*SELECT* 1" (cost=334239012.02..334239012.04 rows=1 width=16)
-> XN Subquery Scan email_data_ring_users (cost=334239012.02..334239012.03 rows=1 width=16)
-> XN Aggregate (cost=334239012.02..334239012.02 rows=1 width=21)
-> XN Hash Join DS_DIST_ALL_NONE (cost=205997849.04..334239012.00 rows=1 width=21)
Hash Cond: ("outer".mailer_method_enum = "inner".mailer_method_enum)
-> XN Hash IN Join DS_DIST_NONE (cost=205997840.80..334239002.92 rows=66 width=25)
Hash Cond: ("outer".user_id = "inner".user_id)
-> XN Seq Scan on email_funnel_2021 e (cost=0.00..123532147.20 rows=376721141 width=25)
Filter: ((sent_at <= '2021-04-23 00:00:00'::timestamp without time zone) AND (sent_at >= '2021-04-06 00:00:00'::timestamp without time zone))
-> XN Hash (cost=205997840.74..205997840.74 rows=24 width=4)
-> XN Subquery Scan users_in_ring (cost=205997620.44..205997840.74 rows=24 width=4)
-> XN Unique (cost=205997620.44..205997840.50 rows=24 width=4)
InitPlan
-> XN Subquery Scan ring_start_date (cost=102998810.21..102998810.23 rows=1 width=8)
InitPlan
-> XN Subquery Scan ring_end_date (cost=102998810.19..102998810.21 rows=1 width=8)
InitPlan
-> XN Aggregate (cost=102998810.19..102998810.19 rows=1 width=8)
InitPlan
-> XN Seq Scan on mailer_methods (cost=0.00..8.24 rows=1 width=4)
Filter: ((mailer_method)::text = 'claim_papers_blast'::text)
-> XN Seq Scan on email_funnel_2021 (cost=0.00..102943456.00 rows=22138378 width=8)
Filter: (mailer_method_enum = $67)
-> XN Result (cost=0.00..0.01 rows=1 width=0)
-> XN Result (cost=0.00..0.01 rows=1 width=0)
-> XN Subquery Scan ring_end_date (cost=102998810.19..102998810.21 rows=1 width=8)
InitPlan
-> XN Aggregate (cost=102998810.19..102998810.19 rows=1 width=8)
InitPlan
-> XN Seq Scan on mailer_methods (cost=0.00..8.24 rows=1 width=4)
Filter: ((mailer_method)::text = 'claim_papers_blast'::text)
-> XN Seq Scan on email_funnel_2021 (cost=0.00..102943456.00 rows=22138378 width=8)
Filter: (mailer_method_enum = $71)
-> XN Result (cost=0.00..0.01 rows=1 width=0)
-> XN Seq Scan on premium_user_treatments (cost=0.00..220.00 rows=24 width=4)
Filter: ((created_at >= $70) AND (created_at <= $73) AND ((reason = 38) OR (reason = 43) OR (reason = 40) OR (reason = 49) OR (reason = 51)))
-> XN Hash (cost=8.24..8.24 rows=1 width=4)
-> XN Seq Scan on mailer_methods "mm" (cost=0.00..8.24 rows=1 width=4)
Filter: ((mailer_method)::text = 'claim_papers_blast'::text)
-> XN Network (cost=334239012.02..334239012.04 rows=1 width=16)
Distribute Round Robin
-> XN Subquery Scan "*SELECT* 2" (cost=334239012.02..334239012.04 rows=1 width=16)
-> XN Subquery Scan email_data_ring_users (cost=334239012.02..334239012.03 rows=1 width=16)
-> XN Aggregate (cost=334239012.02..334239012.02 rows=1 width=21)
-> XN Hash Join DS_DIST_ALL_NONE (cost=205997849.04..334239012.00 rows=1 width=21)
Hash Cond: ("outer".mailer_method_enum = "inner".mailer_method_enum)
-> XN Hash IN Join DS_DIST_NONE (cost=205997840.80..334239002.92 rows=66 width=25)
Hash Cond: ("outer".user_id = "inner".user_id)
-> XN Seq Scan on email_funnel_2021 e (cost=0.00..123532147.20 rows=376721141 width=25)
Filter: ((sent_at <= '2021-04-23 00:00:00'::timestamp without time zone) AND (sent_at >= '2021-04-06 00:00:00'::timestamp without time zone))
-> XN Hash (cost=205997840.74..205997840.74 rows=24 width=4)
-> XN Subquery Scan users_in_ring (cost=205997620.44..205997840.74 rows=24 width=4)
-> XN Unique (cost=205997620.44..205997840.50 rows=24 width=4)
InitPlan
-> XN Subquery Scan ring_start_date (cost=102998810.21..102998810.23 rows=1 width=8)
InitPlan
-> XN Subquery Scan ring_end_date (cost=102998810.19..102998810.21 rows=1 width=8)
InitPlan
-> XN Aggregate (cost=102998810.19..102998810.19 rows=1 width=8)
InitPlan
-> XN Seq Scan on mailer_methods (cost=0.00..8.24 rows=1 width=4)
Filter: ((mailer_method)::text = 'claim_papers_blast'::text)
-> XN Seq Scan on email_funnel_2021 (cost=0.00..102943456.00 rows=22138378 width=8)
Filter: (mailer_method_enum = $60)
-> XN Result (cost=0.00..0.01 rows=1 width=0)
-> XN Result (cost=0.00..0.01 rows=1 width=0)
-> XN Subquery Scan ring_end_date (cost=102998810.19..102998810.21 rows=1 width=8)
InitPlan
-> XN Aggregate (cost=102998810.19..102998810.19 rows=1 width=8)
InitPlan
-> XN Seq Scan on mailer_methods (cost=0.00..8.24 rows=1 width=4)
Filter: ((mailer_method)::text = 'claim_papers_blast'::text)
-> XN Seq Scan on email_funnel_2021 (cost=0.00..102943456.00 rows=22138378 width=8)
Filter: (mailer_method_enum = $64)
-> XN Result (cost=0.00..0.01 rows=1 width=0)
-> XN Seq Scan on premium_user_treatments (cost=0.00..220.00 rows=24 width=4)
Filter: ((created_at >= $63) AND (created_at <= $66) AND ((reason = 38) OR (reason = 43) OR (reason = 40) OR (reason = 49) OR (reason = 51)))
-> XN Hash (cost=8.24..8.24 rows=1 width=4)
-> XN Seq Scan on mailer_methods "mm" (cost=0.00..8.24 rows=1 width=4)
Filter: ((mailer_method)::text = 'claim_papers_blast'::text)
-> XN Network (cost=334239012.02..334239012.04 rows=1 width=16)
Distribute Round Robin
-> XN Subquery Scan "*SELECT* 3" (cost=334239012.02..334239012.04 rows=1 width=16)
-> XN Subquery Scan email_data_ring_users (cost=334239012.02..334239012.03 rows=1 width=16)
-> XN Aggregate (cost=334239012.02..334239012.02 rows=1 width=21)
-> XN Hash Join DS_DIST_ALL_NONE (cost=205997849.04..334239012.00 rows=1 width=21)
Hash Cond: ("outer".mailer_method_enum = "inner".mailer_method_enum)
-> XN Hash IN Join DS_DIST_NONE (cost=205997840.80..334239002.92 rows=66 width=25)
Hash Cond: ("outer".user_id = "inner".user_id)
-> XN Seq Scan on email_funnel_2021 e (cost=0.00..123532147.20 rows=376721141 width=25)
Filter: ((sent_at <= '2021-04-23 00:00:00'::timestamp without time zone) AND (sent_at >= '2021-04-06 00:00:00'::timestamp without time zone))
-> XN Hash (cost=205997840.74..205997840.74 rows=24 width=4)
-> XN Subquery Scan users_in_ring (cost=205997620.44..205997840.74 rows=24 width=4)
-> XN Unique (cost=205997620.44..205997840.50 rows=24 width=4)
InitPlan
-> XN Subquery Scan ring_start_date (cost=102998810.21..102998810.23 rows=1 width=8)
InitPlan
-> XN Subquery Scan ring_end_date (cost=102998810.19..102998810.21 rows=1 width=8)
InitPlan
-> XN Aggregate (cost=102998810.19..102998810.19 rows=1 width=8)
InitPlan
-> XN Seq Scan on mailer_methods (cost=0.00..8.24 rows=1 width=4)
Filter: ((mailer_method)::text = 'claim_papers_blast'::text)
-> XN Seq Scan on email_funnel_2021 (cost=0.00..102943456.00 rows=22138378 width=8)
Filter: (mailer_method_enum = $53)
-> XN Result (cost=0.00..0.01 rows=1 width=0)
-> XN Result (cost=0.00..0.01 rows=1 width=0)
-> XN Subquery Scan ring_end_date (cost=102998810.19..102998810.21 rows=1 width=8)
InitPlan
-> XN Aggregate (cost=102998810.19..102998810.19 rows=1 width=8)
InitPlan
-> XN Seq Scan on mailer_methods (cost=0.00..8.24 rows=1 width=4)
Filter: ((mailer_method)::text = 'claim_papers_blast'::text)
-> XN Seq Scan on email_funnel_2021 (cost=0.00..102943456.00 rows=22138378 width=8)
Filter: (mailer_method_enum = $57)
解决方案
推荐阅读
- ubuntu - Ansible：如何处理首次登录强制密码更改和推送 ssh-key
- python - 我在 Heroku 上托管了一个 Discord Bot，它只是给了我错误 14 ps webscale 也不起作用
- python - Python Django 保存帖子计算方法
- azure - 是否缺少此 Terraform 代码权限或 Azure 帐户（附加错误）以及如何解决？
- php - 如何在 PHP 中将数组转换为键值对
- react-native - Uber H3 geoToH3 在 React Native 中不起作用
- postgresql - 在 PostgreSQL 中获取复杂的交叉销售细节
- javascript - 我想要创建一些目录的指导
- python - Python中的音频文件不播放
- awk - 文件上的 awk 条件，同时保留第一行