sql - 优化以下 postgreSQL 代码的可能方法是什么?
问题描述
我编写了这个 SQL 查询来从 greenplum datalake 中获取数据。主表有 800,000 行,我正在与其他表连接。下面的查询花费了大量的时间来给出结果。查询时间较长的可能原因是什么?如何解决?
select
a.pole,
t.country_name,
a.service_area,
a.park_name,
t.turbine_platform_name,
a.turbine_subtype,
a.pad as "turbine_name",
t.system_number as "turbine_id",
a.customer,
a.service_contract,
a.component,
c.vendor_mfg as "component_manufacturer",
a.case_number,
a.description as "case_description",
a.rmd_diagnosis as "case_rmd_diagnostic_description",
a.priority as "case_priority",
a.status as "case_status",
a.actual_rootcause as "case_actual_rootcause",
a.site_trends_feedback as "case_site_feedback",
a.added as "date_case_added",
a.start as "date_case_started",
a.last_flagged as "date_case_flagged_by_algorithm_latest",
a.communicated as "date_case_communicated_to_field",
a.field_visible_date as "date_case_field_visbile_date",
a.fixed as "date_anamoly_fixed",
a.expected_clse as "date_expected_closure",
a.request_closure_date as "date_case_request_closure",
a.validation_date as "date_case_closure",
a.production_related,
a.estimated_value as "estimated_cost_avoidance",
a.cms,
a.anomaly_category,
a.additional_information as "case_additional_information",
a.model,
a.full_model,
a.sent_to_field as "case_sent_to_field"
from app_pul.anomaly_stage a
left join ge_cfg.turbine_detail t on a.scada_number = t.system_number and a.added > '2017-12-31'
left join tbwgr_v.pmt_wmf_tur_component_master_t c on a.component = c.component_name
解决方案
您的查询基本上是:
select . . .
from app_pul.anomaly_stage a left join
ge_cfg.turbine_detail t
on a.scada_number = t.system_number and
a.added > '2017-12-31' left join
tbwgr_v.pmt_wmf_tur_component_master_t c
on a.component = c.component_name
首先,条件 ona
被忽略,因为它是left join
andon
子句中的第一个表。因此,我假设您实际上打算对其进行过滤,因此将查询编写为:
select . . .
from app_pul.anomaly_stage a left join
ge_cfg.turbine_detail t
on a.scada_number = t.system_number left join
tbwgr_v.pmt_wmf_tur_component_master_t c
on a.component = c.component_name
where a.added > '2017-12-31'
这可能有助于提高性能。然后在 Postgres 中,您需要在turbine_detail(system_number)
和上建立索引pmt_wmf_tur_component_master_t(component_name)
。索引是否对第一个表有帮助是值得怀疑的,因为您已经在选择大量数据。
我不确定索引在 Greenplum 中是否合适。
推荐阅读
- stackify - 如何在 Stackify 跟踪中查看“ASP.NET HTTP 模块”的详细信息?
- c# - 是否可以在 Unity 的自定义检查器中显示部分枚举?
- assembly - 如何在保护模式下切换到 320x200 256 色且没有 bios 中断
- nexus - 通过 REST 检索 Nexus 的文件信息
- sql - SQL右连接返回空白行
- java - 如何从 IntelliJ 中的现有包创建新项目?
- prometheus - 无法在 Grafana 中获取 Prometheus SD 目标
- c - 如何在c编程中做循环
- php - 未定义索引:图像 - Laravel
- wso2 - 无法从 Store 获取消息