Postgres 16

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?.

select version();

version

PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

SELECT 1

CREATE UNLOGGED TABLE appointments (
id serial NOT NULL PRIMARY KEY,
updated_at timestamp without time zone NOT NULL DEFAULT timezone('UTC'::text, now()),
synchronized_at timestamp without time zone
);

INSERT INTO appointments(updated_at, synchronized_at)
SELECT '2023-11-20 01:02', 
       CASE WHEN random() < 0.001 THEN now() ELSE '2023-11-20 01:02' END
  FROM generate_series(1, 100000) AS g(n);

CREATE INDEX ON appointments(id) WHERE synchronized_at IS DISTINCT FROM updated_at;

CREATE TABLE

INSERT 0 100000

CREATE INDEX

VACUUM ANALYZE appointments;

VACUUM

-- Pray tell me how the planner expects to get so many rows here
EXPLAIN (ANALYZE) 
SELECT id
  FROM appointments
 WHERE synchronized_at IS DISTINCT FROM updated_at;

QUERY PLAN

Index Only Scan using appointments_id_idx on appointments  (cost=0.14..1003.51 rows=99500 width=4) (actual time=0.020..0.030 rows=101 loops=1)

  Heap Fetches: 0

Planning Time: 0.300 ms

Execution Time: 0.054 ms

EXPLAIN

-- while it "knows" that:
-- 1. Using that index is OK (and uses it!).
-- 2. There are (approximately) just a few tuples in the *entire* index:
SELECT relname, relpages, reltuples
  FROM pg_class
 WHERE relkind = 'i' AND relname LIKE 'appointments%';

relname
relpages
reltuples

appointments_id_idx
2
74

appointments_pkey
276
100000

SELECT 2