Indexing in DBMS — Missing Foreign Key Broke Checkout

Without an index, your database degrades to a full table scan on every query—reading millions of rows just to find ten. That’s why indexing is the single most impactful performance lever you control. Get the wrong index or none at all, and your users will feel the delay; pick the right one, and your queries will stay sub‑millisecond even as data grows.

Why Indexing Is Not Optional

An index is a separate data structure — typically a B+ tree — that maps column values to row locations, enabling O(log n) lookups instead of O(n) full table scans. Without it, every query degenerates into a sequential read of all pages, which is the single most common cause of production latency. The core mechanic is simple: trade write overhead for read speed by maintaining a sorted or hashed copy of a subset of columns.

In practice, a B+ tree index stores key-pointer pairs in leaf nodes linked as a linked list, allowing both point lookups and range scans in logarithmic time. The index is physically separate from the heap or clustered table, so a query that uses an index performs two I/O operations: one to traverse the tree, another to fetch the row. The selectivity of the indexed column determines whether the optimizer will use it — a cardinality below 1% of total rows is the typical threshold.

Use indexes on columns that appear in WHERE, JOIN, ORDER BY, or GROUP BY clauses, especially foreign keys. The canonical failure is a missing foreign key index causing a cascading full table scan on every join — exactly what broke checkout in the motivating example. Every foreign key column should be indexed unless you have measured and proven otherwise.

How a Database Actually Finds Data Without an Index

Before we talk about indexes, we need to understand what they replace. When you run a query like SELECT * FROM orders WHERE customer_id = 4821, the database has two options: scan every row in the table checking each customer_id one by one, or consult an index to jump directly to the answer.

The first option is a Full Table Scan. It sounds terrible, but for a table with 500 rows it's actually fine — the overhead of an index lookup can even be slower for tiny datasets. The problem explodes when your orders table has 50 million rows and you're running that query 10,000 times per second.

Disk I/O is the bottleneck. Data lives in fixed-size blocks (usually 8KB or 16KB) on disk. A full scan means reading every block. An index is designed so that finding a specific value requires reading only a handful of blocks — typically O(log n) reads for a B-Tree index — regardless of whether the table has 1,000 rows or 1 billion.

This is the core contract: indexes trade extra disk space and slower writes for dramatically faster reads. That trade-off is the single most important idea in this entire article.

-- ============================================================
-- Demonstrate the cost difference between a full scan
-- and an index seek on a large orders table.
-- Run EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to see the plan.
-- ============================================================

-- Step 1: Create a realistic orders table
CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_id   INT            NOT NULL,
    product_name  VARCHAR(200)   NOT NULL,
    order_total   DECIMAL(10,2)  NOT NULL,
    created_at    TIMESTAMP      NOT NULL DEFAULT NOW()
);

-- Step 2: Seed 1 million rows (simulate a production-sized table)
INSERT INTO orders (customer_id, product_name, order_total, created_at)
SELECT
    (RANDOM() * 100000)::INT,              -- 100k unique customers
    'Product_' || (RANDOM() * 500)::INT,   -- 500 unique products
    (RANDOM() * 500 + 5)::DECIMAL(10,2),   -- order value $5 - $505
    NOW() - (RANDOM() * INTERVAL '2 years') -- orders from last 2 years
FROM generate_series(1, 1000000);

-- Step 3: Query WITHOUT an index — watch the execution plan
EXPLAIN ANALYZE
SELECT order_id, product_name, order_total
FROM   orders
WHERE  customer_id = 4821;
-- Expected plan: "Seq Scan on orders" — reads ALL 1M rows
-- Typical cost on spinning disk: 800ms - 3000ms

-- Step 4: Create a B-Tree index on customer_id
CREATE INDEX idx_orders_customer_id
    ON orders (customer_id);
-- This builds the index structure — one-time cost, usually seconds

-- Step 5: Run the SAME query — the planner now uses the index
EXPLAIN ANALYZE
SELECT order_id, product_name, order_total
FROM   orders
WHERE  customer_id = 4821;
-- Expected plan: "Index Scan using idx_orders_customer_id"
-- Typical cost: 0.5ms - 3ms — a 1000x improvement

B-Tree vs Hash Indexes — Picking the Right Tool for the Job

