Not Every Slow Query Needs a New Index

What a SQL review taught me about assumptions, AI-written queries, and common-sense tuning

A development team asked me to review the SQL behind a change-data publishing job before they moved it from once a day to once an hour. In QA, one full run took 10 to 12 minutes. After the review, the same run took under 30 seconds. In production, most of its queries now finish in under a millisecond.

Almost none of that came from adding indexes. It came from reading the queries, asking what the job actually needed, and checking each assumption against real data. This post walks through what we found, in the order we found it, and what I would tell any team (or any AI assistant) writing SQL against a busy production database.

The short version:

• You don't always need to tune the database. Often you need to understand the use case and explain how the database behaves.
• Functions on indexed columns and "this column might be null" assumptions are the most common silent performance killers.
• What is harmless in QA can be dangerous in production, because the data is shaped differently.
• An index can make a query slower. Measure after every change.

The setup

The job polls a PostgreSQL read replica for rows that changed since its last run and publishes them as events. It is a classic checkpoint pattern:

1. Read a ceiling: the newest change timestamp in the source tables.
2. Count the rows changed between the last checkpoint and that ceiling.
3. Page through them with keyset pagination, 500 rows at a time.
4. Store the ceiling as the next checkpoint.

I'll use a generic schema for the examples:

Table	Rows (largest)	What it holds
accounts	~1M	Users, organizations and partners, with an account_type column
contracts	~1,100	Partner contracts, keyed by the owning account
memberships	~650k	Which accounts belong to which organization, with a membership_type
org_members	~9M	Account-to-location assignments
locations	~hundreds of k	Organization addresses

Every table has created_at and updated_at. The source system also maintains updated_at_num, a numeric YYYYMMDDHHMISS copy of updated_at, and that column is indexed on every table. Keep that last detail in mind.

Lesson 1: A function on an indexed column throws the index away

Every change-window filter in the job looked like this:

WHERE COALESCE(a.updated_at, a.created_at) >  :checkpoint
  AND COALESCE(a.updated_at, a.created_at) <= :snapshot

The intent is reasonable: if a row was never updated, fall back to its creation time. But PostgreSQL can only use a B-tree index when the predicate matches the indexed expression. Wrap the column in COALESCE, and the index on updated_at (or updated_at_num) is no longer a candidate. Every window query became a sequential scan of the whole table.

The COALESCE also encoded an assumption: that updated_at can be null. So before changing anything, we checked:

SELECT count(*) FROM accounts WHERE updated_at IS NULL;      -- 0
SELECT count(*) FROM accounts WHERE updated_at_num IS NULL;  -- 0

Zero on every table. The source system sets both timestamps when a row is created, so the fallback could never fire. The fix was to drop the function and filter on the indexed column directly:

WHERE a.updated_at_num >  :checkpoint
  AND a.updated_at_num <= :snapshot

That one change turned every window query into an index range scan.

Two things to take from it:

• Don't defend against a condition until you've checked whether it exists. A one-line count(*) settled a question the code had been paying for on every run.
• If you really do need the fallback, write it so it can use an index. Either (updated_at > x AND updated_at <= y) OR (updated_at IS NULL AND created_at > x AND created_at <= y), or an expression index on exactly COALESCE(updated_at, created_at).

Lesson 2: What is fine in QA can bite in production

The COALESCE wasn't the only defensive habit. One query filtered on TRIM(a.account_type) = 'STAFF'. In QA that is harmless: small tables, fast scans, correct results. In production it does two quiet things:

• It stops any index on account_type from being used.
• It hints that some values might be padded with spaces. If that were true, every other query that compares account_type without TRIM would silently miss those rows.

Again, the answer was in the data:

SELECT count(*) FROM accounts WHERE account_type <> TRIM(account_type);  -- 0

No padded values, so the TRIM came out, and the queries became consistent with each other.

I want to be clear about where this comes from. The developers I work with write good SQL. But we are now in an era where a lot of SQL is drafted by AI assistants, and they are very good at producing queries that return the right answer. They are much less good at knowing your data. A model will happily add COALESCE, TRIM, LOWER or DISTINCT "just in case", because in the abstract each one is safer. On a 10,000-row QA table you'll never notice. On a 10-million-row production table, each one can turn an index lookup into a full scan.

