ReddRadar
Agent skill
trino-optimizer
TD Trino performance optimization including CTAS (5x faster), UDP bucketing for ID lookups, magic comments for join distribution, REGEXP_LIKE vs LIKE, and approx functions.
Install this agent skill to your Project
npx add-skill https://github.com/treasure-data/td-skills/tree/main/sql-skills/trino-optimizer
SKILL.md
TD Trino Query Optimizer
Output Optimization
CTAS is 5x faster than SELECT (skips JSON serialization):
create table results as
select td_time_string(time, 'd!', 'JST') as date, count(*) as events
from events
where td_interval(time, '-1M', 'JST')
group by 1
User-Defined Partitioning (UDP)
Hash partition for fast ID lookups on large tables (>100M rows):
create table customer_events with (
bucketed_on = array['customer_id'],
bucket_count = 512
) as
select * from raw_events
where td_interval(time, '-30d', 'JST')
Accelerated queries (equality on all bucketing columns):
select * from customer_events
where customer_id = 12345
and td_interval(time, '-7d', 'JST')
NOT accelerated (missing bucketing column):
select * from customer_events
where td_interval(time, '-7d', 'JST')
UDP for colocated joins:
-- set session join_distribution_type = 'PARTITIONED'
-- set session colocated_join = 'true'
select a.*, b.*
from customer_events_a a
join customer_events_b b on a.customer_id = b.customer_id
Magic Comments for Join Distribution
-- BROADCAST: Small right table fits in memory
-- set session join_distribution_type = 'BROADCAST'
select * from large_table, small_lookup
where large_table.id = small_lookup.id
-- PARTITIONED: Both tables large or memory issues
-- set session join_distribution_type = 'PARTITIONED'
select * from large_table_a, large_table_b
where large_table_a.id = large_table_b.id
REGEXP_LIKE vs Multiple LIKE
-- BAD: Multiple LIKE clauses
where column like '%android%' or column like '%ios%' or column like '%mobile%'
-- GOOD: Single REGEXP_LIKE
where regexp_like(column, 'android|ios|mobile')
Approximate Functions
approx_distinct(user_id) -- vs count(distinct user_id)
approx_percentile(response_time, 0.95)
approx_set(column) -- HyperLogLog sketch
~2% error rate, dramatically reduced memory.
Common Errors
| Error | Fix |
|---|---|
| Query exceeded memory limit | Narrow time range, use approx functions, try PARTITIONED join |
| PAGE_TRANSPORT_TIMEOUT | Reduce columns, use CTAS, process in smaller chunks |
| Query timeout | Add time filters, use limit for testing |
Optimization Checklist
- Time filter (td_interval/td_time_range)
- Specific columns (not select *)
- approx_distinct for unique counts
- regexp_like for pattern matching
- CTAS for large results
- UDP for frequent ID lookups
Recommended Agent Skills
Expand your agent's capabilities with these related and highly-rated skills.
treasure-data/td-skills
email-campaign
This skill should be used when the user asks to "create an email", "build an email campaign", "design an email template", "generate an email for a segment", "preview an email", or "push an email to Engage". Generates enterprise-grade HTML email templates with live preview in Treasure Studio and natural language editing, then pushes the final version to Treasure Engage.
treasure-data/td-skills
action-report
YAML format reference for action reports rendered via preview_action_report. MUST be read before writing any action report YAML — defines the report structure (title, summary, actions array) and action item fields (as_is, to_be, reason, priority, category, impact) with incremental build workflow. Required by seo-analysis and any skill that produces prioritized recommendations.
treasure-data/td-skills
grid-dashboard
YAML format reference for grid dashboards rendered via preview_grid_dashboard. MUST be read before writing any dashboard YAML — defines the page structure, 6 cell types (kpi, gauge, scores, table, chart, markdown), grid layout rules, cell merging syntax, and incremental build workflow. Required by seo-analysis and any skill that produces visual data dashboards.
treasure-data/td-skills
seo-analysis
Runs SEO and AEO (Answer Engine Optimization) analysis on websites or specific pages. Use when the user mentions SEO, AEO, search rankings, search optimization, or wants to analyze how their pages perform in search engines and AI answers. Produces a data dashboard and action report with before/after recommendations.
treasure-data/td-skills
aps-doc-core
Core documentation generation patterns and framework for Treasure Data pipeline layers. Provides shared templates, quality validation, testing framework, and Confluence integration used by all layer-specific documentation skills.
treasure-data/td-skills
aps-doc-id-unification
Expert documentation generation for ID unification layers. Documents identity resolution algorithms, merge strategies, match rules, entity graphs, and multi-workflow orchestration. Use when documenting ID unification processes.
Didn't find tool you were looking for?