SQL Query Analyzer: Find Why Your Query Is Slow

How the SQL Query Analyzer handles your SQL

The Basic level reads your SQL the way a database parser starts to: it splits the text into tokens, so a keyword inside a string, a quoted name or a comment is never mistaken for part of the query. It then looks at the structure of each statement (the select list, the FROM clause and its joins, the WHERE clause, the sort and the paging) and checks it against ten rules.

Each rule describes a pattern that costs time or returns wrong results on any database: reading every column, a filter that cannot use an index, tables combined without a join condition, a subquery that silently returns nothing when it meets a NULL. A finding says where the pattern is, why it matters and how to fix it, and names the table, column or pattern it found exactly as you wrote it.

At the Intermediate level you also paste the CREATE TABLE and CREATE INDEX statements for the tables in the query. The analyzer then checks the query against them: columns that do not exist, comparisons that force a type conversion, joins between different types, and filters, joins and sorts that no index can serve. It also corrects the Basic findings: a condition such as LOWER(email) = ... backed by an index on that expression is no longer reported, and NOT IN is reported only when the subquery column can be NULL.

At the Advanced level you paste the execution plan as well: the output of EXPLAIN ANALYZE in PostgreSQL or MySQL 8, or an actual execution plan saved as XML in SQL Server. It is the only level that sees what really happened: how many rows each step read and returned, how long it took, whether a sort or a hash spilled to disk, and how far the planner's estimates were off. Each finding points to the plan line it comes from.

What neither level can know is your data. The analyzer does not see row counts, statistics or the plan the database chose, so a finding is a strong hint rather than a measurement: a leading-wildcard LIKE on a table of fifty rows costs nothing. To see what the database actually did, run EXPLAIN ANALYZE in PostgreSQL or MySQL 8, or look at the actual execution plan in SQL Server.

Everything happens in this page. Your SQL is not uploaded, stored or sent to any service, so the analyzer is safe to use on queries that name real tables and customers.

Using it, step by step

1. Paste one or more SQL statements into the SQL query box, or select Load example to see a query with several problems.
2. Choose the database the query runs on: PostgreSQL, MySQL or SQL Server.
3. Choose the level. Basic reads only the query; Intermediate also needs the CREATE TABLE and CREATE INDEX statements for its tables; Advanced needs the execution plan from EXPLAIN ANALYZE, or the actual plan as XML from SQL Server.
4. Select Analyze query, or press Ctrl + Enter (Cmd + Enter on a Mac).
5. Work through the findings from high severity down. Each one shows the line it is on, why it matters and how to fix it.
6. Fix the query, analyze it again, and copy or download the report to attach to a ticket or a pull request.

A worked example

This query finds a customer's open orders. It returns the right rows, but it has three problems:

SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE LOWER(c.email) = 'ana@example.com'
  AND o.status = 'open'
ORDER BY o.created_at DESC;

The analyzer reports them like this:

• Medium SQA001 SELECT * reads every column: SELECT * on orders returns every column, including ones the application may never use.
• Medium SQA004 Function applied to a filtered column: LOWER(c.email) in the WHERE clause hides c.email from its index.
• Info SQA008 ORDER BY without a row limit: The result is sorted but not limited, so every matching row is sorted before the first one is returned.

The rewrite lists only the columns it uses, compares the stored value directly and returns one page of rows:

SELECT o.id, o.total, o.created_at
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.email = 'ana@example.com'
  AND o.status = 'open'
ORDER BY o.created_at DESC
LIMIT 50;

LOWER() can go because the application saves email addresses in lower case. If yours does not, keep the condition and create an index on the expression LOWER(email) instead, which makes it indexable.

At the Intermediate level, add the definitions of the two tables:

CREATE TABLE customers (
    id    bigint PRIMARY KEY,
    email varchar(200) NOT NULL
);

CREATE TABLE orders (
    id          bigint PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers (id),
    status      text NOT NULL,
    total       numeric(12, 2),
    created_at  timestamptz NOT NULL
);

CREATE INDEX orders_status_created ON orders (status, created_at);

The analyzer then also reports:

• Medium SQA108 Foreign key without an index: The foreign key orders(customer_id) references customers and this query joins on it, but it has no index.

PostgreSQL does not index foreign keys by itself, so every order of the customer is found by scanning orders. An index on orders (customer_id) fixes the join, and the index on (status, created_at) already serves the status filter, so nothing is reported for it.

Where this fits in day-to-day work

• Finding out why a query is slow before opening a database console or asking a DBA.
• Reviewing the SQL in a pull request for the patterns that cause slow pages and full table scans.
• Catching an UPDATE or DELETE without a WHERE clause before it runs against production.
• Learning which ways of writing a condition stop a database from using an index.
• Checking queries that name real tables and customers without pasting them into a hosted service.

When the output is not what you expected

The query could not be read

The analyzer stops when a string, a quoted name or a comment is not closed, and names the line and column where it starts. Close the quote or the comment. For PostgreSQL dollar-quoted bodies, check that the closing $$ or $tag$ matches the opening one exactly.

A finding does not apply to your table

The Basic level reads only the SQL, not your schema or data. A leading-wildcard LIKE on a small lookup table, or ORDER BY on a result you know is tiny, costs nothing. Treat findings as questions to check against your row counts, then confirm with EXPLAIN ANALYZE or the actual execution plan.

A slow query produced no findings

The Basic level checks the query text only. Missing indexes, stale statistics, locking and a poor plan are invisible in the text; run EXPLAIN ANALYZE (PostgreSQL, MySQL 8) or capture the actual execution plan in SQL Server to see where the time goes.

Every table is reported as not in the schema

Tables are matched by name, ignoring the schema prefix and letter case, so dbo.Orders matches orders. Check that the Schema box holds the CREATE TABLE statements themselves, not SELECT results or a diagram, and that the same database is chosen for both boxes: its quoting rules decide how names such as [Orders] are read.

An index you created is not taken into account

The Intermediate level reads CREATE INDEX statements and the PRIMARY KEY, UNIQUE and KEY entries inside CREATE TABLE. An index whose first column differs from the filtered one cannot find rows by that column, which is exactly what SQA104 reports. Full-text, spatial and GIN indexes are only used for the searches they are built for.

The execution plan is not recognized

Paste the plan exactly as the database prints it: the rows of EXPLAIN ANALYZE from psql or pgAdmin (quoted lines and the QUERY PLAN header are fine), the JSON of EXPLAIN (ANALYZE, FORMAT JSON), the tree from MySQL's EXPLAIN ANALYZE, or the XML of an actual plan from SQL Server Management Studio. Graphical plans and screenshots cannot be read.

Most Advanced findings are missing

Check that the plan has measured rows and times. A plan from EXPLAIN without ANALYZE, or an estimated plan from SQL Server, only has estimates, and the analyzer says so with SQA213. Run the query with EXPLAIN ANALYZE, or with Include Actual Execution Plan turned on.

Tables in a comma-separated FROM are reported as not joined

The check looks for equality conditions between qualified columns, such as o.customer_id = c.id, that link every table in the FROM list. Qualify the join columns with their table or alias, or rewrite the query with explicit JOIN ... ON clauses, which is clearer anyway.

Questions about the SQL Query Analyzer