Analysis rules
The rules query analysis applies to a query’s plan, its SQL and
the tables it reads. Each one runs on the engines it names; a version after @ is the first it
applies to, and @< the first it no longer does. Words in italics stand for what the finding
names: a table, a column, an index, a number.
A severity says how much it costs: problem for what reads far more than the query needs, warning for what costs more than it should or can go wrong, info for what is fine today and worth knowing.
Every engine
Section titled “Every engine”| Rule | Looks at | Severity | Says |
|---|---|---|---|
full-scan-missing-index | a table read | problem | Reads every row of table to find the ones it wants: no index has columns. |
full-scan-small-table | a table read | info | Reads all rows rows of table: cheap at this size, but no index has columns, so it will read every row as the table grows. |
full-scan-index-skipped | a table read | warning | Reads every row of table although an index leads with columns: the database expects many rows to match, or the condition does not match how the index is built (a cast, a collation, an operator class). |
full-scan-index-skipped-small | a table read | info | Reads all rows rows of table instead of its index on columns: with this few rows that is cheaper, and it will switch to the index as the table grows. |
join-without-index | a table read | problem | Reads table again for every row it is joined with: no index has columns. |
write-scan | a table read | problem | Reads every row of table to find the rows to change: no index has columns. |
full-index-scan | a table read | warning | Reads every entry of the index index on table: none starts with the columns it filters on, columns. |
many-loops | a table read | warning | Read table loops times, once for every row on the other side of the join, without an index. |
rows-thrown-away | a table read | warning | Read removed rows of table only to throw them away, keeping actual. |
misestimated-rows | a table read | warning | Expected estimated rows per loop from table and found actual: a plan built on the wrong count can pick the wrong way to read and join. |
sort-without-index | a sort | warning | Sorts every matching row to return the first ones: an index on columns gives them in order and stops at the limit. |
sort-on-disk | a sort | warning | The sort outgrew its memory and wrote to disk. |
function-on-column | a condition | warning | wrapper on column hides the column from its index: compare the column itself, or index the same expression. |
or-across-columns | a condition | info | OR between column and other columns: an index on one column cannot find the rows of the other side. |
long-in-list | a condition | info | IN with size values on column: each value is its own lookup, and every new length is a new SQL text for the database to plan. |
huge-in-list | a condition | warning | IN with size values on column: lists this long usually come from loading a relation for many parent rows, and they grow with the page. |
equals-null | a condition | problem | column operator NULL is never true: NULL is compared with IS NULL or IS NOT NULL. |
not-in-subquery | a condition | warning | NOT IN with a subquery returns no rows at all when the subquery returns a NULL, and cannot run as an anti join the way NOT EXISTS does. |
deep-offset | the query | warning | Skips offset rows to return the next ones: every page reads all the rows before it. |
random-order | the query | warning | ORDER BY random() reads and sorts every row to return a few. |
write-every-row | the query | warning | Changes every row of table: an updateMany or deleteMany with an empty where. |
redundant-index | an index | info | The index index on table is the start of other, which serves the same queries a little slower: one index fewer to update on every write. |
duplicate-index | an index | warning | The indexes index and other on table have the same columns, order and operator classes: every write updates both. |
buried-column | a table read | warning | Reads every row of table: the index buriedIn has columns, but after leading, and an index is searched from its first column. |
buried-join | a table read | problem | Reads table again for every joined row: the index buriedIn has columns, but after leading, so it cannot look them up. |
paging-without-order | the query | warning | Skips offset rows with no ORDER BY: the rows come in no fixed order, so pages can repeat or miss rows. |
full-scan-broad-filter | a table read | info | Reads every row of table: about share% of them match, so its index on columns would not save reads. |
many-rows | the query | warning | Returned returned rows with no limit: if the page shows fewer, take or a cursor reads only those. Manual |
unstable-order | the query | warning | Pages through table ordered by keys, which several rows can share: rows with the same values can land on either page, so pages can repeat or miss rows. Add a unique column, such as id, last in the order. |
PostgreSQL
Section titled “PostgreSQL”| Rule | Looks at | Severity | Says |
|---|---|---|---|
never-analyzed | a table read | info | table has no statistics yet, so the plan guesses how many rows match, and every choice it makes about indexes is a guess too. Manual |
full-scan-unanalyzed | a table read | warning | Reads every row of table although an index leads with columns: table has no statistics, so the plan cannot tell how few rows match. Manual |
misestimated-unanalyzed | a table read | warning | Expected estimated rows from table and found actual: table has never been analyzed, so the plan counts with default guesses. Manual |
stale-statistics | a table read | warning | Expected estimated rows from table and found actual: modified rows changed since its last ANALYZE, and autovacuum has not caught up. Manual |
stale-statistics-scan | a table read | warning | Reads every row of table although an index leads with columns: modified rows changed since its last ANALYZE, so the plan counts with old numbers. Manual |
correlated-columns | a table read | info | Expected estimated rows from table and found actual: the plan treats related as unrelated and multiplies their odds. Manual |
index-then-filter | a table read | warning | Found rows of table through index, then threw away removed of them on filter: the index holds only part of the condition. Manual |
buried-columnpostgres@<18 | a table read | warning | Reads every row of table: the index buriedIn has columns, but after leading, and PostgreSQL searches an index from its first column. Manual |
buried-columnpostgres@18 | a table read | warning | Reads every row of table: the index buriedIn has columns after leading, and PostgreSQL 18 skips through such an index only when leading has few distinct values. Manual |
buried-join | a table read | problem | Reads table again for every joined row: the index buriedIn has columns, but after leading, so it cannot look them up. Manual |
index-only-heap-visits | a table read | info | The index-only read of table still checked the table heapFetches times: rows on pages changed since the last VACUUM have to be confirmed there. Manual |
hash-on-disk | a table read | warning | Hashed table for the join in batches batches: the hash table did not fit in work_mem × hash_mem_multiplier, so part of it went to disk. Manual |
planned-without-values | a table read | info | Planned without the parameters' real values, so this full read of table may not be the plan the app gets: the values decide whether the index on columns pays. Manual |
prefix-needs-pattern-ops | a condition | warning | LIKE with a fixed start on column could read a range of leadingIndex, but the column sorts by a language's rules: only an index with text_pattern_ops, or the C collation, serves LIKE. Manual |
full-scan-needs-pattern-ops | a table read | warning | Reads every row of table for a LIKE with a fixed start on like: the column sorts by a language's rules, so only an index with operatorClass, or the C collation, serves LIKE. Manual |
full-scan-pattern-ops-index | a table read | warning | Reads every row of table although misfit leads with misfitColumn: its operator class, misfitClass, serves = and LIKE but not <, <=, >, >= or ORDER BY, which need an index with the default operator class as well. Manual |
full-scan-collation-index | a table read | warning | Reads every row of table although misfit leads with misfitColumn: the index holds it in the collation misfitCollation, and the query compares it in the column's, columnCollation. An index serves =, <, > and ORDER BY only in the collation the query compares in. Manual |
sort-pattern-ops-index | a sort | warning | Sorts every matching row to return the first ones: misfit holds misfitColumn with misfitClass, which has no <, the operator ORDER BY sorts with, so the index cannot give the rows in order. An index with the default operator class gives them in order and stops at the limit. Manual |
sort-collation-index | a sort | warning | Sorts every matching row to return the first ones: misfit holds misfitColumn in the collation misfitCollation, not the column's columnCollation, so it cannot give the rows in the column's order. An index in the column's collation gives them in order and stops at the limit. Manual |
leading-wildcard | a condition | warning | LIKE with % first on column: a B-tree index only finds a fixed start, so every row is read. Manual |
insensitive-match | a condition | warning | ILIKE on column, which Prisma sends for mode: "insensitive": a B-tree index serves it only when the pattern starts with a character that has no case, so every row is read. Manual |
short-pattern | a condition | info | The pattern on column has no run of three characters, so even a trigram index has nothing to look up and reads all of itself. Manual |
not-equal | a condition | info | column operator …: a B-tree index finds rows by the values they have, not by the ones they lack, so this condition does not narrow the read. Manual |
partial-index-parameter | a condition | warning | column is compared with a parameter, and the partial index index (WHERE predicate) applies only when the planner sees the value: a reused generic plan cannot use it. Manual |
not-in-null | a condition | problem | NOT IN with a NULL among its values on column is never true, so the query returns no rows. Manual |
count-every-row | the query | info | Counts all rows rows of table: PostgreSQL reads the whole table, or a whole index, on every call. Manual |
jit-on-short-query | the query | warning | JIT compiled the query for jitMs ms of its executionMs ms: the estimated cost passed jit_above_cost, but the query is too short to win the time back. Manual |
planning-heavy | the query | info | Planning took planningMs ms and running executionMs ms: the planner redoes that work every time this query gets a new plan. Manual |
foreign-key-without-index | a foreign key | warning | table.columns references references with no index: PostgreSQL does not create one, so a join on it, or a delete in references, reads all of table. Manual |
group-on-disk | a grouping | warning | The grouping outgrew work_mem × hash_mem_multiplier and wrote to disk. Manual |
invalid-index | an index | problem | The index index on table is invalid: queries ignore it, but every write still updates it. Manual |
| Rule | Looks at | Severity | Says |
|---|---|---|---|
join-buffer | a table read | warning | Reads all of table to join it, matching its rows against the earlier tables' rows held in a join buffer: an index on columns would look up only the rows each joined row needs. Manual |
join-buffer-small | a table read | info | Joins table through a join buffer, reading all rows of its rows: cheap at this size, but with no index on columns it reads the whole table on every run as it grows. Manual |
index-considered-not-used | a table read | warning | MySQL considered the indexes possible for table but read every row: it expects most rows to match, or its statistics are out of date. Manual |
write-index-scan | a table read | warning | Reads every entry of the index index on table to find the rows to change, and InnoDB locks each entry it reads until the transaction ends. Manual |
write-locks-every-row | a table read | info | Reads all rows rows of table to find the rows to change, and InnoDB locks every one until the transaction ends, which blocks inserts by others meanwhile: an index on columns locks only the rows it needs. Manual |
invisible-index-would-serve | a table read | warning | The invisible index hidden on table leads with a column this read needs: make it visible instead of adding another index. Manual |
dependent-subquery-scan | a table read | problem | Reads every row of table inside a correlated subquery, which MySQL runs again for each row of the outer query: an index on columns makes each run a lookup. Manual |
dependent-subquery-scan-small | a table read | info | Reads all rows rows of table inside a correlated subquery, again for each row of the outer query: cheap at this size, but an index on columns keeps each run a lookup as the table grows. Manual |
dependent-subquery-loops | a table read | info | Ran the correlated subquery on table loops times, once for each row of the outer query: each run is an index lookup, but there are as many runs as outer rows. Manual |
range-before-equality | a table read | warning | Looks up table through index by used only: the range on last is as far as the index can search, so skipped is checked on each entry the range reads. An index on columns, with the equalities first, finds the rows directly. Manual |
leading-wildcard | a condition | warning | LIKE with % first on column: a B-tree index cannot seek to the rows, so MySQL reads every row unless another condition narrows them first. Manual |
number-on-string | a condition | warning | column is text compared with a number: MySQL converts every row's text to a number to compare them, so its index cannot be used. Pass the value as a string. Manual |
temporary-sort | a sort | info | Sorts through a temporary table: an index on columns gives the rows in order. Manual |
temporary-group | a grouping | info | Groups through a temporary table, built for every run. Manual |
group-without-index | a grouping | info | Groups table through a temporary table, built for every run: an index on columns lets MySQL read the rows already grouped. Manual |
impossible-condition | the query | info | reason: MySQL found from the constants alone that no row can match, so the query returns nothing. Manual |
invisible-index | an index | info | The index index on table is invisible: MySQL keeps it up to date on every write but never uses it. Make it visible, or drop it. Manual |
MariaDB
Section titled “MariaDB”| Rule | Looks at | Severity | Says |
|---|---|---|---|
join-buffer-scan | a table read | problem | Joins table through a join buffer: no index has columns, so MariaDB reads all of table again for every bufferful of rows from the other side. Manual |
join-buffer-scan-small | a table read | info | Joins table through a join buffer, reading all rows rows for every bufferful of rows from the other side: cheap at this size, but no index has columns. Manual |
join-buffer-index-unused | a table read | warning | Joins table through a join buffer although an index leads with columns: the joined columns usually differ in type or character set, so MariaDB cannot look one up in an index of the other. Manual |
join-index-unused | a table read | warning | Reads table again for every joined row although an index leads with columns: the joined columns differ in type or collation, or the statistics on table are out of date. Manual |
full-scan-index-considered | a table read | warning | Reads every row of table although MariaDB could use possible: it expects most rows to match, or its statistics are out of date. EXPLAIN with FORCE INDEX (possible) shows the plan with the index. Manual |
write-index-scan | a table read | problem | Reads every entry of the index index on table to find the rows to change: no index starts with columns, and InnoDB locks the entries it reads until the transaction ends. Manual |
write-scan-small | a table read | info | Reads all rows rows of table to find the rows to change: cheap at this size, but no index has columns, and InnoDB locks the rows it reads until the transaction ends. Manual |
write-scan-index-skipped | a table read | warning | Reads every row of table to find the rows to change although an index leads with columns, and InnoDB locks the rows it reads: the statistics on table may be out of date. Manual |
filesort | a sort | info | Sorts every matching row in a filesort: an index on columns gives them in order. Manual |
temporary-sort | a sort | info | Sorts through a temporary table: an index on columns gives the rows in order. Manual |
temporary-group | a grouping | info | Groups through a temporary table, built for every run. |
ignored-index-would-serve | a table read | warning | The IGNORED index hidden on table leads with a column this read needs: set it NOT IGNORED instead of adding another index. Manual |
leading-wildcard | a condition | warning | LIKE with % first on column: an index cannot find the rows, so every row is read. Manual |
number-on-string | a condition | warning | column is text compared with a number: MariaDB converts every row's text to a number to compare them, so its index cannot be used. Pass the value as a string. Manual |
impossible-condition | the query | info | reason: MariaDB found from the constants alone that no row can match, so the query returns nothing. |
ignored-index | an index | info | The index index on table is IGNORED: MariaDB updates it on every write but never uses it. Drop it, or set it NOT IGNORED if a query needs it. Manual |
SQLite
Section titled “SQLite”| Rule | Looks at | Severity | Says |
|---|---|---|---|
automatic-index | a table read | problem | SQLite builds a temporary index on table for columns every time this runs, then throws it away: a lasting index saves that work. Manual |
automatic-index-small | a table read | info | SQLite builds a temporary index on table for columns on every run: cheap at rows rows, but rebuilt each time and slower as the table grows. Manual |
automatic-index-on-subquery | a table read | info | SQLite builds a temporary index on table, a subquery or view, every time this runs: a subquery cannot keep an index, so join or filter the table it reads instead. Manual |
index-unusable | a table read | warning | Reads every row of table although an index starts with columns: SQLite uses an index whenever the condition lets it, so the condition is what stops it. LIKE needs an index with COLLATE NOCASE, and a different COLLATE or column type also stops it. Manual |
index-unusable-small | a table read | info | Reads all rows rows of table although an index starts with columns: the condition stops SQLite from using it, so it will keep reading every row as the table grows. Manual |
index-not-chosen | a table read | warning | Reads every row of table instead of its index on columns: the statistics in sqlite_stat1 say the index helps little. Statistics gathered on a small development database say that of most indexes. Manual |
index-not-chosen-small | a table read | info | Reads all rows rows of table instead of its index on columns: sqlite_stat1 says the table is small, and reading it all is cheaper at that size. Manual |
never-analyzed | a table read | info | table has no statistics (sqlite_stat1 has no row for it), so SQLite assumes every table is large when it picks an index. Manual |
correlated-scan | a table read | problem | Reads every row of table again for each row of the outer query, inside a correlated subquery: an index on columns finds them directly. Manual |
correlated-scan-small | a table read | info | Reads all rows rows of table again for each row of the outer query, inside a correlated subquery: cheap at this size, but the work grows with both tables. An index on columns finds them directly. Manual |
foreign-key-check-scan | a table read | problem | Each row this deletes or re-keys in references makes SQLite look for the rows of table that reference it, reading all of table each time: an index on columns finds them directly. Manual |
foreign-key-without-index | a foreign key | warning | table.columns references references with no index: SQLite does not create one, so loading table through the relation reads all of it, and so does each delete in references while foreign keys are on. Manual |
foreign-keys-off | a foreign key | warning | Foreign keys are off on this connection, so SQLite enforces none of them: a delete or a key change in a parent table runs no ON DELETE or ON UPDATE action and is not stopped by the rows that reference it, and a row can point at a parent that does not exist. Prisma's better-sqlite3 and libSQL adapters start with them on, so something ran PRAGMA foreign_keys = OFF on this client, and they stay off for its connection until PRAGMA foreign_keys = ON. Manual |
like-needs-nocase-index | a table read | warning | LIKE on like cannot use an index: LIKE ignores case, so SQLite only uses an index built with COLLATE NOCASE. Manual |
leading-wildcard | a condition | warning | LIKE with a wildcard first on column: no index can find the rows, so SQLite reads every row. Manual |
temporary-sort | a sort | info | Sorts the rows in a temporary b-tree: an index on columns gives them already in order. Manual |
temporary-group | a grouping | info | Groups or removes duplicates in a temporary b-tree, built on every run. Manual |
or-across-columns | a condition | info | OR between column and other columns: SQLite searches each side with its own index only when every side has one. Manual |
not-in-subquery | a condition | warning | NOT IN with a subquery returns no rows when the subquery returns a NULL: NOT EXISTS has no such trap. Manual |