A worked example, rendered from real sample data. Sign in to run the tool on your own input.
CREATE TABLE users (
id BIGINT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
team_id BIGINT NOT NULL,
status VARCHAR(16) NOT NULL,
created_at TIMESTAMP NOT NULL
);
SELECT id, email FROM users WHERE team_id = 42 AND status = 'active' ORDER BY created_at DESC LIMIT 50;═══ What this tool did ═══
ℹ Every recommendation below comes from the SQL you pasted. This page runs offline and makes no outbound network calls. — no database was connected.
✗ Index sizes, row counts, cardinality and selectivity are NOT estimated here. Those depend on your data; the commands at the end get them from your server.
Dialect: PostgreSQL
Parsed: 1 table definition, 0 existing indexes, 1 query
═══ Schema read from your DDL ═══
─── users ───
Columns: 5
Primary key: id
═══ Recommendations from your queries (1) ═══
─── Query 1 · SELECT on users ───
SELECT id, email FROM users WHERE team_id = 42 AND status = 'active' ORDER BY created_at DESC LIMIT 50
CREATE INDEX idx_users_team_id_status_created_at ON users (team_id, status, created_at) INCLUDE (id, email);
• Column order: equality first (team_id, status), then the sort columns (created_at) — the equality-range-sort rule.
• The INCLUDE columns make this a covering index, so the query never touches the heap.
═══ Measure it on your server (PostgreSQL) ═══
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <your query>;
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';
SELECT relname, seq_scan, idx_scan, n_live_tup FROM pg_stat_user_tables WHERE relname = 'users';
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0; -- indexes nobody uses
SELECT pg_size_pretty(pg_total_relation_size('users')); -- real size, not an estimate
CREATE INDEX CONCURRENTLY … -- avoids the write lock on a live table
ℹ Add one index at a time and re-run EXPLAIN. Every index slows down every
…
Derive CREATE INDEX recommendations from your pasted DDL and queries, with column order, redundancy and covering-index advice. Part of the DevTools Surf developer suite. Browse more tools in the Database Tools collection.