Choosing Between a Cache and an Index

Problem Statement

An endpoint takes four seconds and there are two proposals on the table: add an index, or materialize the result. Choosing wrongly is not neutral — an unnecessary cache adds a refresh job, a staleness contract and a second copy of the data, while a missing index makes the cache expensive to maintain and leaves every other consumer slow. This page shows how to read the evidence that decides it.

Why the Naive Approach Fails

The usual decision procedure is a guess dressed as intuition: “it is a big table, so it needs an index” or “it is an aggregate, so it needs a cache”. Both are right often enough to be dangerous.

The evidence is already in the plan, and it takes one command to get:

sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT d.name,
       count(p.id)                     AS parcels,
       ST_Union(p.geom)                AS coverage
FROM districts d
JOIN parcels p ON p.geom && d.geom AND ST_Intersects(p.geom, d.geom)
GROUP BY d.name;

What matters is not the total but where it accumulates.

Three profiles, three different answers Profile one is dominated by a sequential scan and wants an index. Profile two is dominated by aggregate and geometry-function time with a fast scan, and wants a materialized view. Profile three has small per-loop times multiplied by a huge loop count, and wants a query rewrite rather than either. What the node timings are telling you Seq Scan on parcels (actual time=0.02..3860 rows=4,180,000) 94% of the time in a scan that returns almost everything → add the index. A cache here would just cache a table scan. Index Scan … 41 ms · GroupAggregate ST_Union (actual time=…2 940) rows found fast, then 2.9 s of geometry work on them → materialize it, if the answer may be minutes old. Nested Loop … Index Scan (actual time=0.38 rows=3 loops=41,000) each iteration is fast; there are forty-one thousand of them → rewrite: the outer side is too large, or the join is inverted.

Production-Ready Implementation

A short procedure that turns the plan into a decision, written as a checklist you can run in ten minutes:

1. Capture the plan with real parameters. Bind values change everything — a plan for a tiny bounding box and one for a national extent are different queries in practice.

sql
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT …;   -- with the actual values your API sends, not placeholders

2. Attribute the time. Read bottom-up and record three numbers: time in scan nodes, time in function and aggregate nodes, and the largest loops count anywhere in the tree. Remember that a child node’s actual time is per loop and must be multiplied.

3. Apply the rule.

What dominates Remedy Why
Scan nodes, high rows removed by filter index the query cannot find its rows efficiently
Aggregate or geometry-function nodes materialized view the work is real and repeated identically
A large loops count with small per-loop time rewrite the plan shape is wrong, not the storage
shared read far exceeding shared hit memory, or a smaller working set nothing is cached, including the index

4. Confirm the staleness budget before choosing the cache. A cache is only available as an option if somebody can say how out of date the answer may be. If the answer is “it must be current”, the choice collapses to index or rewrite regardless of the profile.

From a slow query to a decision A flow starting at a slow query. Attribute the plan time. If scans dominate, add an index. Otherwise ask whether stale data is acceptable: if yes, materialize; if no, rewrite the query or provision memory. Every path ends in a specific action rather than a general one. Two questions decide it slow query with a real plan do scans dominate? read the node times yes add or fix the index no may the answer be stale? a product decision, not a technical one materialize it rewrite or resize

Configuration and Tuning Knobs

track_io_timing = on adds real I/O timings to EXPLAIN (ANALYZE, BUFFERS), which is what separates “slow because it read from disk” from “slow because it computed”. It costs a small amount of overhead and is worth enabling permanently on any database where these decisions get made.

shared_buffers sets the ceiling on what can stay cached. When the buffers line shows mostly read rather than hit, no index or materialized view changes the fundamental problem — the working set exceeds memory, and either the memory or the working set has to change.

work_mem affects sorts and hash aggregates. A GroupAggregate spilling to disk shows as external merge in the plan, and raising work_mem for that query can be a bigger win than either an index or a cache.

What each remedy changed on the same query The same 3.4-second query after each remedy: adding an index leaves it at 3.3 seconds because scans were only four percent of the time, raising work_mem takes it to 2.9, and materializing it takes it to 41 milliseconds. One compute-bound query, three remedies applied add an index 3.3 s · scans were not the problem raise work_mem 2.9 s · the sort was, a little materialize it 41 ms · the aggregate was the query The plan said which of these would work before any of them were tried. That is the entire method.

Verification Steps

Whichever remedy you pick, verify it against the same measurement that motivated it:

sql
-- before and after, on the same query with the same parameters
EXPLAIN (ANALYZE, BUFFERS) SELECT …;

For an index, the scan node should change type and its time should collapse. For a cache, the endpoint plan should no longer contain the aggregate at all — if it does, the application is still running the old query somewhere. For a rewrite, the loop count should fall by orders of magnitude.

And check the second-order effect. An index adds write cost: measure insert throughput before and after on a write-heavy table. A cache adds a refresh job: measure its duration and confirm it fits inside its schedule with room to grow.

A worked example of the decision

A reporting endpoint takes 3.4 seconds. The plan attributes 120 milliseconds to an index scan over parcels, 2.9 seconds to a GroupAggregate running ST_Union, and 380 milliseconds to a sort. Loops are all 1, and buffers are almost entirely shared hit.

Scans are 4% of the time, so an index changes nothing. Memory is fine, so provisioning changes nothing. The aggregate is the query, and the aggregate is the same for every caller because the grouping key is a district that changes only on an annual boundary import. The product owner confirms the report may be up to an hour old.

That is a materialized view, and the analysis took four minutes. Had the same plan shown 2.9 seconds in a sequential scan with rows removed by filter in the millions, the same four minutes would have produced the opposite answer with equal confidence — which is the point of reading the plan rather than reasoning from the shape of the SQL.

Gotchas Checklist

  • A plan captured with EXPLAIN alone tells you nothing about time. Only ANALYZE executes the query, and only executed nodes report reality.
  • Child node times are per loop. The single most common misreading of a spatial plan is treating a nested loop’s inner scan as if it ran once.
  • Adding both an index and a cache is usually one too many. Do the index first, re-measure, and let the numbers decide whether the cache is still needed.
  • A cache over a query with a bad plan preserves the bad plan. Refreshes will be slow forever, and nothing will surface it because no user waits on them.
  • SETTINGS in the EXPLAIN options shows non-default GUCs affecting the plan. It answers “why does this behave differently on staging?” faster than any other single flag.