Data Collection

Data Engineering

SQL Performance Optimization

Identify slow queries, poor indexing, and inefficient patterns, and output rewrite plans based on execution plans and database optimization rules, taking SQL from "won't finish" to "sub-second response".

IndustriesAll industries

Agent · Skills Live

skill · loadedindustry+100

Run this Skill on my data

Query console

generated

query.sql · generated

SELECT channel, count(*)

FROM v_payment

GROUP BY channel — 0.4s

12 rows returned

Analysis readyevidence attached

SQL Performance Optimization is a data-engineering capability in ThinkingAI's industry Skill library. It helps developers and data analysts identify the root cause of a bottleneck when a SQL query runs too slow, locate poor indexing and inefficient patterns through execution-plan analysis, and output actionable rewrite plans and tuning suggestions, while ensuring the optimized results are exactly identical to the original query. This is ThinkingAI's decade of industry experience, packaged into ready-to-use Skills.

Live walkthrough

AgentSave as AgentExport session

sql-performance-optimization

There's a user-behavior analysis SQL query that ran for 45 minutes last night without returning and clogged the query queue. I've pasted the SQL: help me see where it's slow and how to fix it.

Read the original SQL and run EXPLAIN ANALYZE to locate the bottleneck

Full-table scan of about 500 million rows, with the Sort node taking 80% of the time

Diagnose the inefficient patterns: missing $part_date / $part_event partition filters, the paying-user subquery computed twice, and a full sort with no LIMIT

Rewrite: add both partition filters, extract the repeated subquery into a WITH intermediate table, and sort after aggregation with a LIMIT as a safety net

Run and verify

Returned in 12.3 seconds, scanning 5.12 million rows

Result-consistency check: compare column count / column names / row count / numeric precision against the original SQL

Exactly consistent

It's rewritten and verified. The same query went from not finishing in 45 minutes to returning in 12.3 seconds, with the scan dropping from about 500 million rows to 5.12 million: the bottleneck isn't the cluster, it's the query.

1. Optimized SQL

sql

WITH pay_users AS (
  SELECT DISTINCT "#user_id"
  FROM ta.v_event_2
  WHERE "$part_date" BETWEEN '2026-06-16' AND '2026-07-15'
    AND "$part_event" = 'payment'
)
SELECT
  e."$part_date"               AS stat_date,
  e."$part_event"              AS event_name,
  COUNT(*)                     AS event_count,
  COUNT(DISTINCT e."#user_id") AS user_count
FROM ta.v_event_2 e
JOIN pay_users p ON e."#user_id" = p."#user_id"
WHERE e."$part_date" BETWEEN '2026-06-16' AND '2026-07-15'
  AND e."$part_event" IN ('login', 'battle_start', 'item_use')
GROUP BY e."$part_date", e."$part_event"
ORDER BY stat_date
LIMIT 500

2. Three changes made

Added both $part_date + $part_event partition filters: the original SQL was missing both, so Trino did a full-table scan of about 500 million rows, the primary cause of the timeout

The 'paying users' subquery appeared twice in the original SQL and was recomputed each time; extracting it into the WITH intermediate table pay_users computes it once

The original SQL did a full sort over detail rows with no LIMIT, and the Sort node ate 80% of the time; sorting after aggregation with a LIMIT safety net fixes it

3. Before / after comparison

Metric	Before	After
Execution time	45 min, no return (timeout)	12.3 seconds
Rows scanned	~500 million	5.12 million
Sort node time share	80%	6%
Result consistency	N/A	Column count / row count / numeric precision exactly consistent

Recommendation

These three pitfalls (missing partitions, repeated subquery, full sort) are a frequent combination in your project's slow queries. Add '$part_date + $part_event must be in the WHERE clause' to the team's SQL review checklist: in an AE / Trino environment, partition pruning is the first gate for performance, higher priority than any index tuning.

The optimized SQL is written back to the IDE query library, and the original version is annotated with the reason it was deprecated.

On your data

That was a simulated run

Leave your work email and we will run a live walkthrough on your real business data.

The problem

Slow queries are the most painful technical problem for data teams, but the optimization direction is often driven by intuition rather than data. A data team handles 3-5 slow queries per week on average, and 60% of the root causes are inefficient patterns rather than missing indexes. A subtler problem is that patterns like SELECT *, inefficient subqueries, DISTINCT overuse, and missing $part_date and $part_event partition filters can inflate the scan volume 3-10x, and even trigger a Trino full-table scan of 500 million rows.

What it does

Execution-plan-driven optimization: first read the execution plan to locate bottleneck nodes (large scan volume, high sort cost, unpruned partitions), then rewrite in a targeted way rather than blindly adding indexes

Result-consistency guarantee: the optimized SQL must produce output exactly identical to the original SQL (zero tolerance on column count, column names, row count, and numeric precision)

Trino partition-field priority check: the AE system is built on the Trino engine, and dual-partition filtering on $part_date and $part_event is the first checkpoint for SQL performance

When to use it

A SQL query taking too long that needs to be optimized for speed

Interpreting an execution plan and locating bottleneck nodes

Optimization plans for poor index design

Optimizing partition-field usage under the Trino engine

Rewrite plans for inefficient SQL patterns (subquery to JOIN, DISTINCT to GROUP BY, etc.)

Performance-tuning suggestions at the database-architecture level

In the field

Case

A game data analyst · slow user-behavior SQL optimization

A user-behavior analysis SQL ran for 45 minutes without returning. The Skill found it was missing $part_date and $part_event filters, triggering a Trino full-table scan of 500 million rows, and the execution plan showed the Sort node consuming 80% of the time. After rewriting to add dual-partition filtering, adjust the ORDER BY fields, and add a LIMIT, the scan range shrank to 5 million rows and query time dropped from 45 minutes to 12 seconds, with results exactly identical to the original SQL.

FAQ

Will the optimized results deviate at all?

No. The Skill applies a zero-tolerance standard for result consistency: column count, column names, row count, and numeric precision must be exactly identical, with verification steps included.

Can adding indexes solve all slow queries?

No. Many slow queries are rooted in inefficient patterns or full-table scans, and blindly adding indexes only raises maintenance costs. The Skill analyzes the execution plan first, then decides the strategy.

How does optimizing on the Trino engine differ from other databases?

The most critical thing on the Trino engine is partition pruning. AE event tables are stored with dual partitions on $part_date and $part_event, and SQL must include both filter conditions to avoid a full-table scan.