Postgres 12

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

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