Cheat SheetsSQLIndexing & Performance

Indexing & Performance — Cheat Sheet

SQL · 6 topics. Download the PDF or the Instagram carousel and share it.

Cheat Sheet · AiCanCode.org

Indexing & Performance

SQL6 topicsQuick revision reference

B-Tree Index Internals

B-Tree indexes store sorted key values in a balanced tree so the database can binary-search to the target leaf node and follow a pointer to the heap row — avoiding full-table scans.

✓B-Tree leaf nodes store key + heap pointer; the tree is traversed top-down then heap pages are fetched — called an index scan.
✓The optimizer picks sequential scan over index scan when selectivity is low (reading most pages anyway) or the table is tiny.
✓Expression indexes must syntactically match the WHERE clause expression exactly, including case and function call.
✓Partial indexes carry fewer entries than full indexes, fit in cache better, and update faster — ideal for large tables with a small active subset.
✓Index bloat grows after heavy DML; VACUUM ANALYZE (PostgreSQL) or OPTIMIZE TABLE (MySQL) reclaims space and refreshes statistics.
✓Always index foreign key columns — unindexed FK columns cause sequential scans on every JOIN and ON DELETE CASCADE.

SQL — standard, expression, and partial index creation

-- Standard single-column index on foreign key (always index FK columns)
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Composite index (covered in a separate concept)
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- Expression index: supports WHERE LOWER(email) = 'foo@bar.com'
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
-- Query that benefits:
SELECT id, name FROM users WHERE LOWER(email) = 'alice@example.com';

-- Partial index: only active users (makes index far smaller)
CREATE INDEX idx_users_active ON users (email) WHERE status = 'active';
-- Benefits only queries that include WHERE status = 'active'
SELECT id, email FROM users WHERE status = 'active' AND email = 'x@y.com';

Composite Index & Column Order

A composite index covers multiple columns, but only queries that reference the leftmost prefix of the index columns can use it — column order is a critical design decision.

✓The leftmost prefix rule: a composite index on (a, b, c) can be used by queries filtering a, (a,b), or (a,b,c) — never b or c alone.
✓Place equality-filter columns before range-filter columns in composite index definitions.
✓A covering index includes all columns referenced in SELECT, WHERE, and ORDER BY — eliminates heap fetches entirely.
✓Low-cardinality leading columns (booleans, small enums) reduce index selectivity; use a partial index instead.
✓Index skip scan (MySQL 8+, PostgreSQL 13+ loose index scan) can sometimes use an index without the leading column, but it is not universally available.
✓Always verify index usage with EXPLAIN before and after adding composite indexes in production.

SQL — leftmost prefix rule with EXPLAIN

-- Index: (department_id, salary)
CREATE INDEX idx_emp_dept_salary ON employees (department_id, salary);

-- Query 1: uses index (leading column present)
EXPLAIN SELECT name, salary FROM employees
WHERE department_id = 3;
-- ✓ Index Scan using idx_emp_dept_salary

-- Query 2: uses index (both columns)
EXPLAIN SELECT name FROM employees
WHERE department_id = 3 AND salary > 70000;
-- ✓ Index Scan — range on second column is fine after equality on first

-- Query 3: CANNOT use index (leading column absent)
EXPLAIN SELECT name, salary FROM employees
WHERE salary > 70000;
-- ✗ Seq Scan — salary is not the leading column
-- Fix: create a separate index on (salary) if this query is common

Covering Index & Index-Only Scans

A covering index contains every column a query needs, allowing the database to satisfy the query entirely from the index without touching the main table heap.

✓An index-only scan satisfies all of SELECT, WHERE, and ORDER BY from the index without fetching heap pages.
✓PostgreSQL INCLUDE clause adds non-key columns to leaf nodes; they do not affect sort order but are visible to index-only scans.
✓MySQL achieves covering indexes by including all needed columns directly in the composite index key.
✓Index-only scans are more likely when visibility map shows all heap pages are all-visible (VACUUM keeps this up to date).
✓The write trade-off: each additional index column adds maintenance cost on every INSERT/UPDATE/DELETE to that column.
✓Cursor-based pagination with a covering index on (created_at DESC, id DESC) INCLUDE (...) avoids the expensive OFFSET deep-page problem.