There isn't just one type of index. The right choice depends entirely on the kind of queries you run.

B-Tree (Balanced Tree) is the default in every major RDBMS — PostgreSQL, MySQL, Oracle, SQL Server. It stores index entries in a sorted tree structure. The sorted order means it handles equality lookups (=), range queries (BETWEEN, <, >), and ORDER BY efficiently. 90% of the time, B-Tree is what you want.

Hash Index stores a hash of the column value mapped to a row pointer. Hash lookups are O(1) — theoretically faster than B-Tree's O(log n) for equality checks. The catch: hash indexes are useless for range queries. WHERE price > 100 on a hash index results in a full scan because hashed values have no sorted relationship to each other.

Bitmap Index (Oracle, PostgreSQL partial support) is ideal for low-cardinality columns — columns with very few distinct values like status (active/inactive) or country_code. It stores a bit array per distinct value and uses bitwise AND/OR to combine multiple conditions. Never use bitmap indexes on high-cardinality columns like email — the storage overhead becomes catastrophic.

Choosing wrong here is a real interview red flag.

-- ============================================================
-- Compare B-Tree vs Hash index behaviour on a products table.
-- PostgreSQL syntax — MySQL uses ENGINE=MEMORY for Hash indexes.
-- ============================================================

CREATE TABLE products (
    product_id    SERIAL PRIMARY KEY,
    sku           VARCHAR(50)    NOT NULL,   -- unique product code
    category      VARCHAR(100)   NOT NULL,   -- e.g. 'electronics'
    price         DECIMAL(10,2)  NOT NULL,
    in_stock      BOOLEAN        NOT NULL DEFAULT TRUE
);

-- ── B-TREE INDEX ─────────────────────────────────────────────
-- Best for: equality, range queries, ORDER BY, LIKE 'prefix%'
CREATE INDEX idx_products_price_btree
    ON products USING BTREE (price);

-- This query USES the B-Tree index efficiently (range query)
EXPLAIN SELECT product_id, sku
FROM   products
WHERE  price BETWEEN 50.00 AND 150.00;  -- range scan ✓

-- ── HASH INDEX ───────────────────────────────────────────────
-- Best for: exact equality lookups ONLY — useless for ranges
CREATE INDEX idx_products_sku_hash
    ON products USING HASH (sku);

-- This query USES the Hash index efficiently (exact match)
EXPLAIN SELECT product_id, price
FROM   products
WHERE  sku = 'ELEC-4K-TV-001';          -- equality lookup ✓

-- This query CANNOT use the Hash index — falls back to seq scan
EXPLAIN SELECT product_id, price
FROM   products
WHERE  sku LIKE 'ELEC%';               -- range/pattern — hash fails ✗

-- ── PARTIAL INDEX (a B-Tree power move) ─────────────────────
-- Index ONLY the rows you actually query — smaller, faster index
CREATE INDEX idx_products_instock_price
    ON products (price)
    WHERE in_stock = TRUE;              -- only index in-stock items
-- Result: index is a fraction of the size but covers the hot query path

Clustered vs Non-Clustered Indexes — The One That Actually Moves Your Data

This distinction trips people up in interviews constantly, so let's nail it.

A Clustered Index physically reorders the rows on disk to match the index order. Because the data itself IS the index, there can only ever be one clustered index per table. In MySQL InnoDB, the PRIMARY KEY is always the clustered index. SQL Server lets you choose which column to cluster on. When you read data via the clustered index, you get the actual row immediately — no second lookup needed.

A Non-Clustered Index is a completely separate structure that stores the indexed column values and a pointer back to the actual row (called a Row Identifier or RID in SQL Server, or the primary key in InnoDB). When the query engine uses a non-clustered index, it first finds the matching pointer in the index, then does a second lookup in the main table to fetch the remaining columns. This second lookup is called a Key Lookup or Bookmark Lookup and shows up in execution plans.

The practical implication: if a non-clustered index query needs many columns beyond what's in the index, those Key Lookups add up. The fix is a Covering Index — an index that includes all the columns a query needs, so the engine never has to touch the main table at all.

Covering indexes are one of the highest-impact performance optimisations you can make with zero application code changes.

