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?.

select version();


version


PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


create table t(a text, b int);
insert into t values ('A', 1), ('B', 2), ('C', 3);

create or replace function calc_gen_plain(text, int) returns text
language sql immutable as
$$ select $1||'-'||$2; $$;

create or replace function calc_gen_rec_sql(t) returns text
language sql immutable as
$$ select $1.a||'-'||$1.b; $$;

create or replace function calc_gen_rec_plpgsql(t) returns text
language plpgsql immutable as
$$ begin
  raise notice '[plpgsql] a=%, b=%', $1.a, $1.b;
  return $1.a||'-'||$1.b;
end; $$;

create or replace function calc_gen_d(t) returns text
language plpgsql immutable as
$$ begin
  raise notice '[plpgsql] d=% ++', $1.d;
  return coalesce($1.d,'D ')||'+ '||$1.a||'='||$1.b||' ';
end; $$;

alter table t add gen_val text not null generated always as (calc_gen_plain(a, b)) stored;
alter table t add gen_rec1 text not null generated always as (calc_gen_rec_sql(t)) stored;
alter table t add gen_rec2 text not null generated always as (calc_gen_rec_plpgsql(t)) stored;
alter table t add d text not null generated always as (calc_gen_d(t)) stored;

select length(d),t.* from t;
3 rows affected



length
a
b
gen_val
gen_rec1
gen_rec2
d


8
A
1
A-1
A-1
A-1
D + A=1

14
B
2
B-2
B-2
B-2
D + A=1 + B=2

20
C
3
C-3
C-3
C-3
D + A=1 + B=2 + C=3


update t set a = chr(ascii(a) + 3), b = b + 3;
select length(d),t.* from t ;
3 rows affected



length
a
b
gen_val
gen_rec1
gen_rec2
d


14
D
4
D-4
D-4
D-4
D + A=1 + D=4

20
E
5
E-5
E-5
E-5
D + A=1 + B=2 + E=5

26
F
6
F-6
F-6
F-6
D + A=1 + B=2 + C=3 + F=6


update t set a = chr(ascii(a) + 3), b = b + 3;
select length(d),t.* from t ;
3 rows affected



length
a
b
gen_val
gen_rec1
gen_rec2
d


20
G
7
G-7
G-7
G-7
D + A=1 + D=4 + G=7

26
H
8
H-8
H-8
H-8
D + A=1 + B=2 + E=5 + H=8

32
I
9
I-9
I-9
I-9
D + A=1 + B=2 + C=3 + F=6 + I=9


update t set a = chr(ascii(a) + 3), b = b + 3;
select length(d),t.* from t ;
3 rows affected



length
a
b
gen_val
gen_rec1
gen_rec2
d


27
J
10
J-10
J-10
J-10
D + A=1 + D=4 + G=7 + J=10

33
K
11
K-11
K-11
K-11
D + A=1 + B=2 + E=5 + H=8 + K=11

39
L
12
L-12
L-12
L-12
D + A=1 + B=2 + C=3 + F=6 + I=9 + L=12


update t set a = chr(ascii(a) + 3), b = b + 3;
select t.* from t;
3 rows affected



a
b
gen_val
gen_rec1
gen_rec2
d


M
13
M-13
M-13
M-13
D + A=1 + D=4 + G=7 + J=10 + M=13

N
14
N-14
N-14
N-14
D + A=1 + B=2 + E=5 + H=8 + K=11 + N=14

O
15
O-15
O-15
O-15
D + A=1 + B=2 + C=3 + F=6 + I=9 + L=12 + O=15

length	a	b	gen_val	gen_rec1	gen_rec2	d
8	A	1	A-1	A-1	A-1	D + A=1
14	B	2	B-2	B-2	B-2	D + A=1 + B=2
20	C	3	C-3	C-3	C-3	D + A=1 + B=2 + C=3

length	a	b	gen_val	gen_rec1	gen_rec2	d
14	D	4	D-4	D-4	D-4	D + A=1 + D=4
20	E	5	E-5	E-5	E-5	D + A=1 + B=2 + E=5
26	F	6	F-6	F-6	F-6	D + A=1 + B=2 + C=3 + F=6

length	a	b	gen_val	gen_rec1	gen_rec2	d
20	G	7	G-7	G-7	G-7	D + A=1 + D=4 + G=7
26	H	8	H-8	H-8	H-8	D + A=1 + B=2 + E=5 + H=8
32	I	9	I-9	I-9	I-9	D + A=1 + B=2 + C=3 + F=6 + I=9

length	a	b	gen_val	gen_rec1	gen_rec2	d
27	J	10	J-10	J-10	J-10	D + A=1 + D=4 + G=7 + J=10
33	K	11	K-11	K-11	K-11	D + A=1 + B=2 + E=5 + H=8 + K=11
39	L	12	L-12	L-12	L-12	D + A=1 + B=2 + C=3 + F=6 + I=9 + L=12

a	b	gen_val	gen_rec1	gen_rec2	d
M	13	M-13	M-13	M-13	D + A=1 + D=4 + G=7 + J=10 + M=13
N	14	N-14	N-14	N-14	D + A=1 + B=2 + E=5 + H=8 + K=11 + N=14
O	15	O-15	O-15	O-15	D + A=1 + B=2 + C=3 + F=6 + I=9 + L=12 + O=15