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);
100000 rows affected

VACUUM ANALYZE tbl;
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=98.12..98.15 rows=10 width=20) (actual time=0.329..0.332 rows=10 loops=1)

  Buffers: shared hit=45 read=5

  ->  Sort  (cost=98.12..98.26 rows=53 width=20) (actual time=0.328..0.329 rows=10 loops=1)

        Sort Key: refreshed_utc

        Sort Method: top-N heapsort  Memory: 25kB

        Buffers: shared hit=45 read=5

        ->  Index Scan using tbl_foo_idx on tbl  (cost=0.42..96.98 rows=53 width=20) (actual time=0.205..0.311 rows=48 loops=1)

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

              Buffers: shared hit=45 read=5

Planning:

  Buffers: shared hit=34 read=1

Planning Time: 0.393 ms

Execution Time: 0.409 ms