skip to content

In PostgreSQL, which index lets DISTINCT ON (symbol) ... ORDER BY symbol, traded_at DESC avoid a sort on a huge ticks table, and when is it still slow?

level: seniorimportance: should knowfreq 38%

answer

  1. the sort is the cost
  2. index order must match both directions
  3. Unique node over an index scan
  4. still reads every tick
  5. jump to the next symbol

basics

~20 s

A B-tree on (symbol, traded_at DESC) delivers rows already in DISTINCT ON order, so the plan becomes Unique over an index scan with no sort; it still reads every tick, so few symbols with many ticks need a jump-per-symbol query.

solid answer

~50 s

With `CREATE INDEX ON ticks (symbol, traded_at DESC)` the planner can feed an index scan straight into a `Unique` node, so no `Sort` is needed and the first rows stream out at once; `INCLUDE (price)` can make it an index-only scan on well-vacuumed pages. The directions matter: `(symbol, traded_at)` ascending serves `ORDER BY symbol DESC, traded_at DESC` by scanning backwards, but not the mixed `symbol, traded_at DESC`. The catch is that `Unique` still consumes **every** index entry: 500 symbols over 200 million ticks reads 200 million entries to emit 500 rows, and the planner may prefer a sequential scan plus sort. When there are few keys and many rows per key, emulate a loose index scan with a recursive CTE that jumps to the next symbol, look up each symbol from a `symbols` table, or keep a latest-price table. PostgreSQL 18's skip scan helps only when a `WHERE` condition on `traded_at` is present.

code

sql · 7 lines
sql
CREATE INDEX ticks_symbol_time_idx
  ON ticks (symbol, traded_at DESC) INCLUDE (price);

EXPLAIN (ANALYZE)
SELECT DISTINCT ON (symbol) symbol, price, traded_at
FROM ticks
ORDER BY symbol, traded_at DESC;

go deeper

for a junior

Remember that DISTINCT ON needs sorted input and that a B-tree whose columns and directions match the ORDER BY can supply that order.

for a middle

Explain why (symbol, traded_at DESC) works and (symbol, traded_at) does not for a mixed-direction ORDER BY, and what Unique over an index scan looks like in a plan.

for a senior

Show you can spot when Unique still reads every tick, and replace it with a recursive-CTE jump, a per-symbol lookup or a maintained latest-price table, checking each with EXPLAIN ANALYZE.

for a principal

Decide between query-time cleverness and write-time denormalisation by comparing read frequency, symbol count and write volume, and who will maintain the extra table.

