fix-slow-queries

Add an index on `customer_id`:

```sql
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);
```

Why this helps:
- Without an index, Postgres does a full sequential scan of all 5 million rows.
- With a B-tree index on `customer_id`, it can jump straight to matching rows — typically turning 2 seconds into a few milliseconds.

Notes:
- `CONCURRENTLY` avoids locking the table while building the index (important in production), but it can't run inside a transaction block.
- If queries also filter by status/date, consider a composite index like `(customer_id, created_at)` depending on your query patterns.
- Verify it's being used with `EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;` — you should see an Index Scan instead of Seq Scan.

If you're on MySQL instead of Postgres, just drop the `CONCURRENTLY` keyword (use `ALGORITHM=INPLACE, LOCK=NONE` if needed).

Your query is almost certainly doing a full sequential scan of 5 million rows because there's no index on `customer_id`. Here's what to do:

## 1. Confirm the cause

```sql
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 123;
```

If you see `Seq Scan on orders` with `rows=5000000`, that's the problem — Postgres reads every row to find the matches.

## 2. Fix: add an index

```sql
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);
```

- `CONCURRENTLY` avoids locking the table for writes during the build (important at 5M rows) — note it can't run inside a transaction block.
- Afterward, re-run the `EXPLAIN` — you should see an `Index Scan`/`Bitmap Index Scan` and the 2s should drop to low milliseconds.

## 3. Consider a covering index (optional)

If this query runs very frequently, `SELECT *` still requires a heap fetch per row. If you only need a few columns, selecting just those (or adding them to the index as `INCLUDE (col1, col2)`) can make it index-only.

## 4. In your app

If you're using an ORM/migration tool, add the index via a migration so it's tracked in schema history — but use the concurrent-build option (e.g., `algorithm: :concurrently` in Rails with `disable_ddl_transaction!`) so deployment doesn't lock the table.

**One caveat:** if `customer_id = 123` matches a huge fraction of the table (poor selectivity), an index won't help much and the real fix is different (e.g., partial indexes, denormalization, or revisiting why you're fetching so many rows). Check `SELECT count(*) FROM orders WHERE customer_id = 123;` first — if it's thousands of rows and you only display a page, add `LIMIT`/pagination too.

If you'd like, I can walk through the full evidence-based workflow (baseline capture, before/after benchmarks, regression tests, and a PR) for this query — just point me at the repository.

The main problem is the `OR` condition across two tables — it forces PostgreSQL to scan both tables fully and can't use indexes effectively. Here's how to fix it:

## 1. Rewrite the OR as a UNION (biggest win)

```sql
SELECT title, name FROM (
  SELECT p.title, c.name
  FROM posts p
  LEFT JOIN comments c ON p.id = c.post_id
  WHERE p.created_at > '2023-01-01'

  UNION

  SELECT p.title, c.name
  FROM posts p
  JOIN comments c ON p.id = c.post_id
  WHERE c.created_at > '2023-01-01'
) t;
```

Each branch can use its own index independently, and `UNION` deduplicates rows matching both conditions (same semantics as the original `OR`).

## 2. Add supporting indexes

```sql
CREATE INDEX idx_posts_created_at ON posts (created_at);
CREATE INDEX idx_comments_created_at ON comments (created_at);
CREATE INDEX idx_comments_post_id ON comments (post_id); -- if missing
```

Or better, covering indexes to avoid heap fetches:

```sql
CREATE INDEX idx_posts_ca_covering ON posts (created_at) INCLUDE (title);
CREATE INDEX idx_comments_ca_covering ON comments (created_at) INCLUDE (post_id);
```

## 3. Check the plan

```sql
EXPLAIN (ANALYZE, BUFFERS) <your query>;
```

Look for:
- `Seq Scan` on both tables (expected with the OR)
- A `Hash Join` vs `Nested Loop` — with selective date filters, a nested loop over indexes is much faster
- Whether stats are stale: `ANALYZE posts; ANALYZE comments;`

## 4. Other considerations

- **If `c.created_at > '2023-01-01'` is rarely true**, consider whether the LEFT JOIN + OR is even needed — often the intent is just "posts created recently, with their comments," which simplifies to dropping the OR entirely.
- **Partial indexes** if you always filter on recent data: `CREATE INDEX ... ON posts (created_at) WHERE created_at > '2022-01-01';`
- **Partitioning by `created_at`** if date-range queries are your dominant workload at this scale.

The UNION rewrite alone typically takes this from a full 6M-row scan to index-driven lookups — expect it to drop to well under a second if the date filters are selective.

Here's what's likely going on and what I'd do about it.

## Diagnosis

Two problems in that query:

1. **The `OR` defeats index use.** `WHERE p.created_at > '2023-01-01' OR c.created_at > '2023-01-01'` mixes predicates from two tables, so Postgres can't use a simple index scan on either side — it typically falls back to a scan of `posts` plus a join against all 5M comments, then filters.

2. **`LEFT JOIN` + `OR` on the right table is self-contradictory.** A `LEFT JOIN` preserves unmatched posts, but the `OR c.created_at > ...` filter discards any post with no matching comment (NULL fails the predicate). So the query actually behaves like an `INNER JOIN` — worth confirming with the caller which semantics are intended.

## Fixes to try (in order)

**1. Rewrite as `UNION` of two indexed queries** (preserves the OR semantics exactly):

```sql
SELECT p.title, c.name
FROM posts p
JOIN comments c ON p.id = c.post_id
WHERE c.created_at > '2023-01-01'
UNION
SELECT p.title, c.name
FROM posts p
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.created_at > '2023-01-01';
```

Each branch can use an index on `posts.created_at` and `comments.created_at` (plus `comments.post_id` for the join), and `UNION` deduplicates rows a post would otherwise get twice.

**2. If the intended semantics are really "posts created recently OR having recent comments"**, and you only want posts, consider an `EXISTS`:

```sql
SELECT p.title
FROM posts p
WHERE p.created_at > '2023-01-01'
   OR EXISTS (SELECT 1 FROM comments c
              WHERE c.post_id = p.id AND c.created_at > '2023-01-01');
```

**3. Indexes needed** (if not already present):

- `posts (created_at)`
- `comments (created_at)`
- `comments (post_id)` — or better, `comments (post_id, created_at)` so the EXISTS/join can be index-only.

**4. Verify with plans.** Run before/after:

```sql
EXPLAIN (ANALYZE, BUFFERS) <query>;
```

You should see the 5s plan (seq scans + hash join over 5M rows) replaced by index scans with far fewer buffers. Also check `pg_stat_statements` to confirm this query's share of total database time before investing further.

## Caveats

- If you actually need comment rows for *all* posts (true LEFT JOIN output), the UNION rewrite changes results — compare outputs row-for-row before shipping.
- If a date-range filter is common, a BRIN index on `created_at` may be enough given the append-only nature of typical posts/comments tables, at much lower cost than btree.

If you can share the actual `EXPLAIN ANALYZE` output and which app code issues this query, I can pin down the exact plan change and validate the rewrite preserves results.

Add a composite index on `(user_id, occurred_at)`:

```sql
CREATE INDEX CONCURRENTLY idx_events_user_occurred
ON events (user_id, occurred_at);
```

Why this works:

- **Column order matters**: `user_id` is an equality filter, `occurred_at` a range — putting the equality column first lets Postgres seek directly to user 42's entries and then scan the range efficiently.
- **Index-only potential**: if you only need `id, user_id, occurred_at`, Postgres can do an index-only scan. Since you're selecting `*` (including the large `payload` column), it'll do an index scan + heap fetches — still far faster than a sequential scan over 2M rows.
- **`CONCURRENTLY`**: avoids locking the table during the build (important on a live table this size). Note it can't run inside a transaction block.

Verify afterwards:

```sql
EXPLAIN ANALYZE SELECT * FROM events WHERE user_id = 42 AND occurred_at > '2024-01-01';
```

You should see an Index Scan using `idx_events_user_occurred` instead of Seq Scan.

Extra tips:

- If queries filter on many different `user_id`s, consider `CLUSTER`-ing or partitioning by `occurred_at` (e.g., monthly partitions) so old data can be dropped cheaply.
- Run `ANALYZE events;` after creating the index so the planner has fresh statistics.
- If `payload` is huge and often fetched, consider moving it to a separate table or using `TOAST`-friendly column ordering.

The fix is a **composite index** on `(user_id, occurred_at)` — the current seq scan happens because no index matches both predicates.

```sql
CREATE INDEX CONCURRENTLY idx_events_user_id_occurred_at
  ON events (user_id, occurred_at);
```

Why this shape:

- **Column order matters.** `user_id` is an equality predicate, `occurred_at` a range, so equality column goes first. The index lets Postgres seek directly to `user_id = 42` and then range-scan `occurred_at > '2024-01-01'` within it.
- **`CREATE INDEX CONCURRENTLY`** avoids locking the 2M-row table for writes during the build (run it outside a transaction). Expect the build to take a bit on 2M rows, but it's a one-time cost.
- **Result:** the planner should switch to an Index Scan, typically dropping the query from a full 2M-row scan to touching only the matching rows.

Things to check:

1. **Confirm with the plan first:** `EXPLAIN (ANALYZE, BUFFERS)` before and after — verify it flips from `Seq Scan` to `Index Scan` and that buffers read drop sharply.
2. **Selectivity caveat:** if `user_id = 42` matches a huge fraction of the table (e.g., a bot/admin user), the planner may still prefer a seq scan — that's correct behavior, not a bug.
3. **Statistics:** run `ANALYZE events;` after adding the index so the planner has fresh stats.
4. **Don't over-index:** check existing indexes first — if there's already an index leading with `user_id`, a partial index like `ON events (user_id, occurred_at) WHERE occurred_at > ...` may be a smaller alternative, but the plain composite index is the general-purpose choice.

If you want, I can run the full measured workflow (local repro fixture, before/after `EXPLAIN ANALYZE` comparison, and a PR with the migration) — just point me at the repo and environment.