-- ============================================================
-- Illustrate clustered index layout vs non-clustered,
-- and the power of a COVERING index to eliminate key lookups.
-- SQL Server syntax — concepts identical in MySQL/PostgreSQL.
-- ============================================================

CREATE TABLE employees (
    employee_id   INT           NOT NULL IDENTITY(1,1),
    department_id INT           NOT NULL,
    last_name     VARCHAR(100)  NOT NULL,
    first_name    VARCHAR(100)  NOT NULL,
    salary        DECIMAL(10,2) NOT NULL,
    hire_date     DATE          NOT NULL,

    -- PRIMARY KEY creates the CLUSTERED index by default in SQL Server
    -- Rows are physically sorted on disk by employee_id
    CONSTRAINT PK_employees PRIMARY KEY CLUSTERED (employee_id)
);

-- Non-clustered index on department_id
-- Stores: department_id → employee_id (pointer back to clustered index)
CREATE NONCLUSTERED INDEX idx_employees_department
    ON employees (department_id);

-- ── SCENARIO 1: Non-clustered causes a Key Lookup ────────────
-- The index has department_id but NOT salary or last_name.
-- Engine finds the pointer via the index, then looks up the main
-- table again for each matching row — expensive at scale.
SELECT employee_id, last_name, salary
FROM   employees
WHERE  department_id = 7;
-- Execution plan shows: Key Lookup (clustered) — one extra disk read per row

-- ── SCENARIO 2: Covering index eliminates the Key Lookup ─────
-- Include all columns the query needs directly in the index.
-- The INCLUDE clause adds columns to the leaf level of the B-Tree
-- without making them part of the sort key.
CREATE NONCLUSTERED INDEX idx_employees_dept_covering
    ON employees (department_id)          -- the WHERE / JOIN column
    INCLUDE (last_name, salary);           -- columns needed in SELECT

-- Now the SAME query never touches the main table at all
SELECT employee_id, last_name, salary
FROM   employees
WHERE  department_id = 7;
-- Execution plan shows: Index Only Scan — all data in the index ✓

-- ── PRO MOVE: Composite index column ORDER matters ────────────
-- For a query filtering on department_id AND sorting by hire_date:
CREATE NONCLUSTERED INDEX idx_employees_dept_hire
    ON employees (department_id, hire_date);  -- filter col first, sort col second
-- Putting hire_date first would make the department filter less selective

When NOT to Add an Index — The Write Performance Tax

Here's what nobody tells beginners: indexes are never free. Every index you add is a liability on write-heavy tables, and getting this wrong in production is painful.

Every time you INSERT, UPDATE, or DELETE a row, the database must update every index on that table to keep it consistent. A table with 8 indexes doesn't just write once — it performs up to 9 writes per operation. On a table receiving 10,000 inserts per second, that's 80,000 extra I/O operations per second before you've even handled reads.

The situation gets worse with UPDATE. If you update an indexed column, the database must delete the old index entry and insert a new one — two write operations per index, per updated row.

The right mental model is to think of each index as a maintenance contract. The database pays that contract on every write, forever. You only justify that contract when read performance gains outweigh the write penalty.

Rules of thumb from the trenches: - Tables with > 80% writes and < 20% reads: index sparingly, only the absolute hottest read paths. - Tables with > 80% reads: index aggressively, your queries will thank you. - Event/audit log tables: almost never index beyond the primary key — they're append-only and rarely read column-by-column. - Foreign key columns: almost always index these — they're hit on every JOIN.

-- ============================================================
-- Demonstrate the measurable write cost of indexes on a
-- high-throughput event_log table (append-only pattern).
-- ============================================================

-- Version A: Over-indexed event log (common beginner mistake)
CREATE TABLE event_log_over_indexed (
    event_id      BIGSERIAL PRIMARY KEY,
    user_id       BIGINT        NOT NULL,
    session_id    UUID          NOT NULL,
    event_type    VARCHAR(100)  NOT NULL,
    page_url      TEXT          NOT NULL,
    metadata      JSONB,
    created_at    TIMESTAMP     NOT NULL DEFAULT NOW()
);
-- Beginner adds indexes on every column "just in case"
CREATE INDEX idx_event_user    ON event_log_over_indexed (user_id);
CREATE INDEX idx_event_session ON event_log_over_indexed (session_id);
CREATE INDEX idx_event_type    ON event_log_over_indexed (event_type);
CREATE INDEX idx_event_url     ON event_log_over_indexed (page_url);
CREATE INDEX idx_event_time    ON event_log_over_indexed (created_at);
-- 6 total structures to update on every INSERT — silent write killer

