Index tuning

#Index tuning

An index is the highest-leverage cost fix on Cloudflare: one schema change can cut a query's rows read by 100× or more. This guide takes you from a bad amplification number to a verified fix.

#Prerequisites

• Access to the Performance tab in System Health, or the ability to request cost receipts
• Permission to edit the affected collection's schema

#Steps

#1. Find the expensive query pattern

Start from the signal:

• Dashboard: in the groups table, group by collection and sort by read amplification. Anything above 25× is a candidate. The index-scan hint points at the same rows.
• Receipt: request the endpoint with X-Cost-Debug: 1 and compare rr (rows read) to the number of records returned. The X-Cost-Slowest header shows which query does the scanning.

$ curl -i -H "X-Cost-Debug: 1" -H "Authorization: Bearer sk-your-api-key" "https://your-api.example.com/api/v1/collections/orders/records?filter={\"status\":{\"_eq\":\"shipped\"}}&limit=25"

If the response returns 25 records and the receipt shows rr=8025, the filter is scanning the table: 320× amplification.

#2. Identify the field to index

The field to index is the one the query filters or sorts on. In the example above, that's status. When a query both filters and sorts (filter on status, sort on created_at), the filtered field usually matters more — start there.

The X-Cost-Slowest SQL text confirms your guess: look for the column in the WHERE or ORDER BY clause of the scanning query.

#3. Add the index

Mark the field as indexed in the collection's schema. Prefer indexing fields that are:

• Filtered on frequently (status, category, foreign-key style reference fields)
• Selective — an index on a field where 95% of rows share one value helps little
• Sorted on in list views (created_at descending is a common one)

Indexes aren't free: each one adds a row written per insert and update, drawing from the much smaller writes quota. Index the two or three fields your traffic actually filters on, not every field.

#4. Verify the fix

Re-run the same request with X-Cost-Debug: 1 and compare receipts:

X-Cost: dur=41;d1q=7;rr=68;rw=0;d1ms=6;kvr=1;kvw=0;kvd=0;kvl=0;do=1;r2=0;sub=0

rr dropped from 8025 to 68 — the query now reads roughly what it returns, plus the fixed auth baseline. For a change you want on the record, add or re-run a cost:profile scenario covering the filtered query: the before/after report is the durable proof, and the baseline will catch it if the index is ever lost.

The dashboard catches up more slowly — amplification for the group falls as new (cheap) requests dilute the old (expensive) ones in the selected time range.

#What indexes can't fix

Be realistic about the ceiling — some scans are structural:

Pattern	Why an index doesn't help
JSON-path filters	Filters that reach into JSON content are evaluated per row with json_extract — the database can't use a column index for them. If you filter on a JSON property often, promote it to a real field
COUNT(*) on unpaginated counts	Counting all matching rows scans all matching rows. A total count over a large collection reads every row it counts, index or not
Full-text search	Search queries use their own matching path, not column indexes. Search cost scales with the amount of indexed text, and column indexes on the searched fields don't change it

If the expensive pattern is one of these, the fix is shaping the query rather than indexing: paginate, filter on a real column first to shrink the counted set, or move the JSON property into its own field.

#What you learned

• Read amplification tells you that a query scans; X-Cost-Slowest tells you what it scans
• The field in the scanning query's WHERE or ORDER BY clause is the index candidate
• A receipt before and after the change verifies the fix in minutes
• JSON-path filters, unpaginated counts, and full-text search are scan-by-design — shape the query instead

#Next steps

• Profiling — make the improvement part of your baseline so regressions get caught
• Hints reference — the other patterns the dashboard flags, and their fixes