我有一个查询,需要花费15分钟以上才能在Redshift中执行。该查询是使用AWS Lambda触发的,超时时间为15分钟。因此,我想检查是否有一种方法可以优化查询以使其快速给出结果。
这是我的SQL查询:
insert into
test.qa_locked
select
'1d8db587-f5ab-41f4-9c2b-c4e21e0c7481',
'ABC-013505',
'ABC-013505-2-2020',
user_id,
cast(TIMEOFDAY() as timestamp)
from
(
select
user_id
from
(
select
contact_id
from
test.qa_locked
)
where
contact_cnt <= 1
)
)
这是计划:
XN Subquery Scan "*SELECT*" (cost=1000028198481.69..1000028198481.75 rows=1 width=218)
-> XN Subquery Scan derived_table1 (cost=1000028198481.69..1000028198481.73 rows=1 width=210)
-> XN Window (cost=1000028198481.69..1000028198481.71 rows=1 width=56)
-> XN Sort (cost=1000028198481.69..1000028198481.70 rows=1 width=56)
-> XN Network (cost=1645148.05..28198481.68 rows=1 width=56)
-> XN Hash NOT IN Join DS_DIST_OUTER (cost=1645148.05..28198481.68 rows=1 width=56)
-> XN Hash NOT IN Join DS_DIST_INNER (cost=1645147.76..28091814.71 rows=1 width=56)
-> XN Hash NOT IN Join DS_DIST_INNER (cost=1645147.09..7491814.01 rows=1 width=56)
-> XN Hash NOT IN Join DS_DIST_INNER (cost=1645146.68..6805146.91 rows=1 width=56)
-> XN Hash NOT IN Join DS_DIST_INNER (cost=1645146.16..6438479.71 rows=1 width=56)
-> XN Hash NOT IN Join DS_DIST_NONE (cost=1645145.65..6071812.51 rows=1 width=56)
-> XN Hash NOT IN Join DS_DIST_NONE (cost=1645145.29..6071812.13 rows=1 width=56)
-> XN Hash NOT IN Join DS_DIST_BOTH (cost=1645144.96..6071811.77 rows=1 width=56)
-> XN Hash NOT IN Join DS_DIST_NONE (cost=1645144.50..5598477.96 rows=1 width=56)
-> XN Hash NOT IN Join DS_DIST_BOTH (cost=1645144.47..5598477.91 rows=1 width=84)
-> XN Hash NOT IN Join DS_DIST_OUTER (cost=1645142.59..5078476.00 rows=1 width=84)
-> XN Hash NOT IN Join DS_BCAST_INNER (cost=1645142.57..4065142.63 rows=1 width=600)
-> XN Hash Left Join DS_DIST_BOTH (cost=1201145.21..3221145.24 rows=1 width=1116)
-> XN Seq Scan on contacts xa (cost=1201145.21..1201145.21 rows=1 width=640)
-> XN Hash (cost=0.00..0.00 rows=1 width=556)
-> XN Seq Scan on accounts ya (cost=0.00..0.00 rows=1 width=556)
-> XN Hash (cost=443997.35..443997.35 rows=1 width=32)
-> XN Subquery Scan "IN_subquery" (cost=23989.76..443997.35 rows=1 width=32)
-> XN Unique (cost=23989.76..443997.34 rows=1 width=516)
-> XN Nested Loop DS_BCAST_INNER (cost=23989.76..443997.34 rows=1 width=516)
-> XN Seq Scan on accounts con (cost=0.00..0.00 rows=1 width=516)
-> XN Hash NOT IN Join DS_DIST_OUTER (cost=23989.76..83997.32 rows=1 width=26)
-> XN Seq Scan on campaign_exclusion_list cam (cost=0.00..7.53 rows=1 width=26)
-> XN Hash (cost=23989.75..23989.75 rows=1 width=32)
-> XN Subquery Scan "IN_subquery" (cost=0.00..23989.75 rows=1 width=32)
-> XN Unique (cost=0.00..23989.74 rows=1 width=18)
-> XN Seq Scan on campaign_inclusion_list (cost=0.00..23989.74 rows=1 width=18)
-> XN Hash (cost=0.01..0.01 rows=1 width=516)
-> XN Subquery Scan "IN_subquery" (cost=0.00..0.01 rows=1 width=516)
-> XN Unique (cost=0.00..0.00 rows=1 width=516)
-> XN Seq Scan on contacts (cost=0.00..0.00 rows=1 width=516)
-> XN Hash (cost=1.88..1.88 rows=1 width=210)
-> XN Seq Scan on bh_email_open_clicks (cost=0.00..1.88 rows=1 width=210)
-> XN Hash (cost=0.01..0.01 rows=1 width=210)
-> XN Subquery Scan "IN_subquery" (cost=0.00..0.01 rows=1 width=210)
-> XN Unique (cost=0.00..0.00 rows=1 width=28)
-> XN Seq Scan on contacts (cost=0.00..0.00 rows=1 width=28)
-> XN Hash (cost=0.45..0.45 rows=1 width=210)
-> XN Seq Scan on bh_leads (cost=0.00..0.45 rows=1 width=210)
-> XN Hash (cost=0.32..0.32 rows=1 width=402)
-> XN Subquery Scan "IN_subquery" (cost=0.30..0.32 rows=1 width=402)
-> XN HashAggregate (cost=0.30..0.31 rows=1 width=402)
-> XN Seq Scan on campaign_extraction_history (cost=0.00..0.30 rows=1 width=402)
-> XN Hash (cost=0.35..0.35 rows=1 width=402)
-> XN Subquery Scan "IN_subquery" (cost=0.33..0.35 rows=1 width=402)
-> XN HashAggregate (cost=0.33..0.34 rows=1 width=402)
-> XN Seq Scan on campaign_extraction_history (cost=0.00..0.33 rows=1 width=402)
-> XN Hash (cost=0.50..0.50 rows=1 width=210)
-> XN Seq Scan on bh_leads (cost=0.00..0.50 rows=1 width=210)
-> XN Hash (cost=0.50..0.50 rows=1 width=210)
-> XN Seq Scan on bh_leads (cost=0.00..0.50 rows=1 width=210)
-> XN Hash (cost=0.40..0.40 rows=1 width=402)
-> XN Seq Scan on campaign_extraction_history (cost=0.00..0.40 rows=1 width=402)
-> XN Hash (cost=0.30..0.30 rows=30 width=402)
-> XN Seq Scan on ce_locked_records_tb (cost=0.00..0.30 rows=30 width=402)
-> XN Hash (cost=0.27..0.27 rows=1 width=210)
-> XN Subquery Scan "IN_subquery" (cost=0.26..0.27 rows=1 width=210)
-> XN HashAggregate (cost=0.26..0.26 rows=1 width=210)
-> XN Seq Scan on bh_leads (cost=0.00..0.25 rows=1 width=210)
请提出是否有优化此查询的方法。
感觉就像一次又一次地添加了查询,具有大量的代码重复和许多不必要的表扫描。
理解我的主要经验是使用MSSQL,而不是redshift,但对于大多数情况,将应用相同的原理。
(
lower(xa.primary_function) in (
select
lower(param_val)
from
ce_campaign_spec_tb
where
job_id = '1d8db587-f5ab-41f4-9c2b-c4e21e0c7481'
and param = 'primary_function'
and relation_id = 4
)
and lower(xa.role) in (
select
lower(param_val)
from
ce_campaign_spec_tb
where
job_id = '1d8db587-f5ab-41f4-9c2b-c4e21e0c7481'
and param = 'role'
and relation_id = 4
)
and lower(xa.title) in (
select
lower(title)
from
contacts con
inner join ce_campaign_spec_tb camp on lower(con.title) ilike '%' || trim(
both ' '
from
camp.param_val
) || '%'
where
job_id = '1d8db587-f5ab-41f4-9c2b-c4e21e0c7481'
and param = 'title'
and relation_id = 4
)
)
在不知道这样做的情况下,您似乎将这段代码重复了5次,唯一的变化是related_id。您从ID 4开始,然后是2,然后是1,然后是3然后是5,但是其他的ID似乎没有变化。可能会有细微的差异,但是现在您开始分别扫描表5次,而不是一次使用单个谓词进行一次扫描。根据表的大小,这可能是您正在扫描的大量数据
再往后几行:
and xa.contact_id not in (
select
contact_id
from
bh_leads
where
(CURRENT_DATE - creation_date :: date) <= 60
and UPPER(LOB) = 'ABC'
and agency_id = '1002'
)
and xa.contact_id not in (
select
contact_id
from
bh_leads
where
(CURRENT_DATE - creation_date :: date) <= 60
and UPPER(LOB) = 'ABC'
and sponsor_id = '8306'
)
再次有2个表扫描几乎相同的数据,唯一不同的是检查了Sponsor_id的值,而另一个检查了agency_id。这本可以在单个语句中完成,而不是2
再向下:
and email_id not in (
select
distinct email_id
from
contacts
where
is_email_suppressed = 1
)
之前,您引用了联系人(xa)并将其作为谓词放在where子句中:
and xa.is_email_suppressed = 0
在不确定所涉及表的确切架构的情况下,我无法确定,但是它们似乎在做相同的事情。
也可以从Redshift文档中获得:https : //docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html
看来您可以在单个会话期间创建临时表。可以准备大多数子查询,因此您可以加入结果集。例如,如果您首先为带有有效结果的campaign_extraction_history表准备一个临时结果集,则可以用单个左联接替换以下谓词:
AND contact_id NOT IN (
select
contact_id
from
campaign_extraction_history
where
sf_oms_campaign_id = 'ABC-013505-2-2020'
and sf_campaign_id = 'ABC-013505'
and (CURRENT_DATE - creation_date :: date) < 1
and channel = 'BOTH'
and (
UPPER(STATUS) = 'EXTRACTED'
OR UPPER(STATUS) = 'LAUNCHED'
OR UPPER(STATUS) = 'CONFIRMED'
)
)
AND contact_id NOT IN (
select
contact_id
from
campaign_extraction_history
where
creation_date :: date = CURRENT_DATE
and channel = 'BOTH'
and (
UPPER(STATUS) = 'EXTRACTED'
OR UPPER(STATUS) = 'LAUNCHED'
OR UPPER(STATUS) = 'CONFIRMED'
)
group by
contact_id
having
count(*) > 10
)
AND contact_id NOT IN (
select
contact_id
from
campaign_extraction_history
where
sf_campaign_id = 'ABC-013505'
and channel = 'BOTH'
and (
UPPER(STATUS) = 'EXTRACTED'
OR UPPER(STATUS) = 'LAUNCHED'
OR UPPER(STATUS) = 'CONFIRMED'
)
group by
contact_id
having
count(*) >= 3
)
在更多的地方,您可以一次组合查询的内容并从表中获取数据。例如,您排除了许多email_id值,但是在不同的语句和子查询中的各个位置。它们很可能在单个语句中完成。
也许提高性能的最好方法是问问自己查询要执行和排除的内容,然后重写整个查询。这可能需要大量工作,但从长远来看最终可能会更快。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句