Postgres 13

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 TABLE_A(
  id uuid PRIMARY KEY,
  name text
);
CREATE TABLE TABLE_B(
  id uuid PRIMARY KEY,
  name text
);
CREATE TABLE TABLE_A_B(
  id uuid PRIMARY KEY,
  a_id uuid REFERENCES TABLE_A(id) ON UPDATE CASCADE ON DELETE CASCADE,
  B_id uuid REFERENCES TABLE_B(id) ON UPDATE CASCADE ON DELETE CASCADE
);
select *
from information_schema.table_constraints
where constraint_type = 'FOREIGN KEY' and table_name = 'table_a_b'


constraint_catalog
constraint_schema
constraint_name
table_catalog
table_schema
table_name
constraint_type
is_deferrable
initially_deferred
enforced


db_505247347
public
table_a_b_a_id_fkey
db_505247347
public
table_a_b
FOREIGN KEY
NO
NO
YES

db_505247347
public
table_a_b_b_id_fkey
db_505247347
public
table_a_b
FOREIGN KEY
NO
NO
YES


alter table table_a_b drop constraint table_a_b_a_id_fkey
alter table table_a_b add constraint fk_table_a_b_a foreign key (a_id) references table_a(id) on update cascade on delete cascade

constraint_catalog	constraint_schema	constraint_name	table_catalog	table_schema	table_name	constraint_type	is_deferrable	initially_deferred	enforced
db_505247347	public	table_a_b_a_id_fkey	db_505247347	public	table_a_b	FOREIGN KEY	NO	NO	YES
db_505247347	public	table_a_b_b_id_fkey	db_505247347	public	table_a_b	FOREIGN KEY	NO	NO	YES