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
, 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