skills.rest
treasure-data
treasure-dataOFFICIAL
Explore all skills by this author →
WebsiteGitHub

trino-optimizer

Optimize Trino SQL queries with CTAS, UDP bucketing, and approximate functions.

21|24|Updated Oct 21, 2025
Search Tags:#query optimization#memory management#sql#trino#performance tuning#cost reduction#treasure data
One-click install
npx skills add https://github.com/treasure-data/td-skills --skill trino-optimizer
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: trino-optimizer
Source: https://github.com/treasure-data/td-skills/tree/main/sql-skills/trino-optimizer
Command: npx skills add https://github.com/treasure-data/td-skills --skill trino-optimizer

SYSTEM DOCUMENTATION & REQUIREMENTS

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'

Frequently Asked Questions about trino-optimizer

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I optimize slow Trino queries to reduce memory usage and costs?▼

Optimize Trino queries by adding time-based partition filters, replacing COUNT(DISTINCT) with APPROX_DISTINCT, selecting only needed columns, and using CREATE TABLE AS for output. These techniques reduce memory footprint and execution time across large analytic workloads.

When should I use APPROX_DISTINCT instead of COUNT(DISTINCT) in Trino?▼

Use APPROX_DISTINCT for large-scale aggregations where approximate results are acceptable; it significantly reduces memory consumption with minimal accuracy loss, making it ideal for cardinality estimation on big datasets where exact counts cause timeouts or memory errors.

How do I analyze and fix a Trino query that's timing out or exceeding memory limits?▼

Use EXPLAIN to review the query plan and identify bottlenecks. Apply targeted optimizations: add time filters using TD_INTERVAL for partition pruning, replace multiple LIKE conditions with REGEXP_LIKE, use approximate functions, and materialize results with CTAS instead of returning raw output.

What's the best way to handle large table joins in Trino for better performance?▼

Control join distribution using magic comments to guide the query planner, implement UDP bucketing for partitioned ID lookups on large tables, and ensure time-based filters are applied before joins to reduce data volume and improve execution speed.

Can I convert multiple LIKE conditions to a single expression in Trino?▼

Yes, replace multiple LIKE clauses with REGEXP_LIKE to reduce query overhead and improve performance. This consolidation reduces parsing time and memory usage while maintaining pattern-matching functionality across large datasets.

How do I use CREATE TABLE AS to speed up Trino query results?▼

CREATE TABLE AS materializes query results directly to storage instead of returning raw output, reducing network overhead and enabling faster repeated access. This approach is especially effective for dashboard queries and downstream analytics.

Related Skills

Explore More Data & Analytics Skills→
sahiixxsahiixx
2

schema_exploration

List database tables, detail columns, and map foreign-key relationships.

Community
Basic
nirholasnirholas
71

metrics

Collect, store, export, and query system metrics via a TypeScript API.

Community
Advanced
HieubkavHieubkav

sql-optimization-patterns

Analyze SQL query plans with EXPLAIN and implement indexing strategies.

Community
Advanced
NachoOsellaNachoOsella
6

sql-optimizer

Analyze SQL query plans and generate index recommendations for PostgreSQL, MySQL, and SQLite.

Community
Intermediate