SQL — heap fetch vs index-only scan with INCLUDE

-- Table: users(id, name, email, city, created_at)
-- Query: paginate active users by created_at, return id + name + email

-- Non-covering index — index scan + heap fetch for name and email
CREATE INDEX idx_users_active_created ON users (created_at DESC) WHERE status = 'active';

EXPLAIN SELECT id, name, email
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20;
-- Plan: Index Scan (partial index) → then Heap Fetch for name, email

-- Covering index using INCLUDE (PostgreSQL): non-key columns in leaf
CREATE INDEX idx_users_covering ON users (created_at DESC)
    INCLUDE (id, name, email)
    WHERE status = 'active';

EXPLAIN SELECT id, name, email
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20;
-- Plan: Index Only Scan ← no heap fetch needed

Index Selectivity & Cardinality

Index selectivity — the ratio of distinct values to total rows — determines whether the optimizer will use an index; low-selectivity columns like booleans often trigger full scans instead.

✓Selectivity = distinct_values / total_rows; higher is better for index usage (PK selectivity = 1.0).
✓The optimizer uses column statistics (histogram, n_distinct) to estimate predicate selectivity — stale stats cause bad plans.
✓Low-selectivity predicates (boolean, small enum) matching a large fraction of rows cause the optimizer to prefer sequential scans.
✓Composite indexes multiply selectivity — a (user_id, status) index is far more selective than either column alone.
✓Run ANALYZE (PostgreSQL) or ANALYZE TABLE (MySQL) after bulk loads to refresh statistics and get accurate query plans.
✓pg_stats.n_distinct in PostgreSQL stores negative values for percentage estimates when the table is large.

SQL — low-selectivity column and statistics query

-- orders.status has 4 values: pending, completed, cancelled, refunded
-- Assume 70% rows are 'completed'
CREATE INDEX idx_orders_status ON orders (status);

-- Optimizer likely ignores index for:
EXPLAIN SELECT * FROM orders WHERE status = 'completed';
-- Seq Scan: 70% of rows match → cheaper to scan all pages sequentially

-- Optimizer MAY use index for:
EXPLAIN SELECT * FROM orders WHERE status = 'cancelled';
-- If only 2% of rows are 'cancelled' → index scan is selective enough

-- Check selectivity manually (PostgreSQL):
SELECT
    attname                                         AS column_name,
    n_distinct,
    (SELECT COUNT(*) FROM orders)                   AS total_rows,
    ROUND(ABS(n_distinct)::numeric /
          NULLIF((SELECT COUNT(*) FROM orders), 0), 4) AS selectivity
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

EXPLAIN / EXPLAIN ANALYZE

EXPLAIN shows the query execution plan chosen by the optimizer; EXPLAIN ANALYZE actually runs the query and reports real row counts and timing — the primary tool for diagnosing slow queries.

✓EXPLAIN shows the plan without executing; EXPLAIN ANALYZE executes and adds actual timings — use ANALYZE in development, not production under load.
✓Plans are read bottom-up: child nodes execute first and feed rows upward to parent nodes.
✓cost=(startup..total) is in abstract page units; actual time= is wall-clock milliseconds.
✓A large gap between estimated rows and actual rows means stale statistics — run ANALYZE to fix.
✓Seq Scan on a join's inner side inside a Nested Loop is a red flag — check for a missing index on the join column.
✓Bitmap Heap Scan combines multiple index scans into a bitmap before accessing heap pages — efficient for medium-selectivity queries.

SQL — annotated EXPLAIN ANALYZE output

-- PostgreSQL EXPLAIN ANALYZE for a JOIN query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total_spent
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed'
  AND u.city = 'Mumbai'
GROUP BY u.id, u.name
ORDER BY total_spent DESC;

