postgresql - 查询调优 postgres
问题描述
我正在尝试将以下 postgresql 查询的响应时间从当前的 5 秒减少到 1 秒……为此查询也附上解释计划……请帮助……
(
SELECT
1 AS RowNumber
,'Total Countries' AS RowLabel
,COUNT(DISTINCT ITS.abc CountryTrading) AS Aggregation
FROM ObjectViews.abc InstnTradeSummary AS ITS
WHERE ITS.KeyInstn = 7402194
AND ITS.TradeDataMonthYearPublish >= date_trunc('month', current_date) + interval '-5 years'
AND ITS.TradeDataMonthYearPublish <= date_trunc('month', current_date)
AND ITS.abc CountryTrading IS NOT NULL
GROUP BY ITS.KeyInstn
UNION
SELECT
2 AS RowNumber
,'Total Shipments' AS RowLabel
,SUM(ITS.ShipmentCount) AS TotalShipments
FROM ObjectViews.abc InstnTradeSummary AS ITS
WHERE ITS.KeyInstn = 7402194
AND ITS.TradeDataMonthYearPublish >= date_trunc('month', current_date) + interval '-5 years'
AND ITS.TradeDataMonthYearPublish <= date_trunc('month', current_date)
GROUP BY ITS.KeyInstn
UNION
SELECT
3 AS RowNumber
,'Total Weight in kg' AS RowLabel
,SUM(COALESCE(ITS.ShipmentWeightAR, ITS.ShipmentWeightEst)) AS TotalShipmentWeight
FROM ObjectViews.abc InstnTradeSummary AS ITS
WHERE ITS.KeyInstn = 7402194
AND ITS.TradeDataMonthYearPublish >= date_trunc('month', current_date) + interval '-5 years'
AND ITS.TradeDataMonthYearPublish <= date_trunc('month', current_date)
GROUP BY ITS.KeyInstn
UNION
SELECT
4 AS RowNumber
,'Total Volume in TEU' AS RowLabel
,SUM(COALESCE(ITS.ShipmentVolumeAR, ITS.ShipmentVolumeEst)) AS TotalShipmentVolume
FROM ObjectViews.abc InstnTradeSummary AS ITS
WHERE ITS.KeyInstn = 7402194
AND ITS.TradeDataMonthYearPublish >= date_trunc('month', current_date) + interval '-5 years'
AND ITS.TradeDataMonthYearPublish <= date_trunc('month', current_date)
GROUP BY ITS.KeyInstn
) ORDER BY RowNumber
以下是查询的解释计划...
解决方案
阅读表格一次,然后进行格式化:
SELECT
v.row_number,
v.row_label,
CASE v.row_number
WHEN 1 THEN s.total_countries
WHEN 2 THEN s.total_shipments
WHEN 3 THEN s.total_shipment_weight
ELSE s.total_shipment_volume
END AS total
FROM (
VALUES
(1, 'Total Countries'),
(2, 'Total Shipments'),
(3, 'Total Weight in kg'),
(4, 'Total Volume in TEU')
) AS v(row_number, row_label)
LEFT JOIN (
SELECT
COUNT(DISTINCT ITS.abc CountryTrading) FILTER (WHERE ITS.abc CountryTrading IS NOT NULL) AS total_countries,
SUM(ITS.ShipmentCount) AS total_shipments,
SUM(COALESCE(ITS.ShipmentWeightAR, ITS.ShipmentWeightEst)) AS total_shipment_weight,
SUM(COALESCE(ITS.ShipmentVolumeAR, ITS.ShipmentVolumeEst)) AS total_shipment_volume
FROM ObjectViews.abc InstnTradeSummary AS ITS
WHERE ITS.KeyInstn = 7402194
AND ITS.TradeDataMonthYearPublish >= date_trunc('month', current_date) + interval '-5 years'
AND ITS.TradeDataMonthYearPublish <= date_trunc('month', current_date)
GROUP BY ITS.KeyInstn
) AS s ON TRUE
ORDER BY v.row_number
推荐阅读
- javascript - 如何在javascript中的.map函数中拆分嵌套的三元语句?
- react-native - 不变违规:元素类型无效 React native
- arrays - Exchange Powershell 脚本消耗本地 pc 上的所有系统资源
- kotlin - 无可调试进程:Android studio 3.4.1
- reactjs - 如果组件定义的 propType 中没有定义 prop,如何配置 Eslint 报错?
- text - 他们是如何在 TheDailyBeast.com 的某个角度设置文本图形的?
- javascript - 在不使用 jquery 的情况下从 javascript 隐藏引导模式
- reactjs - 如何从 axios GET 请求中渲染表格?
- shell - 如何在 Jest 中将 shell 脚本作为安装文件运行?
- nuget - 如何将自定义 XML 标记添加到 NuGet .nuspec 文件?