The result being correct does not mean the assumptions behind it are. Before a query ships, check it against the data's real shape: null counts, value distributions, duplicates, and how skewed the busiest keys are. Or ask a database engineer to review it. It never hurts.

Lesson 3: Carry every filter you know into the subquery

The partner-count query looked like this:

SELECT count(*)
FROM contracts c
JOIN accounts a ON a.account_id = c.account_id
WHERE c.org_id = :org
  AND a.updated_at_num > :checkpoint AND a.updated_at_num <= :snapshot
  AND c.account_id IN (
        SELECT DISTINCT m.parent_account_id
        FROM memberships m
        WHERE m.membership_type = 'PRIMARY'
          AND m.is_deleted = false
          AND (m.end_date IS NULL OR m.end_date >= :as_of));

The outer query was narrowed to one organization and a one-hour window: 59 candidate rows. The subquery wasn't narrowed at all. PostgreSQL read 1.77 million membership rows, built a distinct list of 831 IDs, then matched that list against the 59 candidates. That was 31,395 buffers and 83 ms to return a count of zero.

Two changes fixed it:

  AND EXISTS (
        SELECT 1
        FROM memberships m
        WHERE m.parent_account_id = c.account_id   -- correlate to the outer row
          AND m.org_id = :org                      -- the filter the outer query already had
          AND m.membership_type = 'PRIMARY'
          AND m.is_deleted = false
          AND (m.end_date IS NULL OR m.end_date >= :as_of));

	IN (SELECT DISTINCT …)	Correlated EXISTS with filters
Execution time	83 ms	0.8 ms
Buffers	31,395	181
Memberships access	Parallel sequential scan	Index lookup per candidate

A note on IN versus EXISTS: PostgreSQL already turns IN (subquery) into a semi-join, so the two often plan the same way. The DISTINCT adds nothing, since a semi-join never returns a row twice, and it can push the planner toward building the full list first. Switching to EXISTS helps. The bigger win is adding the filter. If the outer query is limited to something you also know about the subquery's rows, say so in the subquery too. Don't rely on the planner to infer it.

Lesson 4: Ask what a query is for before you tune it

The slowest query in the job didn't return any data. It computed the run's ceiling:

SELECT max(ts) FROM (
    SELECT max(updated_at_num) AS ts FROM accounts WHERE account_type IN (...)
    UNION ALL
    SELECT to_char(max(password_changed_at), 'YYYYMMDDHH24MISS')::numeric FROM accounts WHERE account_type IN (...)
    UNION ALL
    SELECT max(updated_at_num) FROM org_members
    UNION ALL
    SELECT max(s.updated_at_num)
    FROM staff_links s JOIN accounts a ON a.account_id = s.account_id AND a.account_type = 'STAFF'
) x;

It took 853 ms and about 1 million buffers to return one number. The plan showed three different behaviours:

Branch	How PostgreSQL ran it	Cost
max(updated_at_num) on one table	Backward index scan, stops at the first row	0.2 ms
max(password_changed_at)	Sequential scan; the column has no index	~590 ms
max(...) over a join	Full join of every staff account, then aggregate	~270 ms

PostgreSQL has a neat shortcut here. As the CREATE AGGREGATE documentation explains, a MIN- or MAX-like aggregate can be answered from an index, because SELECT max(col) FROM tab is equivalent to SELECT col FROM tab ORDER BY col DESC LIMIT 1. The planner walks the index backward and takes the first non-null value. It also works on partitioned tables and UNION ALL views, by merging each child's index. It does not work through a join. The planner source (planagg.c) limits the shortcut to queries that reference exactly one table, so the join branch had to build the whole join just to find its largest value.

That is where understanding the use case matters more than tuning. This value is an upper bound for the next change window. It doesn't have to be the exact newest staff row; it only has to be at least as high. Dropping the join and taking max(updated_at_num) over all of staff_links gives a bound that is never lower, and costs a single index probe. The join filter still applies in the query that reads the window.

The same review turned up a second easy win. The ceiling was global, but the job ran it once per scope, inside a loop, so it returned the same value several times per run. Computing it once per run cost nothing to change.

The password_changed_at branch I left alone. More on that in Lesson 6.

Lesson 5: A test that returns zero rows proves nothing

My first round of EXPLAIN ANALYZE used a one-hour window, the job's steady state. Most queries looked great: under a millisecond. Then I noticed how often the plans said (never executed). In that hour, no organization had changed, so every join after the first step was skipped. The plan was cheap because there was no work, not because the work was cheap.

