DEV Community

suresh selvaraj
suresh selvaraj

Posted on

Postgresql query tuning

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))
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

πŸ‘‹ Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay