Trino best practices

The Trino best practices page describes several ways to optimize Trino queries.

Use `EXPLAIN` for query plans

It’s useful to inspect the query plan before running heavy queries. EXPLAIN shows how Trino handles joins, filters, and table scans.

EXPLAIN SELECT * FROM employees1 WHERE salary > 5000;

For deeper insight, a JSON format reveals scan ranges, cost estimates, and pushdown behavior.

EXPLAIN (FORMAT JSON)
SELECT department_id, AVG(salary)
FROM employees1
GROUP BY department_id;

Push down predicates

Write filters so you can push it down into Iceberg or Hive metadata. This reduces I/O and avoids unnecessary S3 reads.

Bad example:

WHERE CAST(start_date AS DATE) = DATE '2025-01-15'

Good example:
WHERE start_date = DATE '2025-01-15'

Prune columns

Select only the required columns. This helps minimize shuffles and reduces the amount of data pulled from S3.

Bad example:
SELECT * FROM employees1;

Good example:

SELECT employee_id, salary FROM employees1;

Optimize joins

As previously described, use join hints when table sizes are predictable. Broadcast a small dimension table to speed up the join, and apply partitioning for large fact-to-dimension joins.

Bad example:
SELECT /*+ PARTITIONED(f, dim) */ ...
Good example:
SELECT /*+ BROADCAST(dim) */ ...

Limit large scans

Apply filters and LIMIT clauses during early data exploration to avoid scanning entire Iceberg tables.

Bad example:
SELECT * FROM employees1;

Good example:

SELECT *
FROM employees1
WHERE salary > 3000
LIMIT 100;

Pre-compute aggregations with CTAS or materialized views

For dashboards or recurring workloads, pre-compute heavy aggregations using Create Table as Select (CTAS) or materialized views. This reduces the load on Trino and improves Superset’s responsiveness.

CREATE TABLE analytics.employee_salary_by_department AS
SELECT department_id,
       COUNT(*) AS employee_count,
       SUM(salary) AS total_salary
FROM employees1
GROUP BY department_id;

Additional resources

To get an overview of Trino, refer to the Trino in NexusOne page.
To learn practical ways to use Trino in the NexusOne environment, refer to the Trino hands-on examples page.
For more details about Trino, refer to the Trino official documentation.
If you are using the NexusOne portal and want to learn how to launch Trino, refer to the Launch a hosted app page.

​Use EXPLAIN for query plans

​Push down predicates

​Prune columns

​Optimize joins

​Limit large scans

​Pre-compute aggregations with CTAS or materialized views

​Additional resources