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

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Billboard image

Try REST API Generation for Snowflake

DevOps for Private APIs. Automate the building, securing, and documenting of internal/private REST APIs with built-in enterprise security on bare-metal, VMs, or containers.

  • Auto-generated live APIs mapped from Snowflake database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay