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
, CONSTRAINT test_def_abc_fkey -- !
FOREIGN KEY (abc_id,abc_id2) REFERENCES test_abc(id,id2)
);
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_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_1051355232 | public | test_def_abc_fkey | null | null | null | NONE | NO ACTION | NO ACTION |
SELECT 1
SELECT *
FROM information_schema.key_column_usage
WHERE constraint_name = 'test_def_abc_fkey';
| constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | column_name | ordinal_position | position_in_unique_constraint |
|---|---|---|---|---|---|---|---|---|
| db_1051355232 | public | test_def_abc_fkey | db_1051355232 | public | test_def | abc_id | 1 | 1 |
| db_1051355232 | public | test_def_abc_fkey | db_1051355232 | public | test_def | abc_id2 | 2 | 2 |
SELECT 2
-- system catalog (actual source of truth)
SELECT c.conname
, c.conrelid::regclass AS fk_table, k1.fk_columns
, c.confrelid::regclass AS ref_table, k2.ref_key_columns
FROM pg_catalog.pg_constraint c
LEFT JOIN LATERAL (
SELECT ARRAY (
SELECT a.attname
FROM pg_catalog.pg_attribute a
, unnest(c.conkey) WITH ORDINALITY AS k(attnum, ord)
WHERE a.attrelid = c.conrelid
AND a.attnum = k.attnum
ORDER BY k.ord
) AS fk_columns
) k1 ON true
LEFT JOIN LATERAL (
SELECT ARRAY (
SELECT a.attname
FROM pg_catalog.pg_attribute a
, unnest(c.confkey) WITH ORDINALITY AS k(attnum, ord)
WHERE a.attrelid = c.confrelid
AND a.attnum = k.attnum
ORDER BY k.ord
) AS ref_key_columns
) k2 ON true
WHERE conname = 'test_def_abc_fkey';
| conname | fk_table | fk_columns | ref_table | ref_key_columns |
|---|---|---|---|---|
| test_def_abc_fkey | test_def | {abc_id,abc_id2} | test_abc | {id,id2} |
SELECT 1