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
);
CREATE UNIQUE INDEX test_abc_ids ON test_abc(id,id2);
CREATE TABLE test_def (
id int PRIMARY KEY
, abc_id int
, abc_id2 int
, FOREIGN KEY (abc_id,abc_id2) REFERENCES test_abc(id,id2) -- default name = confusing
);
CREATE TABLE
CREATE INDEX
CREATE TABLE
-- information schema (unhelpful)
SELECT * -- unique_constraint_catalog, unique_constraint_schema, unique_constraint_name
FROM information_schema.referential_constraints
WHERE constraint_name ~ 'test'; -- searching for right name
| constraint_catalog | constraint_schema | constraint_name | unique_constraint_catalog | unique_constraint_schema | unique_constraint_name | match_option | update_rule | delete_rule |
|---|---|---|---|---|---|---|---|---|
| db_837020864 | public | test_def_abc_id_abc_id2_fkey | null | null | null | NONE | NO ACTION | NO ACTION |
SELECT 1
-- system catalog (actual source of truth)
SELECT *
FROM pg_constraint
WHERE conname ~ 'test'
AND contype = 'f';
| 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 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2679829 | test_def_abc_id_abc_id2_fkey | 2200 | f | f | f | t | 2679824 | 0 | 2679823 | 0 | 2679818 | a | a | s | t | 0 | t | {2,3} | {2,3} | {96,96} | {96,96} | {96,96} | null | null |
SELECT 1