Widening the window to 30 days told a different story. One query that ran in 0.7 ms on the hourly window chose a completely different plan:

• It started from the membership table instead of the changed rows.
• It read every one of the 15,368 primary memberships, then probed accounts for each.
• It found 31 changed rows, after 72,522 buffers.

The cause was a row estimate. The planner expected 188 primary memberships and got 15,368, about 80 times more. With 188, starting from memberships looks cheap. A likely reason is how PostgreSQL combines filters: by default it multiplies each condition's selectivity as if they were independent, so correlated filters (type, deleted flag, end date) can be badly underestimated. Check that statistics are fresh with ANALYZE first. If the columns really are correlated, CREATE STATISTICS can tell the planner how they relate.

So I now test every change-data query with three windows:

Window	What it tells you
Normal (one hour)	Steady-state cost
Wide (30 days)	What happens after an outage or a missed run
Full history	The very first run, when the checkpoint is empty

The first run is easy to forget. Here it was fine: page one of the full-history query took about 20 ms, because the planner could stop as soon as it had 500 rows. But you only know that by running it.

Lesson 6: "Good enough" is a valid result. Index only what doesn't scale

After the rewrites, I had a list of index candidates that would have made every remaining query near-instant: one on password_changed_at, a partial index for a rare account type, and a few more. Before building any of them, I added up what an hourly run actually cost without them:

Part of the run	Cost
Ceiling queries, once per run	~1.5–1.8 s
Change-window queries, per scope	~1.2 s for the largest
Whole run	~6–7 s, every buffer from cache

The slowest single statement was under a second, far from any timeout. Every index you add costs writes, storage and maintenance forever, and here the database was a replication target that would pay for each one on every write. So most of the candidates were dropped. That is a fine outcome. Not every slow-looking query needs an index; sometimes it needs nothing.

One query was different, because its cost multiplied. It loaded full account details in chunks of 100 IDs, and each chunk joined org_members on account_id, a column with no index. Every chunk scanned a 9-million-row table: about 0.7 s.

Scenario	Accounts to load	Chunks	Time
Hourly	~50	1	~0.7 s
30-day catch-up	~20,000	~200	~2.5 min
First run	~870,000	~8,700	~1.7 hours

That is the test I use now: don't ask whether a query is slow, ask whether its cost grows with the data or the backlog. Fixed costs can usually be tolerated. Costs that multiply need an index. We added exactly one: org_members (account_id).

Lesson 7: A new index can make a query slower

The new index worked exactly as planned: the org_members step went from scanning 5.4 million rows to reading 2,217 buffers. Then I looked at the total for the chunk query. It had gone from 723 ms to 26.9 seconds.

	Before the index	After the index
org_members	Parallel sequential scan, 174k buffers	Index scan, 2,217 buffers
memberships	One scan of the 15k primary rows, hashed	7,191 index probes, each discarding ~14,000 rows
Total	723 ms, 243k buffers	26,872 ms, 22.7M buffers

With org_members suddenly cheap, the planner switched to a nested loop and probed memberships once per organization row through an index on org_id. That index holds every membership in the organization, one per member account. The accounts in this chunk belonged to very large organizations, so each probe walked about 14,000 entries to find the one primary membership it needed.

The planner expected roughly one row per probe. When it knows a value in advance, PostgreSQL can look it up in its most-common-values statistics. Here each probe's value came from the outer row at run time, so the planner estimated an average number of rows per organization. The handful of very large organizations didn't fit that average, and re-running ANALYZE doesn't change how the estimate is made.

The fix was another index, but a very small one: a partial index containing only the rows the query asks for.

CREATE INDEX CONCURRENTLY memberships_primary_org_idx
    ON memberships (org_id) INCLUDE (parent_account_id, end_date)
    WHERE membership_type = 'PRIMARY' AND is_deleted = false;

It holds about 15,000 rows instead of 650,000, so it is small and cheap to maintain, and each probe now lands directly on the row it needs. PostgreSQL uses a partial index only when it can prove, at planning time, that the query's WHERE clause implies the index predicate. The docs point out that a bind parameter such as membership_type = $1 can't prove that. Because this query uses membership_type = 'PRIMARY' AND is_deleted = false as literal values, the planner can match the partial index without any code change.