-- Version B: Lean, thoughtful indexing
CREATE TABLE event_log_lean (
    event_id      BIGSERIAL PRIMARY KEY,
    user_id       BIGINT        NOT NULL,
    session_id    UUID          NOT NULL,
    event_type    VARCHAR(100)  NOT NULL,
    page_url      TEXT          NOT NULL,
    metadata      JSONB,
    created_at    TIMESTAMP     NOT NULL DEFAULT NOW()
);
-- Only index what your actual queries need:
-- Hot query 1: "Show me all events for user X in the last 7 days"
CREATE INDEX idx_lean_user_time
    ON event_log_lean (user_id, created_at DESC);
-- Hot query 2: "Count events by type in the last hour"
CREATE INDEX idx_lean_type_time
    ON event_log_lean (event_type, created_at DESC);
-- Result: 3 structures updated per INSERT vs 6 — 50% write reduction

-- ── Measure index bloat on existing tables (PostgreSQL) ──────
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan   AS times_used,    -- indexes with 0 here are deadweight
    idx_tup_read,
    idx_tup_fetch
FROM   pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;
-- Any index with idx_scan = 0 is costing you writes and giving nothing back

Composite Indexes — Column Order Makes or Breaks Performance

A composite index spans multiple columns. The order of those columns determines which queries it can serve efficiently. This is one of the most misunderstood concepts in indexing.

The Leading Column Rule: The index can be used for queries that filter on the leading column alone, or on the leading column plus any subsequent columns. It cannot efficiently support a query that skips the leading column — that forces a full index scan or full table scan.

Selector First Rule: Put the column with the highest selectivity (most unique values) first. If department_id has 100 distinct values and hire_date has 10,000, then hire_date is more selective. But if queries always filter on department_id first, keep it leading.

The real-world strategy: List all your hot queries, group them by filter columns, then design composite indexes that cover multiple related queries by varying the column order. One index cannot serve all; you often need two or three well-placed indexes rather than fifteen scattered ones.

Covering indexes (using INCLUDE) can add extra columns without affecting the sort order, preserving the selectivity of the leading columns.

-- ============================================================
-- Show how column order in a composite index affects query plans.
-- Example: an orders table with department and order_date.
-- ============================================================

CREATE TABLE orders100k (
    order_id       SERIAL PRIMARY KEY,
    department_id  INT NOT NULL,
    order_date     DATE NOT NULL,
    amount         DECIMAL(10,2),
    status         VARCHAR(20)
);

-- Insert sample data (100k rows, 10 departments, 1000 dates)
INSERT INTO orders100k (department_id, order_date, amount, status)
SELECT
    (RANDOM() * 9 + 1)::INT,
    '2025-01-01'::DATE + (RANDOM() * 365)::INT,
    (RANDOM() * 1000)::DECIMAL(10,2),
    CASE WHEN RANDOM() < 0.5 THEN 'completed' ELSE 'pending' END
FROM generate_series(1, 100000);

-- Index A: department_id first, order_date second
CREATE INDEX idx_dept_date ON orders100k (department_id, order_date);

-- Query 1: filter on leading column only → efficient
EXPLAIN ANALYZE
SELECT COUNT(*)
FROM   orders100k
WHERE  department_id = 3;
-- Plan: Index Only Scan on idx_dept_date — excellent

-- Query 2: filter on both → still efficient
EXPLAIN ANALYZE
SELECT COUNT(*)
FROM   orders100k
WHERE  department_id = 3
  AND  order_date BETWEEN '2025-06-01' AND '2025-06-30';
-- Plan: Index Only Scan — even better because range is narrowed

-- Query 3: filter on second column only → NOT efficient
EXPLAIN ANALYZE
SELECT COUNT(*)
FROM   orders100k
WHERE  order_date BETWEEN '2025-06-01' AND '2025-06-30';
-- Plan: Seq Scan (reads whole table) — index cannot help because leading column is missing

