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