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 t1 (
  a int,
  b int,
  t1 int,
  PRIMARY KEY (a, b)
)
CREATE TABLE t2 (
  a int,
  b int DEFAULT 1 CHECK (b = 1),  -- even with DEFAULT/CHECK it still allows for NULL
  t2 int,
  FOREIGN KEY (a, b) REFERENCES t1
)
INSERT INTO t1 (a, b, t1) 
VALUES(1, 1, 1), (1, 2, 2), (2, 1, 3);
 
INSERT INTO t2 (a,b, t2) 
VALUES (1,1, 11), (2,NULL, 12);
3 rows affected

2 rows affected

SELECT * 
FROM t1
NATURAL LEFT JOIN t2


a
b
t1
t2


1
1
1
11

1
2
2
null

2
1
3
null

a	b	t1	t2
1	1	1	11
1	2	2	null
2	1	3	null