JSON field read on every row

Claim: Performance

Postgres stores a large jsonb value compressed, and moves it out of the row when it is still too wide, once a row passes about 2 KB. Reading one field of that value, with ->>, ->, #> or #>>, decompresses and parses the whole document first. A query that does so on every row it reads pays for every document, not for the field.

Example

SELECT ci_runs.id, ci_runs.created_at,
  COALESCE(SUM((ci_runs_queries.optimization->>'cost')::numeric), 0)
FROM ci_runs
LEFT JOIN ci_runs_queries ON ci_runs_queries.ci_run_id = ci_runs.id
WHERE ci_runs.project_id = $1 AND ci_runs.branch = $2
GROUP BY ci_runs.id, ci_runs.created_at
ORDER BY ci_runs.created_at DESC
LIMIT $3;

optimization averages 24.8 KB per value. The LIMIT keeps a page of runs, but the sum reads the cost of every query of every run on the branch before any run can be dropped. On a production copy the query took 1,068 ms; reading each document took about 15 µs a row.

No index helps: the time is spent turning each stored value back into a document, after the rows are found. The planner charges nothing for that, so the plan’s cost and the index search miss it, and a plan that estimates one matching run reads the documents of every run that matches.

How to fix it

Store the field in its own column and read that instead. A stored generated column keeps it current without application code:

ALTER TABLE ci_runs_queries
  ADD COLUMN cost numeric GENERATED ALWAYS AS ((optimization->>'cost')::numeric) STORED;

Then sum ci_runs_queries.cost. With the column, the query above took 2.5 ms. A listing that returned the field for every query of a branch went from 423 ms to 45 ms, and a per-day median of it from 64 ms to 6.1 ms.

Postgres computes the column on every write of the row, so it pays when the field is read more often than the row is written. Adding it rewrites the table once, under a lock that blocks reads and writes: 21 s for a 2.8 GB table. Plan it like any migration that rewrites a table, and measure the query against the column before committing.

Detection

Query Doctor reports this Finding on a query it has a plan for, from the SQL text and the column statistics, when all of these hold:

• The query reads a field out of a column with ->>, ->, #> or #>> inside an aggregate’s argument, a WHERE, HAVING or ON condition, a GROUP BY, or an ORDER BY (an aggregate’s WITHIN GROUP (ORDER BY …) included), or in the select list of a SELECT with no LIMIT.
• The column’s values average at least 2,000 bytes, as measured from the data by the Collector. A column whose size was not measured is not reported.
• The column’s table holds at least 10,000 rows.
• When pg_stat_statements timed the query in production, one execution takes 5 ms or more on average. A query that is already fast has little left to save from a table rewrite. A query nothing timed, such as one from a CI run, is not held to this.

A field read only in the select list of a SELECT with a LIMIT, or of a scalar subquery, reads only the rows returned and is not reported. Neither is a read in a SELECT whose WHERE pins every table to one row by a unique or primary key, such as WHERE id = $1, when Query Doctor has the schema to prove it. A query that returns the whole document is a response-size question, covered by the large-response Finding. The plan’s row estimate is not used: a monitored query is costed with a generic plan, which on skewed data can estimate one row where hundreds match.

When the key is a parameter, as pg_stat_statements leaves it, the Finding says “the field” instead of naming it.