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

select version();

CREATE TABLE friends (
    id uuid PRIMARY KEY,
    dob timestamptz,
    frn integer,
    status text,
    "groupId" uuid,
    "createdAt" timestamptz,
    "updatedAt" timestamptz,
    UNIQUE("groupId", frn)
);

INSERT INTO friends (
    id, 
    dob, frn, status,
    "groupId", "createdAt", "updatedAt"
) VALUES (
    '1da04305-68ef-4dc1-be6c-826ab83a6479',
    '1937-06-01T08:29:08-07:00', 100001, 'New',
    'bc1567bc-14ff-4ba2-b108-4cb2e0f0f768', NOW(), NOW()
) 
ON CONFLICT ("groupId", frn) DO UPDATE SET
    status='Revised', 
    "updatedAt"=NOW()
WHERE friends.status<>'Deleted';

SELECT * FROM friends;

version
PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit

SELECT 1

CREATE TABLE

INSERT 0 1

id	dob	frn	status	groupId	createdAt	updatedAt
1da04305-68ef-4dc1-be6c-826ab83a6479	1937-06-01 16:29:08+01	100001	New	bc1567bc-14ff-4ba2-b108-4cb2e0f0f768	2024-11-19 12:18:51.080315+00	2024-11-19 12:18:51.080315+00

SELECT 1

INSERT 0 1

id	dob	frn	status	groupId	createdAt	updatedAt
1da04305-68ef-4dc1-be6c-826ab83a6479	1937-06-01 16:29:08+01	100001	Revised	bc1567bc-14ff-4ba2-b108-4cb2e0f0f768	2024-11-19 12:18:51.080315+00	2024-11-19 12:18:51.080315+00

SELECT 1