/*  Sample output (annotated):

HashAggregate                              ← GROUP BY via hash
  (cost=1240.50..1255.50 rows=150 width=48)
  (actual time=28.3..29.1 rows=142 loops=1) ← actual rows close to estimate ✓
  ->  Hash Join                             ← JOIN strategy chosen
        Hash Cond: (o.user_id = u.id)
        ->  Index Scan on orders            ← index used for status filter
              Index Cond: (status = 'completed')
              (actual rows=8543 loops=1)
        ->  Hash                            ← hash table built from users
              ->  Seq Scan on users         ← no index on city → seq scan ⚠
                    Filter: (city = 'Mumbai')
                    Rows Removed by Filter: 9650
                    (actual rows=350 loops=1)
Planning Time: 1.2 ms
Execution Time: 31.4 ms
*/

Query Optimization Patterns

Common SQL anti-patterns — functions on indexed columns, SELECT *, implicit type casts, OFFSET pagination on large tables — prevent index use and degrade performance at scale.

✓A predicate is sargable if it allows the database to binary-search the index; applying a function to the column makes it non-sargable.
✓Rewrite date range filters as BETWEEN or >= / < on the raw column, never as YEAR(col) = 2024.
✓Implicit type conversion (string to int, int to varchar) causes full scans — always match parameter types to column types.
✓SELECT * prevents covering index use, increases network payload, and is fragile against schema changes.
✓OR across differently-indexed columns may degrade to a seq scan — UNION ALL lets each branch use its own index.
✓OFFSET pagination is O(n) in the offset value; keyset pagination is O(log n) and scales to millions of rows.

SQL — sargable vs non-sargable predicates

-- NON-SARGABLE: function on indexed column → forces Seq Scan
-- created_at has an index, but YEAR() is applied to it
SELECT id, amount FROM orders
WHERE YEAR(created_at) = 2024;          -- MySQL: index on created_at ignored

-- SARGABLE fix: use range predicate on the column itself
SELECT id, amount FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at <  '2025-01-01';       -- ✓ Index Scan on created_at

-- Another common non-sargable pattern: implicit type conversion
-- orders.user_id is INT, but a string is passed
SELECT * FROM orders WHERE user_id = '42';   -- cast forces full scan in some DBs

-- Fix: match the data type
SELECT * FROM orders WHERE user_id = 42;    -- ✓ no cast needed

-- Function on filter column — email has an index
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';  -- index ignored
-- Fix: expression index OR rewrite
CREATE INDEX idx_email_lower ON users (LOWER(email));
-- OR: store emails in lowercase at write time

Learn this free with Aria, your AI tutor → AiCanCode.org/learn/sql

Cheat Sheet

Indexing & Performance

SQL

6 topics · swipe →

AiCanCode.org

Indexing & Performance

B-Tree Index Internals

✓B-Tree leaf nodes store key + heap pointer; the tree is traversed top-down then heap pages are fetched — called an index scan.
✓The optimizer picks sequential scan over index scan when selectivity is low (reading most pages anyway) or the table is tiny.
✓Expression indexes must syntactically match the WHERE clause expression exactly, including case and function call.
✓Partial indexes carry fewer entries than full indexes, fit in cache better, and update faster — ideal for large tables with a small active subset.
✓Index bloat grows after heavy DML; VACUUM ANALYZE (PostgreSQL) or OPTIMIZE TABLE (MySQL) reclaims space and refreshes statistics.

-- Standard single-column index on foreign key (always index FK columns)
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Composite index (covered in a separate concept)
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- Expression index: supports WHERE LOWER(email) = 'foo@bar.com'
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
-- Query that benefits:
…

AiCanCode.org

Indexing & Performance

Composite Index & Column Order

✓The leftmost prefix rule: a composite index on (a, b, c) can be used by queries filtering a, (a,b), or (a,b,c) — never b or c alone.
✓Place equality-filter columns before range-filter columns in composite index definitions.
✓A covering index includes all columns referenced in SELECT, WHERE, and ORDER BY — eliminates heap fetches entirely.
✓Low-cardinality leading columns (booleans, small enums) reduce index selectivity; use a partial index instead.
✓Index skip scan (MySQL 8+, PostgreSQL 13+ loose index scan) can sometimes use an index without the leading column, but it is not universally available.

-- Index: (department_id, salary)
CREATE INDEX idx_emp_dept_salary ON employees (department_id, salary);

