Most slow queries are not slow because the database is bad at its job. They are slow because the query is written in a way that forbids the fast path: a function wrapped around an indexed column, an index with its columns in the wrong order, a NOT IN that plans as badly as it handles NULL. This page covers the patterns the analyzer checks for, why each one hurts, and where static analysis honestly ends and EXPLAIN begins.
How to read the report
Paste a query into pane 01 and the analyzer parses it into a real syntax tree, walks every predicate in WHERE, JOIN … ON and HAVING, and reports what it finds. Errors are things that change correctness or force a full scan; warnings usually cost an index; info entries are the smaller habits worth knowing about (they sit behind the --minor flag if you want them gone).
The report gets a lot sharper when you also paste your CREATE TABLE and CREATE INDEX statements into pane 02. With the schema present, the analyzer stops guessing: it matches every filter and join column against your actual indexes, applies the leftmost-prefix rule to composites, knows which columns are nullable (which decides whether your NOT IN is merely slow or silently wrong), catches type mismatches between join columns, and writes out ready-to-run CREATE INDEX statements with the column order explained. The dialect is read off the SQL itself, backticks and AUTO_INCREMENT read as MySQL, :: casts and ILIKE as Postgres, and the advice adjusts where the two differ.
One honest caveat before the patterns: everything here is static analysis. It sees the shape of the query, not the size of your tables. A finding on a 200-row lookup table is technically true and practically irrelevant, so weigh the report against what you know about the data.
Sargability: the left side of the =
An index is a sorted structure. It answers "which rows have created_at after X" by jumping to X and reading forward. It cannot answer "which rows have YEAR(created_at) = 2024", because it is sorted by created_at, not by the output of a function applied to it. The database has to compute the function for every row, which means reading every row. Predicates an index can serve are called sargable (search-argument-able), and the rule is short: the column stands alone on its side of the operator, and everything computed happens on the constant side.
| Kills the index | Same rows, index survives |
|---|---|
YEAR(created_at) = 2024 | created_at >= '2024-01-01' AND created_at < '2025-01-01' |
DATE(ts) = '2024-06-01' | ts >= '2024-06-01' AND ts < '2024-06-02' |
price * 2 > 100 | price > 50 |
status = 1 (varchar column) | status = '1' |
LOWER(email) = 'a@b.com' | expression index on LOWER(email), or a case-insensitive collation |
The date rewrites use half-open ranges (>= the start, < the day after) on purpose: BETWEEN '2024-06-01' AND '2024-06-02' includes midnight of June 2nd, and BETWEEN … '2024-06-01 23:59:59' drops the last fraction of a second on timestamp columns. The half-open form has neither problem, on any engine.
The varchar row deserves a second look because it does not look like a function at all. Compare a string column to a number in MySQL and the column, not the literal, is cast: the index dies, and as a bonus '123abc' compares equal to 123. Postgres refuses the comparison outright. Both behaviors are found the moment the analyzer sees the column type in your DDL, which is one of the reasons pane 02 exists. Where a function genuinely belongs in the predicate, the fix is an expression index on exactly that expression: Postgres has had them forever, MySQL since 8.0.13, SQLite since 3.9.
Composite indexes: column order
The single most valuable thing the schema matching does is the leftmost-prefix check, because this is where indexes exist and still do not work. A composite index on (a, b, c) is sorted by a, then b within equal a, then c within equal b. A query filtering only b gets nothing from it: the matching entries are scattered through the whole structure. The Postgres documentation on multicolumn indexes states the constraint precisely, and InnoDB behaves the same way.
The subtler failure is having the right columns in the wrong order. Take WHERE status = 'shipped' AND created_at >= '2024-01-01' with an index on (created_at, status). The index is entered through the range on created_at, and from that point on the entries are no longer grouped by status: every entry in the range is visited and checked. Flip the index to (status, created_at) and the scan jumps directly to the shipped block, then reads one contiguous range. Same two columns, same data, a fraction of the work. Hence the rule the analyzer applies when it writes suggestions: equality columns first, the range or ORDER BY column last. When your DDL contains an index that has the right columns in the wrong order for the pasted query, that gets its own finding, because dropping and recreating an index beats adding a near-duplicate.
A pleasant side effect of getting the order right: if the ORDER BY column is the last index column, the rows come out of the index already sorted and the sort step disappears from the plan entirely, which is what makes ORDER BY created_at DESC LIMIT 50 instant on large tables.
LIKE and the leading wildcard
LIKE 'ann%' is a range scan: everything from ann up to ano. LIKE '%@gmail.com' is not, and cannot be, because a B-tree sorts from the first character. The analyzer flags the leading wildcard and, since "don't do that" is useless advice when the requirement is a contains-search, points at the structures built for it: pg_trgm with a GIN index in Postgres, which genuinely accelerates %term%, FULLTEXT in MySQL, FTS5 in SQLite.
Postgres has an extra trap even for the harmless-looking prefix form: with the usual en_US.UTF-8 or ICU collations, LIKE 'ann%' ignores a plain B-tree index. The index has to be created with text_pattern_ops (or the database initialized with the C collation) before prefix LIKE uses it. The analyzer mentions this exactly when it detects Postgres and a prefix pattern, because it is the kind of fact you otherwise learn from a production incident.
The NOT IN trap
NOT IN (SELECT …) is the rare pattern that is a performance problem and a correctness bug in the same clause. The correctness part: if the subquery returns even one NULL, the predicate returns zero rows total, because x NOT IN (1, NULL) expands to x <> 1 AND x <> NULL, and the second comparison is NULL, never true. No error, no warning, just an empty result that looks like "no matches". With your DDL pasted, the analyzer checks the subquery's column for a NOT NULL constraint and upgrades the finding to an error when it is missing.
The performance part: planners historically struggle to turn NOT IN into an efficient anti-join precisely because of that NULL semantics. NOT EXISTS carries no such baggage, plans as a clean anti-join on both Postgres and MySQL 8, and says what it means. The NULL variant bit us exactly once, which was enough: treat every NOT IN (SELECT …) in a code review as a bug until proven otherwise.
OFFSET and keyset pagination
LIMIT 50 OFFSET 10000 reads 10,050 rows and throws away 10,000 of them, every time the page loads. The cost grows linearly with page depth, and because rows keep being inserted, page boundaries shift between requests and users see duplicates or gaps. Keyset pagination replaces the offset with a filter on the last row the client saw:
| Pattern | Page 200 cost | Stable under inserts | Jump to page N |
|---|---|---|---|
LIMIT 50 OFFSET 9950 | 10,000 rows read | no | yes |
WHERE (created_at, id) < (:last_ts, :last_id) LIMIT 50 | 50 rows read | yes | no |
The id tiebreaker in the tuple matters: created_at alone is rarely unique, and a non-unique sort key makes rows straddle page boundaries. With an index on (created_at, id) the filter and the ORDER BY are both served by one contiguous index range, which is why every page costs the same. The analyzer flags offsets from 1000 up and writes the keyset form with your LIMIT already filled in.
What static analysis cannot see
A static analyzer reads code, not data, and pretending otherwise is how tools in this category overpromise. What it cannot know: how many rows your tables hold, how selective a predicate is (a filter on status is great when 1% of orders are shipped and worthless when 95% are), what the planner's statistics currently claim, whether the working set fits in cache, and what your write load can afford in index maintenance. A suggested index on a table with 500 rows is correct and pointless; the same index on 50 million rows is the difference between milliseconds and minutes.
So the honest division of labor looks like this: fix the structural findings here first, because a query with a function-wrapped predicate or a missing join index produces a bad plan on any data. Then run EXPLAIN ANALYZE on the result against realistic data and compare estimated to actual row counts; a mismatch of orders of magnitude means the statistics are stale (ANALYZE the table) or the predicate is beyond the planner's estimation. The two steps catch different bugs, which is exactly why neither replaces the other. What never needs to happen in either step: your production schema leaving your machine. Everything this page does runs in your browser tab, which you can verify by loading it, disconnecting, and pasting away.
Slow query questions
How do I find out why a SQL query is slow?
Run it with EXPLAIN ANALYZE (Postgres) or EXPLAIN ANALYZE / EXPLAIN FORMAT=JSON (MySQL 8) and read the plan from the innermost node outward: a Seq Scan or full table scan on a large table, a row estimate that is off by orders of magnitude, or a sort that spills to disk are the usual suspects. Before you get to a live database, a static check catches the structural causes in seconds: a function wrapped around an indexed column, a leading wildcard, a missing index on the join column, an OFFSET in the thousands. Fix those first, then let the real plan judge what remains.
Why is my query not using the index?
The five causes that cover most cases: a function or cast on the column side of the predicate (WHERE DATE(created_at) = … is computed per row); a type mismatch, like comparing a varchar column to a number, which casts the column and kills the index; a leading wildcard in LIKE; a composite index whose first column your query does not filter by equality (the leftmost-prefix rule); and a table so small or a predicate so unselective that the planner correctly decides a scan is cheaper. The last one is not a bug, the first four are fixable in the query or the DDL.
Does the order of columns in a composite index matter?
It decides whether the index works at all. A B-tree is sorted by the first column, then the second within it, like a phone book sorted by last name then first name. A query that filters the second column without pinning the first cannot narrow the search. The working rule: columns compared with = go first, the one column compared with a range (>, <, BETWEEN, date ranges) or used in ORDER BY goes last. An index on (created_at, status) and one on (status, created_at) contain the same data and perform completely differently for WHERE status = ? AND created_at > ?.
Is SELECT * bad for performance?
It costs real I/O and it blocks a specific optimization. Every row carries every column across the network and through sorts, hashes and memory buffers, which hurts most when the table has TEXT, BLOB or JSON columns nobody asked for. And a query that names only indexed columns can be answered from the index alone (an index-only scan / covering index); SELECT * forces the trip to the table for every row. In a one-off psql session, star away; in production code, name the columns.
What is a covering index?
An index that contains every column a query needs, so the table itself is never touched. In Postgres you add payload columns with INCLUDE: CREATE INDEX ON orders (customer_id) INCLUDE (total, created_at). In MySQL/InnoDB you put them at the end of the index column list, and the primary key columns are implicitly part of every secondary index anyway. Covering indexes turn a read pattern of index-then-table-per-row into a single sequential index range read; for hot list queries the difference is routinely 10x.
How do I make a LIKE search with a leading wildcard fast?
A B-tree index is sorted from the start of the string, so LIKE '%term%' cannot use it, the same way you cannot find every surname containing "berg" in a phone book without reading it cover to cover. The real fixes are purpose-built indexes: pg_trgm with a GIN index in Postgres (CREATE INDEX ... USING gin (col gin_trgm_ops)) makes %term% queries indexable; MySQL has FULLTEXT indexes with MATCH ... AGAINST; SQLite has FTS5. If you only ever match the end of the value, storing or indexing the reversed string and searching REVERSE with a trailing wildcard works on any engine.
Why is OFFSET pagination slow on high page numbers?
OFFSET does not skip rows, it reads and discards them. Page 500 with 50 rows per page walks 25,000 rows to return 50, so latency grows linearly with page depth, and rows inserted between requests shift the pages so entries repeat or vanish. Keyset pagination (also called seek method or cursor pagination) filters on the last seen sort key instead: WHERE (created_at, id) < (:last_seen_created_at, :last_seen_id) ORDER BY created_at DESC, id DESC LIMIT 50. With a matching index every page costs the same as the first. The trade-off: no jumping to an arbitrary page number.
NOT IN vs NOT EXISTS: which one should I use?
NOT EXISTS, in almost every case. NOT IN has a correctness trap: if the subquery returns a single NULL, the whole predicate evaluates to NULL and the query returns zero rows, silently. x NOT IN (1, NULL) is not true for any x. NOT EXISTS has no such trap, and both Postgres and MySQL 8 plan it as an anti-join, which is also the faster shape. The only safe NOT IN is one over a column with a NOT NULL constraint, and even then you gain nothing by preferring it.
Do foreign keys automatically get an index?
MySQL/InnoDB: yes, declaring a foreign key creates an index on the referencing column if none exists. Postgres: no, and that surprise is one of the most common performance bugs in Postgres schemas. The referenced side (the primary key) is always indexed, but the referencing column is not, so every DELETE on the parent table scans the child table to check the constraint, and every join along the FK does too. If you declare REFERENCES in Postgres, write the CREATE INDEX next to it.
How many indexes are too many on one table?
Every index is a second data structure that every INSERT, and every UPDATE touching an indexed column, has to maintain, so writes pay for reads. There is no magic number, but the working heuristics: an index nobody queries is pure cost (pg_stat_user_indexes and sys.schema_unused_indexes tell you); an index whose columns are the leftmost prefix of another index is redundant and can be dropped; and write-heavy tables (queues, event logs) deserve the most suspicion. Five focused composite indexes beat twelve single-column ones on almost every OLTP table.
What is the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the plan the optimizer intends to use, with estimated row counts and costs, without running the query. EXPLAIN ANALYZE actually executes it and prints the real row counts and timings next to the estimates, which is how you spot the classic killer: an estimate of 3 rows where 300,000 arrive, sending the planner into a nested loop it should never have chosen. Because ANALYZE executes the statement, wrap it in BEGIN … ROLLBACK when the query is an UPDATE or DELETE. MySQL has supported EXPLAIN ANALYZE since 8.0.18.
Is it safe to paste production queries and schemas into an online SQL optimizer?
Most online SQL tools submit your input to a server for analysis, and a production query plus its DDL reveals table names, business logic and sometimes literal customer data in the predicates, which then sits in someone's logs. Check the network tab before pasting anything sensitive, or strip literals first. This analyzer parses and matches everything in JavaScript inside your browser tab; the query and the schema are never transmitted, and it keeps working with the network disconnected.