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 test_abc (
pk int PRIMARY KEY
, id int NOT NULL
, id2 int NOT NULL
, CONSTRAINT test_abc_uni UNIQUE (id,id2) -- actual constraint inseat of just index
);
CREATE TABLE test_def (
id int PRIMARY KEY
, abc_id int
, abc_id2 int
, CONSTRAINT test_def_abc_fkey FOREIGN KEY (abc_id,abc_id2) REFERENCES test_abc(id,id2)
);
-- information schema (unhelpful)
SELECT * -- unique_constraint_catalog, unique_constraint_schema, unique_constraint_name
FROM information_schema.referential_constraints
WHERE constraint_name = 'test_def_abc_fkey';
| constraint_catalog | constraint_schema | constraint_name | unique_constraint_catalog | unique_constraint_schema | unique_constraint_name | match_option | update_rule | delete_rule |
|---|---|---|---|---|---|---|---|---|
| db_408211707 | public | test_def_abc_fkey | db_408211707 | public | test_abc_uni | NONE | NO ACTION | NO ACTION |
-- system catalog (actual source of truth)
SELECT *
FROM pg_constraint
WHERE conname = 'test_def_abc_fkey';
| oid | conname | connamespace | contype | condeferrable | condeferred | convalidated | conrelid | contypid | conindid | conparentid | confrelid | confupdtype | confdeltype | confmatchtype | conislocal | coninhcount | connoinherit | conkey | confkey | conpfeqop | conppeqop | conffeqop | conexclop | conbin |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 4729484 | test_def_abc_fkey | 2200 | f | f | f | t | 4729479 | 0 | 4729477 | 0 | 4729472 | a | a | s | t | 0 | t | {2,3} | {2,3} | {96,96} | {96,96} | {96,96} | null | null |