CockroachDB indexes — why it still read 200,000 rows after I added one

Symptom: nothing left to trim

During internal testing I was watching the response time of the cluster resource list API. The goal was simple: is there anything left to cut?

I started with the application. Needless serialization, repeated calls, lock contention: I suspected each in turn and found nothing. The code had already been polished several times. So I moved on to what was left: the database.

First hypothesis: it wasn’t slow in dev

Measured in the dev environment, the query wasn’t slow at all. It finished in tens of milliseconds.

If it takes tens of milliseconds in dev, maybe the database isn’t the problem.

I could have stopped at “it’s not slow.” But counting the rows in each table gave the answer.

instances  : 112 rows
volumes    :  87 rows
networks   :  23 rows
projects   :  11 rows

Zero to 150 rows per table. At that size, a full scan and an index scan look the same; reading 100 rows takes next to no time either way.

So it wasn’t fast because the query was good; it was fast because there was no data. Real cloud clusters have thousands to tens of thousands of instances and volumes, so tens of milliseconds measured here tell you nothing about how it behaves there.

The clue: FULL SCAN in EXPLAIN

I connected straight to the database pod and ran EXPLAIN on the query actually being sent.

> EXPLAIN SELECT * FROM volumes WHERE uuid = 'x';

                  info
------------------------------------------
  • scan
    table: volumes@volumes_pkey
    spans: FULL SCAN                        ← here

  index recommendations: 1
    1. CREATE INDEX ON volumes (uuid) STORING (...);

The line to look at is spans: FULL SCAN. To find a single row by uuid, it was sweeping the entire table. The index recommendations below it, suggesting an index on uuid, say the same thing.

I ran it on the join too.

EXPLAIN SELECT i.* FROM instances i
  JOIN projects p ON i.project_id = p.id
 WHERE p.cluster_id = 1;

  → instances : FULL SCAN + lookup join

Here too, instances was read in full, with a lookup join attached where it connects to projects.

A full audit: no secondary indexes at all

To see whether this was a one-off, I queried every table through information_schema.statistics. It was worse than I expected. Seventeen resource tables had no secondary indexes at all beyond the primary key.

Even the most frequently filtered columns, like uuid and project_id, were merely stored columns of the primary key. A stored column only rides along with each index entry, so its value can’t be used to find a position. Searching by it means reading everything.

Reproduction: a 300,000-row scratch table

I knew the problem; what remained was proving it. With dev data, no amount of measuring would show a difference.

Loading data into production tables wasn’t an option. So I created scratch tables with the same structure and loaded 300,000 rows. I matched only the column layout (id, the FK column, uuid) and left the real tables alone. After measuring, I dropped the scratch tables and confirmed the real ones were untouched.

One index made a difference of roughly 30x and 140x. That table was the answer to “it’s not slow.” At 300,000 rows, the same query takes over 400ms.

Why did it read 200,000 rows after indexing?

The indexes only shrank the scan on the 5,000-row projects table. The 200,000-row instances table was still read from start to finish in the plan after indexing, and rows read only dropped to 1,049 once I rewrote the query with an IN subquery.

By the time I had the reproduction table, I thought I’d just clean up the index list, put it in the DDL, and be done. I re-measured, starting with the heaviest query. The instance list is a join across five tables.

SELECT i.*, p.*, h.*, az.*, s.*
  FROM instances i
  JOIN projects            p  ON i.project_id     = p.id
  JOIN hypervisors         h  ON i.hypervisor_id  = h.id
  JOIN availability_zones  az ON i.az_id          = az.id
  JOIN specs               s  ON i.spec_id        = s.id
 WHERE p.cluster_id = ?;

I reproduced it at 200,000 instances and 5,000 projects and compared before and after indexing. By execution time alone, it was 2.2x better. Not bad, it seemed. But the rows-read number was odd: 205,000 to 200,025. Barely any change.

Only after splitting those two numbers did I see what had happened. 205,000 is 200,000 instance rows plus 5,000 project rows; 200,025 is the same 200,000 instance rows plus 25 project rows. The indexes had only helped on the projects side.

The halved execution time came from the same place: the 5,000-row scan went away and the join strategy changed. The 200,000-row scan was still there.

My first explanation, and its hole

At first I thought it read every instance because the cluster_id condition applied after the join.

cluster_id is a column on projects, so I figured there was no way to know which instances qualified before joining.

But that explanation had a hole. Where you write WHERE in SQL doesn’t decide the actual order of execution. CockroachDB’s optimizer uses statistics and cost estimates to pick the join order, where to apply filters, and whether to use an index. The same logical query can get different plans in different environments, and differently shaped queries can end up with the same plan (how CockroachDB’s cost-based optimizer works).

So I’ll stick to what I observed. The plan at the time kept a full scan of instances, and switching to the IN subquery form below narrowed the scan. Explaining why the optimizer picked the first plan would take the statistics and database version from that time, and I didn’t verify those.

Changing the shape of the query

Instead of piling on more indexes, I changed the shape of the query: narrow down the target cluster’s project ids first, then use that result to limit the instance scan itself.

-- ✘ Indexes only: in that plan, instances was still a FULL SCAN
 WHERE p.cluster_id = ?;

-- ✓ Limit the scan range itself
 WHERE i.project_id IN (
         SELECT id FROM projects WHERE cluster_id = ?
       );

There’s no guarantee this form always beats the join. In a similar situation, the first step is to run EXPLAIN ANALYZE on both forms side by side against the same data.

Execution time dropped only about 15% more, from 104ms to 89ms. But rows read fell 190x, from 200,025 to 1,049.

These were measured on the test data at the time (200,000 instances, 5,000 projects). With a different data distribution or database version, the optimizer may pick a different plan, and the gain may differ too.

At 200,000 rows, the whole dataset sits in cache, so the execution time gap looks small. As the data grows, execution time is what diverges. 1,049 tracks the size of one cluster; 200,025 tracks the size of the whole system. The first is close to constant; the second keeps growing.

Why I didn’t index everything

When building the list, I could have gone with every column used in a filter. I didn’t, because an index is a trade of read cost for write cost.

These resource tables take bulk writes during cluster syncs, so indexes weren’t free. I narrowed it to two criteria and picked 16:

• Columns used on their own as filter conditions in real queries (uuid and the like)
• The FK side of joins, where each join does a lookup

One more thing: adding a new index doesn’t touch existing columns or constraints, and CockroachDB’s online schema changes apply it without downtime. I confirmed that before moving on.

Wrapping up

When execution time halved, I nearly stopped there. I only learned the full scan was still there by looking at rows read. From now on I’ll record execution time, rows read, data size, and the query plan before and after any optimization.

The next time a similar query gets slow, this is the order I’ll check things in:

1. Table row counts. First rule out that dev is fast only because there’s no data.
2. spans in EXPLAIN. If you see FULL SCAN, check whether the filter column is an index key.
3. EXPLAIN ANALYZE after indexing. Don’t stop at execution time; check that rows read went down for each table.

Rows read doesn’t show CPU, network, or cache costs either, so if those are suspects, they need measuring separately.

References

• EXPLAIN, EXPLAIN ANALYZE: CockroachDB docs on reading spans, index recommendations, and rows read
• Secondary Indexes: secondary indexes and STORING columns
• Cost-Based Optimizer: how plans are chosen from statistics and costs
• Online Schema Changes: adding indexes without downtime
• information_schema: the statistics table used for the audit