What the SQL Index Advisor does
This advisor reads the SQL queries you paste and proposes candidate B-tree indexes for them, with the reason for every column's position, the INCLUDE columns that would make the index covering, and the storage and write cost each index adds. It also points out query shapes that no ordinary index can help - a leading-wildcard LIKE, a function wrapped around a column, an OR across different columns.
It is a heuristic, and says so. The rules are the ones database documentation and experienced DBAs apply by hand; the advisor applies them consistently and shows its working, but it has no access to your data, statistics or real plans. Nothing is executed and nothing leaves the page.
How to use it
- Choose the dialect - PostgreSQL, MySQL/MariaDB, SQL Server or SQLite - so the proposed DDL uses the right syntax (for example
CONCURRENTLYorINCLUDE). - Paste one or more SELECT, UPDATE or DELETE statements, separated by semicolons. Joins, subqueries and CTEs are followed.
- Optionally open the DDL section and paste your CREATE TABLE and CREATE INDEX statements, plus an approximate row count. With them the advisor knows which table a bare column belongs to, which columns are booleans or unique, and which indexes already exist.
- Select Suggest indexes and read each candidate: the column order with a reason per column, the warnings, and the trade-off line.
- Before creating anything, run EXPLAIN (or EXPLAIN ANALYZE on a copy) with and without the index on production-like data.
Reading the results
Column order follows one rule above all: equality columns first, then at most one range column. A B-tree can seek on any number of leading equality columns and then scan one range; columns after the range cannot narrow the seek.
When a query sorts or groups and all WHERE columns are equalities, the ORDER BY or GROUP BY columns are appended so rows come out of the index already sorted - which also lets a LIMIT stop early. If the range column is itself the sort column, as in created_at >= ... ORDER BY created_at DESC, it serves both.
INCLUDE columns are carried in the index leaf pages but are not part of the key, so the query can be answered from the index alone. PostgreSQL 11+ and SQL Server support INCLUDE; for MySQL and SQLite they are appended as trailing key columns instead, which is larger but has the same effect.
A candidate marked as already covered means an index you pasted - or a single-column primary key or unique constraint on its leading column - already does the job. Creating another would add write cost for nothing.
Worked example: the order history query
The Order history example asks PostgreSQL for a customer's latest paid orders: WHERE customer_id = $1 AND status = 'paid' AND created_at >= now() - interval '90 days' ORDER BY created_at DESC LIMIT 20, against a 5,000,000-row orders table that already has an index on customer_id alone.
The advisor proposes (customer_id, status, created_at DESC) INCLUDE (id, total). The two equalities lead; created_at is the one range column and also the sort column, so the index returns rows in order and the LIMIT stops after 20. status is flagged as probably low-selectivity, with a partial index WHERE status = 'paid' suggested as an alternative.
The trade-off line estimates 68 bytes per index entry: 8 + 22 + 8 bytes of key (customer_id, status as varchar(20) plus its length header, created_at), 8 + 8 bytes of INCLUDE columns (numeric assumed at 8), plus 14 bytes of entry overhead. At 5,000,000 rows and about 70% page fill that is 68 x 5,000,000 x 1.3 = 442,000,000 bytes, roughly 420 MB - an order-of-magnitude figure, not a measurement.
Formulas and scoring rules
- Estimated index size
size = (sum of column widths + 14) x rows x 1.314 bytes approximates the per-entry header and row pointer; 1.3 allows for pages about 70% full. Variable-length widths are assumed from the declared length or a typical value and marked as assumed.
Queries no index can fix as written
LIKE '%smith' starts with a wildcard, so a B-tree on the column has no prefix to seek on. PostgreSQL's pg_trgm extension with a GIN index, or a full-text index, can help; a plain index cannot. LIKE 'smi%' is fine.
WHERE lower(email) = ... or WHERE date(created_at) = ... wraps the column in a function, and an index on the bare column is no longer usable. Either index the expression itself (PostgreSQL, SQLite and MySQL 8.0.13+ support expression indexes; SQL Server uses a computed column), or rewrite the condition as a range: created_at >= '2026-09-01' AND created_at < '2026-09-02'.
WHERE phone = ? OR email = ? needs two different access paths. One composite index cannot serve both; two single-column indexes (combined by a bitmap or index merge) or a UNION ALL of two queries usually can.
What an index costs
Every INSERT and DELETE writes to every index on the table, and an UPDATE writes to each index containing a changed column. On a table that takes many writes, three well-chosen indexes usually beat eight overlapping ones. The advisor warns when a table would collect three or more new candidates, or six indexes in total.
When two candidates share a leading column list and one is longer, the longer one can usually serve both queries; the shorter is marked as possibly redundant.
Limitations: what the result does not prove
- Selectivity is guessed from the DDL and from column names such as
status,typeoris_*. Real selectivity - how many rows a value matches - lives in the data and the optimiser's statistics, which this page never sees. - It only proposes B-tree indexes. Hash, GIN, GiST, BRIN, full-text, columnstore and partial indexes are mentioned where they fit but not designed for you.
- It reads query text, not workloads. An index that makes one query fast may not be worth it if the query runs once a day and the table takes thousands of writes a second.
- The optimiser may still ignore a candidate - for example when most of the table matches, or when statistics are stale. Only EXPLAIN on real data settles it.
Privacy: where your data goes
Everything you paste, type or drop is processed in this browser tab. It is not uploaded, logged, stored or sent to analytics. Session recording and tag-manager scripts are switched off on this page.
Standards and sources
- PostgreSQL documentation - Multicolumn indexes
- PostgreSQL documentation - Index-only scans and covering indexes
- PostgreSQL documentation - Indexes on expressions
- MySQL 8.4 Reference Manual - Multiple-column indexes
- SQL Server - Create indexes with included columns
- SQLite - The SQLite query optimizer overview
Frequently asked questions
Why should equality columns come before range columns in a composite index?
A B-tree is sorted by its first column, then the second within it, and so on. Equalities pin down one contiguous slice at each level, so any number of them can be used for the seek. The first range column spreads over many values, and the columns after it are no longer in a single sorted run.
What does INCLUDE do in an index definition?
INCLUDE adds columns to the index leaf entries without making them part of the sort key. The query can then read every column it needs from the index and skip the table - an index-only scan in PostgreSQL, a covering index in SQL Server - while the key stays short.
Why is a boolean or status column placed last or flagged?
A column with only a few distinct values narrows the search very little on its own, so an index led by it is rarely chosen. After a selective column it can still help. If most queries ask for one value, such as paid, a partial index filtered on that value is often smaller and better.
Can the advisor tell me which indexes to drop?
Not reliably, because it sees only the queries you paste. It does flag candidates that an existing index already covers and candidates made redundant by a wider one. For unused indexes, check the database's own usage statistics, such as pg_stat_user_indexes or sys.dm_db_index_usage_stats.
Why does LIKE with a leading % not use my index?
The index is ordered by the start of each value, and a pattern such as %smith says nothing about the start, so every entry would have to be checked. Use a trigram or full-text index for contains-style search, or store and index a reversed copy for ends-with searches.
Is the proposed CREATE INDEX statement safe to run in production?
It is syntactically correct for the chosen dialect and uses CONCURRENTLY on PostgreSQL to avoid blocking writes, but building any index on a large table uses I/O, CPU and disk. Test on a copy, check the plan improves, and schedule the build like any other migration.
Which SQL dialects does the index advisor understand?
PostgreSQL, MySQL and MariaDB, SQL Server and SQLite. It handles their quoting styles - double quotes, backticks and square brackets - plus comments and string literals, and writes DDL with each dialect's syntax and INCLUDE support.
Last reviewed by the A2Z.Tools team against the sources listed above.
