Query slowness in v475 after upgrading from v370

[xero08]

Hello all,

We are performing an upgrade of our Trino installation from v370 to v475. While we’re seeing consistent performance as was in the older version, the newer version is timing out when running certain queries.

Following is one such query:

SELECT a.*
      FROM   "catalog".schema_name_1.table_name_1 a
             JOIN schema_name_2.table_name_2 b
               ON a.dma_id = b.market_id
      WHERE  b.close_id = '12A1'
             AND ( ( Date_parse(a.clct_dt, '%Y-%m-%d') BETWEEN
                           Date_parse('2025-10-27', '%Y-%m-%d') AND
                             Date_add('day', ( Cast('2' AS INT) - 1
                                             ),
                             Date_parse('2025-10-27', '%Y-%m-%d')) )
                    OR (( Date_parse(a.clct_dt, '%Y-%m-%d') = Date_add('day',
                                                              Cast('2' AS INT),
                               Date_parse('2025-10-27', '%Y-%m-%d'))
                          AND a.batch_id IN ( '1' ) )) )
             AND viewed_endtm_utc IS NOT NULL
             AND rlsd_fr_prcs_flg = 'Y' ORDER BY glbl_hh_id LIMIT 10;

It takes ~10s on v370. When we run it on v475, it takes more than 30m and times out (we've an upper limit of 30m).

We did a bit of digging and the possible culprit is the non-sargable predicate used in the query: Date_parse(a.clct_dt, '%Y-%m-%d') BETWEEN …

After changing the query to use a sargable predicate, which looks like the following, the query performs similarly as it used in v370.

SELECT a.*
      FROM   "catalog".schema_name_1.table_name_1 a
             JOIN schema_name_2.table_name_2 b
               ON a.dma_id = b.market_id
      WHERE  b.close_id = '12A1'
             AND ( ( a.clct_dt BETWEEN
                           '2025-10-27' AND
                             cast(Date_add('day', ( Cast('2' AS INT) - 1
                                             ),
                             Date_parse('2025-10-27', '%Y-%m-%d'))  as varchar))
                    OR (( a.clct_dt = cast(Date_add('day',
                                                              Cast('2' AS INT),
                               Date_parse('2025-10-27', '%Y-%m-%d')) as varchar)
                          AND a.batch_id IN ( '1' ) )) )
             AND viewed_endtm_utc IS NOT NULL
             AND rlsd_fr_prcs_flg = 'Y' ORDER BY glbl_hh_id LIMIT 10;

Can we do anything to get around this ? Refactoring the query for sargable predicates is difficult due to its widespread use in areas we don't control.