## Why the sort is the expensive part `DISTINCT ON` keeps the first row of each group **after sorting**, so the plan for ```sql SELECT DISTINCT ON (symbol) symbol, price, traded_at FROM ticks ORDER BY symbol, traded_at DESC; ``` is, without help, a `Sort` of the whole `ticks` table followed by a `Unique` node that passes the first row of each run of equal symbols. On a table with hundreds of millions of ticks, the sort dominates: it is a blocking step, it may spill to temporary files when it outgrows `work_mem` (sizing is a memory-tuning topic), and nothing is returned until it finishes. ## The index that removes the sort A B-tree stores entries in key order, and the planner can use that order instead of sorting. The index must match the `ORDER BY` **including directions**, read forwards or backwards: | Index | `ORDER BY symbol, traded_at DESC` | `ORDER BY symbol DESC, traded_at DESC` | |---|---|---| | `(symbol, traded_at DESC)` | Index scan, no sort | No: a backward scan gives `symbol DESC, traded_at ASC` | | `(symbol, traded_at)` | No: mixed directions | Backward index scan, no sort | | `(symbol)` only | Possible `Incremental Sort` within each symbol | Possible `Incremental Sort` within each symbol | With a matching index the plan becomes `Unique` over `Index Scan` (or `Index Only Scan` if the index carries `INCLUDE (price)` and the visibility map marks the pages all-visible). Rows stream out immediately, which matters for a dashboard that reads the first page. ## Why it still reads every tick `Unique` is a simple filter: it reads every row the scan produces and discards all but the first of each group. Nothing tells the index scan to jump past the rest of `ACME`'s entries once one has been returned. Consequences: - With **500 symbols and 200 million ticks**, the plan touches 200 million index entries to return 500 rows. - If the scan must also visit the heap for every row, the random I/O can cost more than a sequential scan plus sort, so the planner may ignore your index. - The index pays off most when there are **many symbols with few ticks each**, or when an index-only scan avoids the heap. ## Jumping between symbols When there are few keys and many rows per key, write a query that performs one short index descent per symbol. PostgreSQL has no built-in loose index scan, so it is emulated with a recursive CTE: ```sql WITH RECURSIVE syms AS ( (SELECT symbol FROM ticks ORDER BY symbol LIMIT 1) UNION ALL SELECT (SELECT t.symbol FROM ticks t WHERE t.symbol > s.symbol ORDER BY t.symbol LIMIT 1) FROM syms s WHERE s.symbol IS NOT NULL ) SELECT s.symbol, (SELECT t.price FROM ticks t WHERE t.symbol = s.symbol ORDER BY t.traded_at DESC, t.id DESC LIMIT 1) AS price FROM syms s WHERE s.symbol IS NOT NULL; ``` Each step is one index descent on `(symbol, traded_at DESC)`, so the cost scales with the number of symbols, not ticks. Other options: 1. If a `symbols` table already lists every symbol, a per-symbol `LIMIT 1` lookup (often written with `LATERAL`, a topic of its own) needs no recursion. 2. If the latest price is read far more often than ticks are written, keep a small `latest_prices` table updated by the writer; upserts belong to the upsert topic. ## What PostgreSQL 18's skip scan does and does not change PostgreSQL 18 added **B-tree skip scan**: when a `WHERE` condition constrains a later index column but not the leading one, the scan can generate an internal equality for each leading value and skip the rest. That works on **predicates**, not on `DISTINCT ON`: - `DISTINCT ON (symbol) ... ORDER BY symbol, traded_at DESC` with **no** `WHERE` still reads every entry. - Adding `WHERE traded_at >= now() - interval '15 minutes'` lets skip scan read only each symbol's recent slice of `(symbol, traded_at DESC)`, which is fast when there are few symbols. The meaning changes, though: symbols without a tick in that window vanish from the result. ## Choosing State the trade in one sentence: the matching index removes the sort and makes the query stream, but only a jump-per-key strategy makes cost proportional to the number of symbols. Confirm the plan with `EXPLAIN (ANALYZE)`, looking for `Unique` over an index scan and the absence of `Sort`.

  • With only an index on (symbol), can PostgreSQL still avoid a full sort?
    Partly. The index supplies rows grouped by `symbol`, and the planner can add an `Incremental Sort` that sorts each symbol's rows by `traded_at DESC` separately. That uses less memory than one big sort and starts returning rows sooner, but it still sorts every tick.
  • Can an outer WHERE symbol = 'ACME' make a DISTINCT ON subquery use the index for one symbol?
    Yes. PostgreSQL pushes a condition from the outer query into a `DISTINCT ON` subquery when it references only `DISTINCT ON` columns, because filtering whole groups cannot change which row survives. A condition on `price` is not pushed down, since it could change the survivor.

  • Any index on (symbol, traded_at) serves ORDER BY symbol, traded_at DESC without a sort.
  • With the right index, DISTINCT ON reads only one index entry per symbol.
  • PostgreSQL 18's skip scan turns every DISTINCT ON into a per-symbol jump.
  • If an index exists the planner always uses it for DISTINCT ON.
  • Raising work_mem is the only fix for a slow DISTINCT ON.

Assumes PostgreSQL 18; its B-tree skip scan can apply a WHERE condition on traded_at without an equality on symbol, which earlier releases could not.