# Indexes and Query Plans: Why a Query Is Slow

> Read an EXPLAIN plan, understand when an index helps, and the common reasons a database ignores the index you created.

- Source: https://akshaythakare.com/notes/sql/indexes-and-query-plans
- Topic: SQL
- Level: intermediate
- Reading time: 2 min
- Tags: sql, performance, indexes, postgresql, interview
- Updated: 2026-10-11

## Start with the plan

```sql
EXPLAIN ANALYZE
SELECT * FROM transactions
WHERE store_id = 1001 AND ingested_at >= '2026-01-05';
```

Look for:
- **Seq Scan** on a large table: every row is read.
- **Index Scan / Index Only Scan**: the index narrows the rows.
- Estimated vs actual rows far apart: statistics are stale (`ANALYZE`).
- The node with the largest actual time: optimise that one first.

## When a B-tree index helps

A B-tree index keeps keys sorted, so the database can jump to a range instead of scanning.

```sql
CREATE INDEX ON transactions (store_id, ingested_at);
```

Column order matters. This index serves `store_id = ?` and `store_id = ? AND ingested_at >= ?`, but **not** `ingested_at >= ?` alone.

## Why your index is ignored

| Cause | Example | Fix |
| --- | --- | --- |
| Function on the column | `WHERE date(ingested_at) = ...` | Use a range, or an expression index |
| Low selectivity | `WHERE status = 'paid'` matches 90% | A scan is genuinely cheaper |
| Type mismatch | Comparing text to an integer column | Match the types |
| Leading wildcard | `LIKE '%abc'` | Full-text or trigram index |

## Indexes are not free

Every insert and update must also update each index. Tables that receive heavy writes should only carry the indexes that real queries use.

> **Note: Warehouses are different**
>
> Snowflake, BigQuery and Redshift have no B-tree indexes. They skip data using per-file min/max metadata, so **clustering and partitioning** play the role indexes play in PostgreSQL.