Performance optimisation with postgresql query

by Rahultech   Last Updated November 14, 2018 03:06 AM

Please suggest some further tuning to reduce responses time

SELECT ITS.KeyPanjivaCompanyTrdPartner ,SUM(ITS.ShipmentCount) AS TotalShipments INTO TEMP Top30Companies FROM summary AS ITS
JOIN company AS PC ON ITS.Keypartner = PC.Keycomp 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.Keypartner ORDER BY TotalShipments DESC LIMIT 30;

--Now do the final select

( SELECT 1 AS RowNumber ,'Total Companies' AS RowLabel ,COUNT(DISTINCT ITS.Keypartner) AS Aggregation FROM summary AS ITS
JOIN company AS PC ON ITS.Keypartner = PC.Keycomp 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 2 AS RowNumber ,'Shipments (Top 30 companies)' AS RowLabel ,SUM(ShipmentCount) AS TotalShipmentCount FROM Summary AS ITS
JOIN Company AS PC ON ITS.KeyComptrdprt = PC.Keycomp 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.Keycomptrdprt IN (SELECT Keycomptrdprt FROM Top30Companies) GROUP BY ITS.KeyInstn

UNION

SELECT 3 AS RowNumber ,'Weight in kg (Top 30 companies)' AS RowLabel ,SUM(COALESCE(ITS.ShipmentWeightAR, ITS.ShipmentWeightEst)) AS TotalShipmentWeight FROM summary AS ITS
JOIN Company AS PC ON ITS. Keycomptrdprt= PC.Keycomp 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.Keycomptrdprt IN (SELECT Keycomptrdprt FROM Top30Companies) GROUP BY ITS.KeyInstn

UNION

SELECT 4 AS RowNumber ,'Volume in TEU (Top 30 companies)' AS RowLabel ,SUM(COALESCE(ITS.ShipmentVolumeAR, ITS.ShipmentVolumeEst)) AS TotalShipmentVolume FROM Summary AS ITS
JOIN Company AS PC ON ITS.=Keycomptrdprt PC.Keycomp 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. Keycomptrdprt IN (SELECT Keycomptrdprt FROM Top30Companies) GROUP BY ITS.KeyInstn)

ORDER BY RowNumber

DROP TABLE Top30Companies



Related Questions


Make PostgreSQL perform SELECT queries in memory

Updated September 03, 2017 22:06 PM


Prune unused joins

Updated October 10, 2017 14:06 PM