trino-optimizer

What problem does it solve?

This Skill provides expert assistance for optimizing Trino query performance in Treasure Data, directly addressing slow queries, memory issues, timeouts, and high costs. It empowers users to write efficient SQL that leverages TD's underlying infrastructure, saving time and reducing operational expenses.

Core Features & Use Cases

• Critical Optimization Principles: Emphasizes time-based partition pruning, selective column retrieval, and efficient output methods (CTAS) to drastically improve query speed and reduce resource consumption.
• Approximate Functions: Guides on using APPROX_DISTINCT and APPROX_PERCENTILE for large-scale aggregations, significantly reducing memory footprint with minimal accuracy loss.
• Query Analysis Workflow: Provides a step-by-step process for analyzing slow queries using EXPLAIN, identifying bottlenecks, and applying targeted optimizations.
• Use Case: A daily dashboard query is consistently timing out or exceeding memory limits. This skill helps the user analyze the query plan, identify missing time filters, replace COUNT(DISTINCT) with APPROX_DISTINCT, and use CREATE TABLE AS for output, transforming a failing query into a fast, cost-effective solution.

Quick Start

Optimize a slow query:

-- Before: -- SELECT COUNT(DISTINCT user_id) FROM events WHERE event_type = 'click' -- After (faster, less memory): SELECT APPROX_DISTINCT(user_id) FROM events WHERE TD_INTERVAL(time, '-1d', 'JST') AND event_type = 'click'