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;
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',
| 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 | 2025-04-04 14:14:36.672067+01 | 2025-04-04 14:14:36.672067+01 |
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 | 2025-04-04 14:14:36.672067+01 | 2025-04-04 14:14:36.672067+01 |
SELECT 1