Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE EXTENSION IF NOT EXISTS pageinspect;

DROP TABLE IF EXISTS demo CASCADE;

CREATE TABLE demo (
    a       integer NOT NULL,
    b       text    NOT NULL,
    payload text    NOT NULL
);

INSERT INTO demo (a, b, payload)
SELECT
    a,
    repeat(md5(g::text), 28),       -- approximately 900 bytes
    repeat('payload-' || a || '-' || g, 20)
FROM generate_series(1, 8) AS a
CROSS JOIN generate_series(1, 80) AS g;

CREATE INDEX demo_ab_idx ON demo (a, b);

CREATE EXTENSION

DROP TABLE

CREATE TABLE

INSERT 0 640

CREATE INDEX

VACUUM ANALYZE demo;

VACUUM

EXPLAIN (ANALYZE, BUFFERS)
WITH RECURSIVE first_per_a AS (
    (
        SELECT a, b
        FROM demo
        ORDER BY a, b
        LIMIT 1
    )
    UNION ALL
    SELECT nxt.a, nxt.b
    FROM first_per_a cur
    CROSS JOIN LATERAL (
        SELECT a, b
        FROM demo
        WHERE a > cur.a
        ORDER BY a, b
        LIMIT 1
    ) AS nxt
)
SELECT b
FROM first_per_a
ORDER BY a;

QUERY PLAN

Sort  (cost=44.62..44.87 rows=101 width=36) (actual time=0.276..0.278 rows=8 loops=1)

  Sort Key: first_per_a.a

  Sort Method: quicksort  Memory: 25kB

  Buffers: shared hit=21

  CTE first_per_a

    ->  Recursive Union  (cost=0.28..39.24 rows=101 width=904) (actual time=0.035..0.262 rows=8 loops=1)

          Buffers: shared hit=21

          ->  Limit  (cost=0.28..0.35 rows=1 width=904) (actual time=0.033..0.033 rows=1 loops=1)

                Buffers: shared hit=3

                ->  Index Only Scan using demo_ab_idx on demo  (cost=0.28..45.88 rows=640 width=904) (actual time=0.022..0.022 rows=1 loops=1)

                      Heap Fetches: 0

                      Buffers: shared hit=3

          ->  Nested Loop  (cost=0.28..3.79 rows=10 width=904) (actual time=0.027..0.027 rows=1 loops=8)

                Buffers: shared hit=18

                ->  WorkTable Scan on first_per_a cur  (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=1 loops=8)

                ->  Limit  (cost=0.28..0.35 rows=1 width=904) (actual time=0.026..0.026 rows=1 loops=8)

                      Buffers: shared hit=18

                      ->  Index Only Scan using demo_ab_idx on demo demo_1  (cost=0.28..16.00 rows=213 width=904) (actual time=0.025..0.025 rows=1 loops=8)

                            Index Cond: (a > cur.a)

                            Heap Fetches: 0

                            Buffers: shared hit=18

  ->  CTE Scan on first_per_a  (cost=0.00..2.02 rows=101 width=36) (actual time=0.038..0.269 rows=8 loops=1)

        Buffers: shared hit=21

Planning:

  Buffers: shared hit=28

Planning Time: 0.773 ms

Execution Time: 0.490 ms

EXPLAIN