The New Orange mark: a radial orange gradient on a dark square.New Orange CMS

Querying

Keeping a query fast

Which predicates the planner can push into SQL, which it cannot, and how to shape a query so the expensive half never runs.

The performance model is simple to hold in your head: whatever the planner can push into SQL costs a Postgres index lookup, and whatever it cannot costs a row streamed into JavaScript.

Pushed down today

  • _type == "x" and _type in [...]
  • _id == "x" and _id in [...]
  • defined(field)
  • plain field equality, including slug.current == $slug — which has its own expression index, being the most common frontend lookup there is
  • the draft or published discrimination the perspective implies
  • order(...) and a trailing [a...b] slice, when lifting them cannot change the result

Not pushed down

  • Anything inside a projection.
  • Comparisons involving functions the planner does not model.
  • Filters that depend on a dereferenced document's fields.

The last one is the one that bites: *[_type == "post" && author->name == $name] streams every post into the evaluator. Invert it instead — find the author, then find posts referencing that id.

Four habits

  1. Always filter by _type first. It is the cheapest narrowing there is.
  2. Project only what you render. Bodies are large, and a listing that fetches every field pays for every field.
  3. Slice at the end of the query, not in the client. A trailing slice can be pushed down; slicing an array you already fetched cannot.
  4. Split a page's reads. Two cached reads with precise tags beat one giant read whose tag set is the whole dataset.

Measuring

The response envelope carries ms. For anything unclear, run the query in the playground and compare shapes: the difference between a narrowed and an unnarrowed query is usually an order of magnitude, and it is visible immediately.