-- To support Query 3, you'd need an index on (order_date) alone
CREATE INDEX idx_date ON orders100k (order_date);

Multilevel Indexing — When a Single B-Tree Won't Fit in Memory

You just read about B-Trees. Good. Now imagine your index itself is too large to fit in RAM. This happens when your table has billions of rows. A single-level B-Tree still requires too many disk IOs for the root lookup. Multilevel indexing solves this by building an index on top of the index. Think of it as a directory of directories. The outer index points to blocks of the inner index. Each level shrinks the search space exponentially. Your database almost certainly uses this under the hood for large tables. You never see it. The query planner handles it. But you need to understand why a query that touches 1% of a 10-billion-row table can still return in milliseconds. It's not magic. It's multiple layers of sparse indexes. Each level stores only a few thousand entries. The top level fits entirely in cache. That means you find your data with 3-4 disk reads instead of 20. The cost? Insertions now update multiple index levels. Write-heavy tables pay this tax. If your workload is 90% reads, multilevel indexing is your best friend. If it's 90% writes, reconsider your schema.

-- io.thecodeforge
-- Simulating multilevel index behavior in PostgreSQL
-- Outer index (level 1) points to inner index blocks (level 2)

CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMP NOT NULL,
    total DECIMAL(10,2)
);

-- PostgreSQL automatically creates a B-Tree index on primary key
-- For multilevel, the engine stores index pages in blocks
-- Inspect index depth using pg_stat_user_indexes

SELECT
    indexrelid::regclass AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

-- Real-world: a 10M row index typically has depth 3-4
-- You can estimate depth with:
SELECT pg_relation_size('orders_pkey') AS index_size_bytes;
-- Divide by 8192 (page size) to get number of leaf pages
-- Then take log base (fill factor * page entries) to estimate levels

Index Scan vs Seek — Why Range Queries Kill Hash Indexes

You slapped a hash index on the user email column. Great for login queries. Terrible for 'find all users registered in January'. Hash indexes only support equality lookups. No range scans. No ORDER BY. No prefix matching. The database has to fall back to a full table scan for anything that isn't an exact match. This is the #1 mistake junior devs make. They see 'index' and think it works everywhere. It doesn't. B-Tree indexes support both seek (exact match) and scan (range). When you query WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31', the B-Tree navigates to the first matching leaf page, then walks the linked list of leaf pages forward. That's an index scan — sequential reads from the index, not the table. If your query filters on a leading column of a composite index, the same logic applies. The database decides between seek and scan based on selectivity. High selectivity (few rows) -> seek. Low selectivity (many rows) -> scan. You can force a seek with a hint in some databases, but don't. Trust the optimizer. It knows the statistics.

-- io.thecodeforge
-- Force different access patterns in SQL Server

CREATE TABLE events (
    event_id BIGINT IDENTITY(1,1) PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    recorded_at DATETIME2 NOT NULL
);

-- Non-clustered index supporting both seek and scan
CREATE NONCLUSTERED INDEX idx_events_recorded_at 
ON events (recorded_at)
INCLUDE (event_type);

-- Query 1: Seek plan (high selectivity)
SELECT * FROM events 
WHERE recorded_at = '2025-01-15 14:30:00';
-- Estimated rows: ~1, plan shows Index Seek

-- Query 2: Scan plan (low selectivity)
SELECT * FROM events 
WHERE recorded_at >= '2025-01-01' 
  AND recorded_at < '2025-02-01';
-- Estimated rows: ~150K, plan shows Index Scan

-- Check actual execution plan
SET STATISTICS XML ON;
GO
-- Run your query, then examine XML for:
-- <PhysicalOp>Index Seek</PhysicalOp> vs <PhysicalOp>Index Scan</PhysicalOp>

B-Tree vs LSM-Tree: Storage Engine Comparison

