| Index types |
|---|
| Balanced tree for equality, range, and sorted scans. Default for most databases. | Use for =, <, >, BETWEEN, ORDER BY. Skip for full-text search. |
| Index whose leaf level is the table itself. Rows are stored in key order. One per table. | InnoDB clusters on the primary key. SQL Server tables without one are heaps. |
| Separate structure holding keys plus a pointer back to the base table. Many per table. | On a heap it stores a row ID. On a clustered table it stores the clustering key. |
| Hash table for equality lookups only. Constant-time exact match. | Use only for = operations; ranges and ordering need B-tree. SQL Server memory-optimized hash needs a bucket count near the row count. |
| Generalized Inverted Index. Maps values to rows. Built for arrays and full-text. | Use for jsonb, arrays, tsvector, trigram search. Slower to build than B-tree. |
| Generalized Search Tree. Extensible for non-scalar types and overlap queries. | Use for geometric ranges, kNN, exclusion constraints. pg_trgm speeds LIKE. |
| Space-partitioned GiST. Splits data into non-overlapping regions via radix or quad trees. | Use for points, IP prefixes, and prefix matching. Not for overlap queries; use GiST. |
| Block Range Index. Stores min/max per block range. Tiny footprint. | Use on large, physically sorted tables. Cheap to build, weak selectivity. |
| Index stored as column segments instead of rows. High compression, batch execution. | Use for analytic scans over many rows and few columns. Single-row lookups are slow. |
| Dedicated inverted word index in MySQL and SQL Server. Built per text column. | Use MATCH AGAINST or CONTAINS. Postgres full-text uses GIN on tsvector instead. |
| Oracle index storing one bitmap per distinct value. Each bit marks one row. | Use on low-cardinality columns in read-heavy warehouses. Concurrent writes serialize per bitmap. |
| Index column declared DESC. Keys for that column are stored in reverse order. | Needed for mixed sorts like (a ASC, b DESC). Plain DESC sorts can also read a normal index backwards. |
| MySQL 8 index the optimizer ignores. Still maintained on every write. | Hide an index before dropping to test the impact. Make it visible again without a rebuild. |
| Design |
|---|
| Multi-column index. Leading columns must match query filter order. | Order by equality first, then range. Two single indexes cannot replace one composite. |
| Index on a WHERE predicate. SQL Server calls it a filtered index. Covers only matching rows. | Index active='t' rows only. Halves size and write cost. |
| B-tree with extra non-key columns appended. Index-only scans avoid heap lookups. | Add frequently fetched columns via INCLUDE. Keeps the index sortable. |
| Index that enforces no duplicate values. Rejects inserts that collide. | Use for natural keys and one-to-one constraints. UNIQUE NULL allows many NULLs. |
| Index built on a function of columns rather than the columns themselves. | Index lower(email) or date_trunc('day', created_at). The query must repeat the exact expression. |
| Operator class. Defines how an index compares and stores one column's type. | Set varchar_pattern_ops for LIKE 'abc%' without case folding. Defaults suit equality and ranges. |
| Plan that answers a query from index pages alone. The heap is never read. | Needs every fetched column in the index or INCLUDE list. Vacuum keeps the visibility map current. |
| Foreign key columns get no automatic index on the referencing table. | Index every FK column you join or delete through. Cascade deletes scan the child table without one. |
| Maintenance |
|---|
| Plan inspector. Shows sequential scans, index choices, cost estimates. | Use EXPLAIN (ANALYZE, BUFFERS). A Seq Scan on a 10M-row table means missing index. |
| Samples table columns to build planner statistics. Histograms drive index choice. | Run after bulk loads. Stale stats cause seq scans on indexed columns. |
| Each index adds write overhead. Inserts, updates, deletes pay per index. | Drop unused indexes. Track usage in pg_stat_user_indexes. |
| Postgres view of per-index usage. Counts scans and read tuples since stats reset. | Filter for idx_scan = 0 to find unused indexes. Reset counters with pg_stat_reset(). |
| Builds an index without blocking reads or writes. Needs two table scans. | Cannot run inside a transaction. A failed build leaves an INVALID index to drop. |
| Removes an index and frees its storage. Takes an exclusive lock by default. | Use DROP INDEX CONCURRENTLY on busy tables. Check usage stats before removing. |
| Rebuilds a bloated index. Reclaims space from dead tuples. | Use REINDEX CONCURRENTLY in production. Locks block writes without it. |
| Dead tuples and page gaps that accumulate in MVCC tables and their indexes. | Measure with pgstattuple. Vacuum often; REINDEX when dead space dominates. |
| Percentage of a page packed at write time. Free space absorbs future updates. | Lower it to 70-90 on hot-update tables. Full pages force page splits. |
| Rewrites the table physically ordered by an index. One-time operation. | Pairs with BRIN for sorted blocks. Order is not maintained on future writes. |