EXPLAIN with plain-language tips
Right-click any query in the editor → EXPLAIN. Quay runs the dialect-appropriate plan command and surfaces tips in plain language alongside the raw plan tree.
What it shows
Section titled “What it shows”Two panels side-by-side:
- Plan tree — the engine’s native plan, indented per node, with cost / time / rows annotations
- Tips — flagged issues from a rule-based interpreter:
- Sequential scan on a big table (“
pg_classreports 4.2M rows, no index used; consider an index on the WHERE clause”) - Sort spilling to disk (work_mem too small)
- Nested loop with high outer rows (likely missing index on inner side)
- Hash join with skewed bucket (data distribution mismatch)
- CTE materialization that could inline (PG 12+)
- Index-only scan with high heap fetches (vacuum / analyse)
- Function in WHERE preventing index (
WHERE upper(col) = …) - Implicit cast preventing index (
WHERE int_col = '42')
- Sequential scan on a big table (“
Tips are deterministic: same plan + same Quay version = same
tips. No AI in the loop. The rule engine is in
src/components/ExplainModal.tsx::parsePlan if you want to read
the source.
Per-dialect coverage
Section titled “Per-dialect coverage”| Dialect | EXPLAIN command | Plan format |
|---|---|---|
| PostgreSQL | EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <query> | JSON tree |
| MySQL / MariaDB | EXPLAIN FORMAT=JSON <query> | JSON tree |
| SQL Server | SET SHOWPLAN_XML ON; <query>; SET SHOWPLAN_XML OFF; | XML |
| SQLite | EXPLAIN QUERY PLAN <query> | Indented text |
Each dialect’s parser maps to the same internal node shape so the tip-rules apply uniformly. New dialects (DuckDB / ClickHouse / Cassandra-CQL) are on the roadmap.
Read the plan, not just the tips
Section titled “Read the plan, not just the tips”The tips are starting points. Quay’s plan tree is interactive:
- Click a node → highlight in the source SQL (where applicable)
- Hover → cost / time / rows / actual-vs-estimated mismatch
- Right-click → “Add suggested index” — emits the candidate
CREATE INDEXstatement into a new editor tab; you decide whether to run it
Cost discrepancy ⏵: if estimated and actual rows diverge by
10×, Quay flags the node in red. That usually means the planner has stale statistics — running
ANALYZE <table>(PG) /ANALYZE TABLE …(MySQL) often fixes the plan choice.
When AI is enabled
Section titled “When AI is enabled”If you’ve turned on Settings → Pro Plus → AI assistant and selected “AI EXPLAIN”, the tips panel adds an AI commentary section below the deterministic tips. It says, in natural language, “this is a typical hash-join-vs-merge-join trade-off; if you’re hitting this every query, …”. The deterministic tips above stay unchanged — AI augments, doesn’t replace.
If AI is off, the AI commentary section just doesn’t appear. No “sign up to enable AI” upsell. See AI assistant.
Limits
Section titled “Limits”- Read-only side effects.
EXPLAIN ANALYZEactually runs the query (with side effects suppressed). For an estimate-only plan, pick “EXPLAIN (no ANALYZE)” in the dropdown — much faster, but cost numbers are estimates, not measured. - Large plans (1000+ nodes) — the rule engine works, but the tip-flagging gets noisy. We tune toward “fewer, more actionable tips” rather than flagging every node.
- Parameterised queries — pass placeholder values via the parameters panel; the planner needs concrete values to pick the right plan.
Why we don’t lean on AI for this
Section titled “Why we don’t lean on AI for this”The rule-based engine catches the 80% of common slow-query patterns deterministically + cheaply. AI is helpful for the long tail — “the query has 47 CTEs and the optimiser made a counter-intuitive choice; explain why” — but the bulk of “your query is slow because you have a seq scan on a 4M-row table” doesn’t need GPT to detect. The North Star (confidence over breadth) leans toward “I always get the same correct answer” over “AI sometimes explains it more eloquently”.