Database Index Strategy Advisor

A database index is a data structure (usually a B-tree) that stores a sorted copy of column values alongside pointers to the full rows. Indexes allow the DB engine to find rows without scanning the entire table, reducing query time from O(n) to O(log n). Without indexes on WHERE, JOIN, and ORDER BY columns, large tables suffer full table scans.

B-tree is the default index type in PostgreSQL and MySQL, suitable for equality (=), range (<, >, BETWEEN), and ORDER BY operations on any data type with a natural sort order. Use B-tree for most columns. It handles about 95% of indexing needs efficiently.

GIN (Generalized Inverted Index) is optimal for array containment, JSONB operators (@>, ?, ?|), and full-text search (tsvector). GiST (Generalized Search Tree) is ideal for geometric/PostGIS data, range types, and full-text. Use GIN for JSON and full-text, GiST for spatial and range queries.

A partial index includes only rows matching a WHERE condition, e.g., CREATE INDEX idx ON orders(user_id) WHERE status = 'pending'. This creates a much smaller, faster index for queries that always filter on that condition. Great for soft-deleted records, status filters, or active-only queries.

In a composite index (col1, col2, col3), the leftmost prefix rule applies: the index can be used for queries filtering on col1, col1+col2, or col1+col2+col3. Queries filtering only on col2 or col3 cannot use the index. Put high-cardinality equality columns first, then range columns, then sort columns.