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 A_to_C (
  a_col VARCHAR(1),
  c_col VARCHAR(1)
);

INSERT INTO A_to_C
  (a_col, c_col)
VALUES
  ('a', 'x'),
  ('b', 'y'),
  ('c', 'z');

CREATE TABLE B_to_C (
  b_col VARCHAR(1),
  c_col VARCHAR(1)
);

INSERT INTO B_to_C
  (b_col, c_col)
VALUES
  ('g', 'u'),
  ('h', 'v'),
  ('i', 'w');
3 rows affected

3 rows affected

select a_col, c_col, null b_col
from A_to_C
union all
select null, c_col, b_col
from B_to_C


a_col
c_col
b_col


a
x
null

b
y
null

c
z
null

null
u
g

null
v
h

null
w
i


select 
  (t.a_col is not null) a_col, 
  t.c_col, 
  (t.b_col is not null) b_col
from (
  select a_col, c_col, null b_col
  from A_to_C
  union all
  select null, c_col, b_col
  from B_to_C
) t


a_col
c_col
b_col


t
x
f

t
y
f

t
z
f

f
u
t

f
v
t

f
w
t


select true a_col, c_col, false b_col
from A_to_C
union all
select false a_col, c_col, true b_col
from B_to_C


a_col
c_col
b_col


t
x
f

t
y
f

t
z
f

f
u
t

f
v
t

f
w
t

a_col	c_col	b_col
a	x	null
b	y	null
c	z	null
null	u	g
null	v	h
null	w	i