Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tbl (
  id            serial PRIMARY KEY
, refreshed_utc timestamp
, purged_utc    timestamp
);

INSERT INTO tbl (refreshed_utc, purged_utc)
SELECT (timestamp '2022-1-1' - interval '6 month' * random())::timestamp
     , CASE WHEN random() > 0.99 THEN NULL ELSE LOCALTIMESTAMP END  -- much fewer NULL values overall!
FROM  generate_series (1, 100000);

CREATE INDEX tbl_foo_idx ON tbl (purged_utc, refreshed_utc);

CREATE TABLE

INSERT 0 100000

CREATE INDEX

VACUUM ANALYZE tbl;

VACUUM

-- added sort step (not as expected)
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM   tbl -- public.customer
WHERE  purged_utc IS NULL
AND    refreshed_utc < '2021-07-11 00:00:00'
ORDER  BY refreshed_utc
LIMIT  10;

QUERY PLAN

Limit  (cost=96.33..96.36 rows=10 width=20) (actual time=0.186..0.191 rows=10 loops=1)

  Buffers: shared hit=51 read=3

  ->  Sort  (cost=96.33..96.46 rows=52 width=20) (actual time=0.178..0.180 rows=10 loops=1)

        Sort Key: refreshed_utc

        Sort Method: top-N heapsort  Memory: 25kB

        Buffers: shared hit=51 read=3

        ->  Index Scan using tbl_foo_idx on tbl  (cost=0.42..95.21 rows=52 width=20) (actual time=0.065..0.139 rows=51 loops=1)

              Index Cond: ((purged_utc IS NULL) AND (refreshed_utc < '2021-07-11 00:00:00'::timestamp without time zone))

              Buffers: shared hit=51 read=3

Planning:

  Buffers: shared hit=34 read=1

Planning Time: 0.874 ms

Execution Time: 0.259 ms

EXPLAIN

-- no added sort step (as expected)
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM   tbl -- public.customer
WHERE  purged_utc = '2021-07-11 00:00:00'  -- not-null value
AND    refreshed_utc < '2021-07-11 00:00:00'
ORDER  BY refreshed_utc
LIMIT  10;

QUERY PLAN

Limit  (cost=0.42..8.44 rows=1 width=20) (actual time=0.045..0.045 rows=0 loops=1)

  Buffers: shared hit=1 read=2

  ->  Index Scan using tbl_foo_idx on tbl  (cost=0.42..8.44 rows=1 width=20) (actual time=0.044..0.044 rows=0 loops=1)

        Index Cond: ((purged_utc = '2021-07-11 00:00:00'::timestamp without time zone) AND (refreshed_utc < '2021-07-11 00:00:00'::timestamp without time zone))

        Buffers: shared hit=1 read=2

Planning Time: 0.125 ms

Execution Time: 0.067 ms

EXPLAIN