-- Query 1: uses index (leading column present)
EXPLAIN SELECT name, salary FROM employees
WHERE department_id = 3;
-- ✓ Index Scan using idx_emp_dept_salary

-- Query 2: uses index (both columns)
…

AiCanCode.org

Indexing & Performance

Covering Index & Index-Only Scans

✓An index-only scan satisfies all of SELECT, WHERE, and ORDER BY from the index without fetching heap pages.
✓PostgreSQL INCLUDE clause adds non-key columns to leaf nodes; they do not affect sort order but are visible to index-only scans.
✓MySQL achieves covering indexes by including all needed columns directly in the composite index key.
✓Index-only scans are more likely when visibility map shows all heap pages are all-visible (VACUUM keeps this up to date).
✓The write trade-off: each additional index column adds maintenance cost on every INSERT/UPDATE/DELETE to that column.

-- Table: users(id, name, email, city, created_at)
-- Query: paginate active users by created_at, return id + name + email

-- Non-covering index — index scan + heap fetch for name and email
CREATE INDEX idx_users_active_created ON users (created_at DESC) WHERE status = 'active';

EXPLAIN SELECT id, name, email
FROM users
WHERE status = 'active'
…

AiCanCode.org

Indexing & Performance

Index Selectivity & Cardinality

✓Selectivity = distinct_values / total_rows; higher is better for index usage (PK selectivity = 1.0).
✓The optimizer uses column statistics (histogram, n_distinct) to estimate predicate selectivity — stale stats cause bad plans.
✓Low-selectivity predicates (boolean, small enum) matching a large fraction of rows cause the optimizer to prefer sequential scans.
✓Composite indexes multiply selectivity — a (user_id, status) index is far more selective than either column alone.
✓Run ANALYZE (PostgreSQL) or ANALYZE TABLE (MySQL) after bulk loads to refresh statistics and get accurate query plans.

-- orders.status has 4 values: pending, completed, cancelled, refunded
-- Assume 70% rows are 'completed'
CREATE INDEX idx_orders_status ON orders (status);

-- Optimizer likely ignores index for:
EXPLAIN SELECT * FROM orders WHERE status = 'completed';
-- Seq Scan: 70% of rows match → cheaper to scan all pages sequentially

-- Optimizer MAY use index for:
…

AiCanCode.org

Indexing & Performance

EXPLAIN / EXPLAIN ANALYZE

✓EXPLAIN shows the plan without executing; EXPLAIN ANALYZE executes and adds actual timings — use ANALYZE in development, not production under load.
✓Plans are read bottom-up: child nodes execute first and feed rows upward to parent nodes.
✓cost=(startup..total) is in abstract page units; actual time= is wall-clock milliseconds.
✓A large gap between estimated rows and actual rows means stale statistics — run ANALYZE to fix.
✓Seq Scan on a join's inner side inside a Nested Loop is a red flag — check for a missing index on the join column.

-- PostgreSQL EXPLAIN ANALYZE for a JOIN query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total_spent
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed'
  AND u.city = 'Mumbai'
GROUP BY u.id, u.name
ORDER BY total_spent DESC;
…

AiCanCode.org

Indexing & Performance

Query Optimization Patterns

✓A predicate is sargable if it allows the database to binary-search the index; applying a function to the column makes it non-sargable.
✓Rewrite date range filters as BETWEEN or >= / < on the raw column, never as YEAR(col) = 2024.
✓Implicit type conversion (string to int, int to varchar) causes full scans — always match parameter types to column types.
✓SELECT * prevents covering index use, increases network payload, and is fragile against schema changes.
✓OR across differently-indexed columns may degrade to a seq scan — UNION ALL lets each branch use its own index.

-- NON-SARGABLE: function on indexed column → forces Seq Scan
-- created_at has an index, but YEAR() is applied to it
SELECT id, amount FROM orders
WHERE YEAR(created_at) = 2024;          -- MySQL: index on created_at ignored

-- SARGABLE fix: use range predicate on the column itself
SELECT id, amount FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at <  '2025-01-01';       -- ✓ Index Scan on created_at
…

AiCanCode.org

Master Indexing & Performance

Full lessons + an AI tutor, free.

AiCanCode.org

/learn/sql