⇦

2026-06-29

Postgres index skip scans

As of Postgres 18 this becomes irrelevant, but in the meantime we can practice our RECURSIVE chops. This post is a rehash of the linked one that fits my brain better.

These kind of queries can often be slow when there are not that many users, but many orders:

SELECT
    user_id,
    max(created_at) AS max_created_at
FROM orders
GROUP BY user_id
ORDER BY max_created_at DESC
LIMIT 5;

We can make it (about) 100 times faster.

CREATE TABLE orders (
    user_id UUID,
    created_at TIMESTAMPTZ
);
INSERT INTO orders
SELECT
    md5((i % 100)::TEXT)::UUID,
    to_timestamp(i)::TIMESTAMPTZ
FROM generate_series(0, 1000000 - 1) AS i;

CREATE INDEX ON orders (user_id ASC, created_at DESC);
VACUUM (ANALYZE) orders;

Let's refactor our original query into the following RECURSIVE one.

WITH RECURSIVE r AS (
    SELECT
        '00000000-0000-0000-0000-000000000000'::UUID AS user_id,
        NULL::TIMESTAMPTZ AS max_created_at
    UNION ALL
    SELECT sub.*
    FROM r,
    LATERAL (
        SELECT user_id, created_at
        FROM orders
        WHERE user_id > r.user_id
        ORDER BY user_id ASC, created_at DESC
        LIMIT 1
    ) AS sub
)
SELECT * FROM r
WHERE max_created_at IS NOT NULL
ORDER BY max_created_at DESC
LIMIT 5;

• Our base case is the smallest UUID and a null timestamp - most other examples online fetch the first record out of orders, but I think it's neater to use a static base case and filter it out afterwards.
• We then accumulate one row at a time in a way that maps directly to the index.
• In the context of user_id > r.user_id, r refers to the previous result (which has one row), not the accumulation of results.

Query go brrr:

Naive GROUP BY
               user_id                |     max_created_at
--------------------------------------+------------------------
 ac627ab1-ccbd-b62e-c96e-702f07f6425b | 1970-01-12 13:46:39+00
 ed3d2c21-991e-3bef-5e06-9713af9fa6ca | 1970-01-12 13:46:38+00
 e2ef524f-bf3d-9fe6-11d5-a8e90fefdc9c | 1970-01-12 13:46:37+00
 26657d5f-f902-0d2a-befe-558796b99584 | 1970-01-12 13:46:36+00
 812b4ba2-87f5-ee0b-c9d4-3bbf5bbe87fb | 1970-01-12 13:46:35+00

Time: 97.505 ms

RECURSIVE version
               user_id                |     max_created_at
--------------------------------------+------------------------
 ac627ab1-ccbd-b62e-c96e-702f07f6425b | 1970-01-12 13:46:39+00
 ed3d2c21-991e-3bef-5e06-9713af9fa6ca | 1970-01-12 13:46:38+00
 e2ef524f-bf3d-9fe6-11d5-a8e90fefdc9c | 1970-01-12 13:46:37+00
 26657d5f-f902-0d2a-befe-558796b99584 | 1970-01-12 13:46:36+00
 812b4ba2-87f5-ee0b-c9d4-3bbf5bbe87fb | 1970-01-12 13:46:35+00

Time: 1.123 ms