Postgresql query tuning

suresh selvaraj — Fri, 23 Aug 2024 09:36:41 +0000

WITH COUNTRYCODETONAME AS--external api
(SELECT *
FROM UNNEST(STRING_TO_ARRAY('AD|AE|AF|AG','|'),

                            STRING_TO_ARRAY('Andorra|United Arab Emirates|Afghanistan|Antigua and Barbuda|Anguilla','|')) AS X(CODE,

                                                                        VALUE)) ,
SHIPCOMPCODETONAME AS--carrier code
(SELECT *
    FROM UNNEST(STRING_TO_ARRAY('0015|0002|0011|0001',

                                                        '|'),

                            STRING_TO_ARRAY('APL|ANL|CNC|CMA CGM',

                                '|')) AS X(CODE,

                                                                        VALUE))

SELECT BWS.STATUS,
BWS.BOOKING_WINDOW_ID,
BWS.COUNTRY_CODE,---count.value
BWS.SHIPCOMP_CODE,--carrier
BWS.BOOKING_WINDOW_DAYS,
BWS.VALID_FROM,
BWS.VALID_TO,
MAX(CREATED_DATE) AS CREATED_DATE,
MAX(MODIFIED_DATE) AS MODIFIED_DATE,
STRING_AGG(DISTINCT CREATED_BY,

    '|') AS CREATED_BY,
STRING_AGG(DISTINCT MODIFIED_BY,

    '|') AS MODIFIED_BY,
STRING_AGG(DISTINCT CAST(BWS.CUSTOMER_RANK AS varchar),

    ',') AS CUSTOMER_RANK,
STRING_AGG(DISTINCT BWS.ORIGIN,

    ',') AS ORIGIN,
STRING_AGG(DISTINCT BWS.DESTINATION,

    ',') AS DESTINATION,
STRING_AGG(DISTINCT BWS.CARGO_TYPE_CODE,

    ',') AS CARGO_TYPE_CODE,
STRING_AGG(DISTINCT BWS.CONTRACT_REFERENCE,

    ',') AS CONTRACT_REFERENCE

FROM BOOKING_WINDOW.BOOKING_WINDOW_STRATEGY BWS

INNER JOIN COUNTRYCODETONAME COUNTRYCODETONAME ON BWS.COUNTRY_CODE = COUNTRYCODETONAME.CODE

where exists
(SELECT 1
FROM BOOKING_WINDOW.BOOKING_WINDOW_STRATEGY BWS1--remove
INNER JOIN SHIPCOMPCODETONAME SHIPCOMPCODETONAME ON BWS1.SHIPCOMP_CODE = SHIPCOMPCODETONAME.CODE
where
SHIPCOMPCODETONAME.code in ('APL','ANL','CNC'))

GROUP BY BWS.STATUS,
BWS.BOOKING_WINDOW_ID,
BWS.COUNTRY_CODE,
BWS.SHIPCOMP_CODE,
BWS.BOOKING_WINDOW_DAYS,
BWS.VALID_FROM,
BWS.VALID_TO
ORDER BY MIN(COUNTRYCODETONAME.code) DESC
limit 50
offset 0;

need to optimize the sql query

Forem: suresh selvaraj

Postgresql query tuning