trino-query-optimization

Optimize slow Trino SQL queries by analyzing the execution plan to detect pushdown failures, poor join strategies, and missing partition pruning. This process enforces filter and projection pushdown, adjusts session properties, and rewrites SQL to reduce memory pressure and unnecessary scans.

Predicate pushdown fails in Trino when filters are not applied early in the Iceberg scan, causing full table scans. Diagnose this by checking the EXPLAIN plan to ensure partition pruning occurs, then rewrite SQL to enforce early filtering and avoid SELECT *.

Tune cross-catalog joins in Trino by selecting appropriate broadcast versus partitioned join strategies and leveraging runtime dynamic filtering. Running ANALYZE-backed CBO guides join reordering to minimize expensive shuffles and reduce scanned data across JDBC-backed connectors.

Reduce shuffle and memory pressure in Trino by configuring session properties for exchange compression and task parallelism. Adjusting these settings minimizes expensive repartitioning and alleviates bottlenecks during distributed query execution.

Dynamic filtering in Trino improves query optimization by generating runtime filters from join operations to reduce scanned data. This mechanism prevents unnecessary reads in partitioned joins by pruning data before the exchange occurs.

Use broadcast joins in Trino for smaller tables to avoid shuffling, while partitioned joins handle larger datasets. Choosing the correct join strategy through execution plan analysis minimizes data movement and optimizes cross-catalog performance.