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?
answer
- the sort is the cost
- index order must match both directions
- Unique node over an index scan
- still reads every tick
- jump to the next symbol
basics
~20 sA 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 sWith `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 linesCREATE 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
Remember that DISTINCT ON needs sorted input and that a B-tree whose columns and directions match the ORDER BY can supply that order.
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.
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.
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.