OptiHouseDocs
Open app
Core features

SQL Optimizer

The optimizer takes a single query, runs it through the same Wave 1–7 rule engine that powers cluster recommendations, and returns a ranked list of issues with before/after rewrites. It works standalone — no cluster connection required.

How to use it

  1. 1
    Open the Optimizer
    Use Optimizer in the app sidebar, or the live playground on the landing page.
  2. 2
    Paste a query
    Drop in any SELECT. Start from one of the preset examples if you just want to see it work.
  3. 3
    Set the workload knobs
    Three sliders — average GB read, runs per day, average duration — describe how the query behaves in production. They decide which rules fire.
  4. 4
    Analyze
    You get a health score plus one card per issue: severity, rule name, confidence, expected impact and a copy-ready rewrite.
Why the workload knobs matter
A SELECT * over a tiny lookup table is harmless; the same query reading 10 GB a thousand times a day is not. The knobs let the rule engine judge real cost instead of guessing from the SQL text alone.

What it checks

The optimizer covers the query-level rules from the recommendation catalog. The most common findings are:

IssueRewrite it suggests
Missing PREWHEREMoves a selective predicate into a PREWHERE clause.
FROM ... FINALReplaces FINAL with argMax or LIMIT 1 BY over the version column.
Function on a partition / WHERE keyRewrites the predicate so the partition index can be used.
SELECT *Narrows the projection to the columns actually consumed.
Memory-heavy JOINAdds join_algorithm = 'grace_hash' (or parallel_hash) settings.
Repeated subqueriesCollapses duplicated subqueries into a shared CTE.

Reading the result

Each issue card shows a severity badge, the rule that fired, a confidence percentage and the expected impact. The before/after panel is diff-highlighted and has a copy button. A rewrite tagged paste-ready is an exact transformation; one tagged template still has placeholders you need to fill in — for example a version column the optimizer could not infer from the text alone.

Always review before running
Rewrites are generated from the SQL text, not from your live schema. Confirm column names, version columns and JOIN keys against the actual table before you run a rewritten query in production.

Optimizer vs. recommendations

The optimizer is for one query you are actively working on — paste, rewrite, move on. Recommendations run the same rules continuously across your whole query_log, so they catch the expensive queries you did not think to check. Use the optimizer to fix a specific query; use recommendations to find which queries are worth fixing.