While B-trees dominate relational databases, LSM-trees (Log-Structured Merge-Trees) power many modern NoSQL systems like Cassandra, RocksDB, and LevelDB. The core difference lies in write amplification: B-trees update data in-place, causing random disk I/O, whereas LSM-trees buffer writes in memory (memtable) and flush them as immutable sorted SSTables, merging them in the background. This makes LSM-trees superior for write-heavy workloads—up to 10x faster writes—but they suffer from read amplification: a point query may need to check multiple SSTables and the memtable. B-trees excel at reads, especially range scans, because data is stored contiguously. For example, an e-commerce checkout system with frequent order inserts (write-heavy) might prefer LSM-trees, while a reporting system with complex range queries (read-heavy) benefits from B-trees. Bloom filters in LSM-trees reduce unnecessary SSTable lookups, but they add memory overhead. In practice, PostgreSQL uses B-trees by default, while MySQL's MyRocks engine uses LSM-trees. Choosing between them requires understanding your workload's read/write ratio and latency tolerance.

-- Simulating write-heavy workload: 1M inserts
-- B-tree (PostgreSQL default)
CREATE TABLE orders_btree (id SERIAL PRIMARY KEY, data TEXT);
INSERT INTO orders_btree (data) SELECT 'payload' FROM generate_series(1, 1000000);

-- LSM-tree (using MyRocks engine in MySQL)
CREATE TABLE orders_lsm (id INT AUTO_INCREMENT PRIMARY KEY, data TEXT) ENGINE=MyRocks;
INSERT INTO orders_lsm (data) SELECT 'payload' FROM generate_series(1, 1000000);

-- Compare insert latency (conceptual, not actual benchmark)

Index-Only Scans and Covering Indexes

An index-only scan occurs when a query can be satisfied entirely from the index without accessing the table's heap (data pages). This is possible when the index contains all columns referenced in the query (SELECT, WHERE, JOIN, ORDER BY). Such an index is called a covering index. For example, consider a table orders(id, user_id, total, status) with a composite index on (user_id, total). The query SELECT total FROM orders WHERE user_id = 123 can use an index-only scan because total is in the index. If the query also needed status, the index would not cover it, forcing a table lookup. To maximize index-only scans, include frequently selected columns in the index, but beware of index bloat—adding too many columns increases size and write overhead. In PostgreSQL, you can check if an index-only scan is used via EXPLAIN ANALYZE; look for "Index Only Scan" in the output. In MySQL, covering indexes are indicated by "Using index" in the Extra column. A practical example: in an e-commerce checkout, if you often query SELECT order_id, total FROM orders WHERE user_id = ? AND status = 'paid', create a covering index on (user_id, status, order_id, total). This avoids heap lookups, speeding up the checkout flow.

-- Create covering index
CREATE INDEX idx_covering ON orders (user_id, status) INCLUDE (order_id, total);

-- Query that benefits from index-only scan
EXPLAIN ANALYZE
SELECT order_id, total FROM orders WHERE user_id = 42 AND status = 'paid';

-- Output should show 'Index Only Scan' (PostgreSQL) or 'Using index' (MySQL)

Partial, Expression, and Conditional Indexes

Not all indexes need to cover every row. Partial indexes (PostgreSQL, SQL Server) index only a subset of rows based on a WHERE clause, reducing index size and maintenance overhead. For example, in an orders table, you might index only rows where status = 'pending' because those are the ones queried frequently. Expression indexes (PostgreSQL, MySQL) index the result of a function or expression, enabling fast lookups on computed values. For instance, CREATE INDEX idx_lower_email ON users (LOWER(email)) speeds up case-insensitive email searches. Conditional indexes (SQL Server filtered indexes) are similar to partial indexes. A practical example: in a checkout system, you might have a partial index on (user_id, created_at) WHERE status = 'abandoned' to quickly find abandoned carts. Without it, a full index on status would include millions of 'completed' rows that are rarely queried. Expression indexes are useful for date truncation: CREATE INDEX idx_order_month ON orders (DATE_TRUNC('month', created_at)) to accelerate monthly reports. However, these indexes require the exact same expression in queries—otherwise they are ignored. In MySQL, expression indexes are supported from 8.0.13; earlier versions require generated columns. Use them judiciously: they optimize specific queries but add complexity.

-- Partial index: only pending orders
CREATE INDEX idx_pending_orders ON orders (user_id, created_at) WHERE status = 'pending';

-- Expression index: case-insensitive email lookup
CREATE INDEX idx_lower_email ON users (LOWER(email));

-- Query using expression index
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';