Query tuning postgres

Question

I'm trying to reduce response time for below postgresql query from current 5 seconds to 1...attaching explain plan too for this query..please help...

(
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

Below is explain plan for the query...

https://explain.depesz.com/s/xgC2

I once posted an answer on how to improove performance on big data analysis, maybe you'll find some useful info there about query planning etc.. — iLuvLogix
– iLuvLogix, Commented Nov 14, 2018 at 12:20
Could you not wrap all those into the same query and return the results on a single row ? — Gurmokh
– Gurmokh, Commented Nov 14, 2018 at 13:32
Please don't edit your question into another after receiving an answer. — Rob
– Rob ♦, Commented Nov 19, 2018 at 5:59

404 · Accepted Answer · 2018-11-14 14:04:26Z

Read the table once, do your formatting after:

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

Collectives™ on Stack Overflow

Query tuning postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related