The lesson isn't "avoid indexes". It's that an index changes the plan around it, not just the step it targets. Re-run the full EXPLAIN ANALYZE after every index, and read the total, not just the line you were fixing.

Building the indexes online

Both indexes went in with CREATE INDEX CONCURRENTLY, so writes kept flowing. A few things I check every time:

1. Look for old transactions first. Per the CREATE INDEX docs, a concurrent build waits before each table scan for transactions that have modified the table, and after the second scan for any transaction holding a snapshot older than that scan. My pre-check found 65 sessions from a replication tool sitting idle in transaction for 28 days. Before cancelling anything, check whether they actually matter:

SELECT count(*) AS sessions,
       count(backend_xmin) AS holding_snapshot,
       count(backend_xid)  AS holding_xid
FROM   pg_stat_activity
WHERE  state = 'idle in transaction'
  AND  now() - xact_start > interval '1 day';

Ours held no snapshot, no transaction ID and no locks on the target tables, so they couldn't block the build or hold back vacuum. That's a cleanup item, not a blocker.

2. Run with autocommit on. CREATE INDEX CONCURRENTLY cannot run inside a transaction block, and some SQL clients wrap scripts in one.

3. Decide on lock_timeout deliberately. It lets a build fail fast instead of queueing behind a long transaction, but a build that fails partway leaves an invalid index behind. I left it unset and watched the progress view instead.

4. Check for invalid indexes afterwards. CREATE INDEX ... IF NOT EXISTS sees an invalid leftover's name and skips it with only a NOTICE. Drop it with DROP INDEX CONCURRENTLY and build again, or rebuild it with REINDEX INDEX CONCURRENTLY.

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

5. Build smallest to largest, and watch replication lag during the big ones.

A review checklist

This is what I now run through for any SQL headed to a large production table, whether a person or an AI wrote it.

Predicates

• No functions wrapped around indexed columns in WHERE or JOIN (COALESCE, TRIM, LOWER, casts, date math).
• Every "defensive" expression is backed by a data check: null counts, padded values, duplicate keys.
• Filters the outer query knows are repeated inside subqueries and on every joined table.
• IN (SELECT DISTINCT ...) replaced with a correlated EXISTS, or at least the DISTINCT dropped.
• Partial-index conditions written as literals, not bind parameters, so the planner can match them.

Purpose

• For each query, what is it for? A ceiling, a count or a page may not need the precision it was written with.
• Values that don't change within a run are computed once per run, not inside a loop.
• Keyset pagination sorts on a unique key, or ends with a unique tie-breaker.

Evidence

• EXPLAIN (ANALYZE, BUFFERS) on production-sized data, for normal, wide and first-run windows.
• No (never executed) hiding the parts you care about.
• Estimated versus actual rows compared; a gap of 10x or more is worth chasing.
• Costs that multiply (per chunk, per page, per backlog hour) indexed; fixed costs judged against the schedule.
• The full plan re-run after every new index.

Closing: tuning is mostly a conversation

Looking back, the biggest gains came from questions, not configuration:

• "Can this column actually be null?" No, so the COALESCE went, and the existing index started working.
• "What is this MAX used for?" An upper bound, so it didn't need the expensive join.
• "Does this cost grow with the backlog?" Only in one place, so we added one index instead of six.

None of that needed database expertise the developers couldn't learn. It needed someone to explain how the planner reads a predicate, why a function hides an index, and why QA data isn't evidence for production. Once that was clear, the developer rewrote the queries himself, and the run time in QA went from 30–50 minutes to under 30 seconds.

Some tuning really is just common sense and basic troubleshooting: read the plan, check the data, and ask what the query is for. If your team writes SQL, especially with an AI assistant drafting it, get a database engineer to look at it before it meets production data. It never hurts, and sometimes it saves you an hour-long first run, or a 27-second query you didn't know you had.

References

• CREATE AGGREGATE: how MIN/MAX-like aggregates use an index
• planagg.c: the MIN/MAX optimization and its single-table restriction
• Indexes on Expressions
• Partial Indexes
• Index-Only Scans and Covering Indexes
• Row Estimation Examples: MCV lists, averages and combining conditions
• Statistics Used by the Planner: including extended statistics
• Using EXPLAIN
• CREATE INDEX: Building Indexes Concurrently