Index Query Tuning — Cheat Sheet

Tags: sql, database, performance, optimization, indexing, b-tree, execution-plan

Level: intermediate

TL;DR: Quick reference for diagnosing slow SQL queries, understanding execution plans, and applying the right index structures (clustered, non-clustered, covering) to optimize performance.

Key concepts

A database index is a data structure (typically a B-Tree) that improves the speed of data retrieval operations on a database table.

Clustered indexes dictate the physical order of data in a table; there can be only one per table.

Non-clustered indexes contain a sorted list of pointers (or clustered index keys) to the actual data rows.

An Index Seek is highly efficient, filtering data directly through the B-Tree structure.

An Index Scan reads all rows in the index, which can be inefficient for large datasets.

A covering index includes all the columns needed by a query, allowing the database engine to resolve the query without accessing the actual table rows.

SARGable (Search Argument Able) predicates allow the database engine to use indexes; avoid wrapping indexed columns in functions.

Execution plans reveal how the database engine executes a query and where the bottlenecks (like full table scans) occur.

Write penalties occur because every insert, update, or delete requires updating all associated indexes.

Typical questions and ready answers

Q: What is the difference between a clustered and a non-clustered index?R: A clustered index determines the physical storage order of the rows, so there is only one per table. A non-clustered index creates a separate structure containing the indexed columns and pointers to the actual rows, allowing multiple non-clustered indexes per table.

Q: How do you approach tuning a slow query?R: I start by running EXPLAIN or EXPLAIN ANALYZE to read the execution plan. I look for sequential scans (table scans) or index scans that could be index seeks. Then, I verify if a covering index can be created or if the WHERE clause violates SARGability.

Q: Why wouldn't you just put an index on every column?R: Because indexes consume disk space and memory, and they significantly degrade write performance. Every time a row is modified, the database engine must synchronously update the B-Trees of all affected indexes.

Pitfalls and common mistakes

Wrapping indexed columns in functions within the WHERE clause (e.g., WHERE YEAR(created_at) = 2023), breaking SARGability.

Over-indexing tables with high write volumes, leading to insert/update performance degradation.

Creating single-column indexes for queries that filter on multiple columns instead of using composite indexes.

Ordering composite index columns incorrectly; the most selective columns and equality checks should come first.

Ignoring implicit data type conversions, which can silently bypass an index and cause a full table scan.

Snippets and commandsSQL

```-- Analyze query execution plan (PostgreSQL)

EXPLAIN ANALYZE

SELECT id, name FROM users WHERE status = 'active';

-- Create a composite, covering index

CREATE INDEX idx_users_status_created

ON users (status, created_at)

INCLUDE (name);

-- Check for unused indexes (PostgreSQL)

SELECT relname, indexrelname, idx_scan

FROM pg_stat_user_indexes

WHERE idx_scan = 0;

```

To review

[ ] Deep dive into Hash Indexes vs B-Tree Indexes.

[ ] Understand specific index types like GIN and GiST for JSONB or spatial data.