Is the Presto Pinot connector will not get update anymore? We are trying to switch from Presto to Trino, but some sql are using much more memories than Presto. Like this one, it only cost 140M memory on Presto, but on Trino it will OOM.

WITH tmp_table_vmrss_inc_steps AS (
		WITH t1 AS (
				SELECT device_sn, event_time, ts, vmrss
					, row_number() OVER (PARTITION BY device_sn, event_time ORDER BY ts ASC) AS rank
				FROM (
					SELECT format_datetime(from_unixtime((ts - ts % (60 * 60 * 1000)) / 1000 + 8 * 60 * 60), 'yyyy-MM-dd HH:mm:00') AS event_time
						, ts, device_sn, vmrss
					FROM pad_streamer_track_info
					WHERE ts >= 1644494400000
						AND ts < 1644498000000
				) t
			), 
			t2 AS (
				SELECT device_sn, event_time, ts, vmrss
					, rank - 1 AS rank
				FROM t1
				WHERE rank > 1
			)
		SELECT device_sn, event_time
			, sum(CASE 
				WHEN diff > 0 THEN 1
				ELSE 0
			END) AS vmrss_inc_steps
		FROM (
			SELECT t2.ts, t2.device_sn, t2.event_time, t2.vmrss - t1.vmrss AS diff
			FROM t2
				LEFT JOIN t1
				ON t1.device_sn = t2.device_sn
					AND t1.event_time = t2.event_time
					AND t2.rank = t1.rank
		) a
		GROUP BY device_sn, event_time
	), 
	tmp_table_device_report_num AS (
		SELECT device_sn, event_time, count(vmrss) AS device_report_num
		FROM (
			SELECT format_datetime(from_unixtime((ts - ts % (60 * 60 * 1000)) / 1000 + 8 * 60 * 60), 'yyyy-MM-dd HH:mm:00') AS event_time
				, ts, device_sn, vmrss
			FROM pad_streamer_track_info
			WHERE ts >= 1644494400000
				AND ts < 1644498000000
		) t
		GROUP BY device_sn, event_time
	), 
	all_table AS (
		SELECT device_sn, event_time
		FROM (
			SELECT device_sn, event_time
			FROM tmp_table_vmrss_inc_steps
			UNION ALL
			SELECT device_sn, event_time
			FROM tmp_table_device_report_num
		)
		GROUP BY device_sn, event_time
	)
SELECT all_table.device_sn, all_table.event_time, coalesce(tmp_table_vmrss_inc_steps.vmrss_inc_steps, 0) AS vmrss_inc_steps
	, coalesce(tmp_table_device_report_num.device_report_num, 0) AS device_report_num
FROM all_table
	LEFT JOIN tmp_table_vmrss_inc_steps
	ON all_table.device_sn = tmp_table_vmrss_inc_steps.device_sn
		AND all_table.event_time = tmp_table_vmrss_inc_steps.event_time
	LEFT JOIN tmp_table_device_report_num
	ON all_table.device_sn = tmp_table_device_report_num.device_sn
		AND all_table.event_time = tmp_table_device_report_num.event_time
LIMIT 1000

@Elon ^^

This looks like it's because pushdown is not being used. Which tables are trino tables? I can help you put together a passthrough query that will work. Also, what version of trino are you running?

I’m using version 370, trino table is pad_streamer_track_info, others are temp table.

@troywinter was the issue resolved? I am trying to assess Pinot connector in Presto vs. Trino.

I see that this query may be modified - is this the longest subquery?

SELECT device_sn, event_time, count(vmrss) AS device_report_num
		FROM (
			SELECT format_datetime(from_unixtime((ts - ts % (60 * 60 * 1000)) / 1000 + 8 * 60 * 60), 'yyyy-MM-dd HH:mm:00') AS event_time
				, ts, device_sn, vmrss
			FROM pad_streamer_track_info
			WHERE ts >= 1644494400000
				AND ts < 1644498000000
		) t
		GROUP BY device_sn, event_time
	),