Postgres 15

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 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


select setseed(.42);

create table t1(a,b,c,d)as
select (random()*9)::int
     , (random()*9)::int
     , (random()*9)::int
     , (random()*9)::int
from generate_series(1,100);

create table t2(a,b,c,d)as
select (random()*9)::int
     , (random()*9)::int
     , (random()*9)::int
     , (random()*9)::int
from generate_series(1,200);
--insert into t2 
  --select 1,null,null,null;


setseed






SELECT 1



SELECT 100



SELECT 200


select count(*)as cnt1
     , count(*)filter(where t2.b=t1.b)as cnt2 
     , count(*)filter(where t2.b=t1.b 
                        and t2.c=t1.c)as cnt3
     , count(*)filter(where t2.b=t1.b 
                        and t2.c=t1.c 
                        and t2.d=t1.d)as cnt4
from t1
join t2 using(a);


cnt1
cnt2
cnt3
cnt4


2031
225
17
2



SELECT 1


select t1.b=t2.b as b
     , t1.c=t2.c as c
     , t1.d=t2.d as d
     , grouping(t1.b=t2.b,t1.c=t2.c,t1.d=t2.d)
     , count(*)
from t1
join t2 using(a)
group by rollup(t1.b=t2.b,t1.c=t2.c,t1.d=t2.d)
having null is not distinct from nullif((t1.b=t2.b and t1.c=t2.c and t1.d=t2.d),true)
order by grouping;


b
c
d
grouping
count


t
t
t
0
2

t
t
null
1
17

t
null
null
3
225

null
null
null
7
2031



SELECT 4


select t1.b=t2.b,t1.c=t2.c,t1.d=t2.d
     , grouping(t1.b=t2.b,t1.c=t2.c,t1.d=t2.d)
     , count(*)
from t1
join t2 using(a)
group by rollup(t1.b=t2.b,t1.c=t2.c,t1.d=t2.d)
--having null is not distinct from nullif((t1.b=t2.b and t1.c=t2.c and t1.d=t2.d),true)
order by grouping;


?column?
?column?
?column?
grouping
count


t
t
t
0
2

f
t
f
0
148

t
t
f
0
15

f
f
t
0
163

t
f
t
0
19

t
f
f
0
189

f
t
t
0
26

f
f
f
0
1469

t
f
null
1
208

t
t
null
1
17

f
f
null
1
1632

f
t
null
1
174

t
null
null
3
225

f
null
null
3
1806

null
null
null
7
2031



SELECT 15

b	c	d	grouping	count
t	t	t	0	2
t	t	null	1	17
t	null	null	3	225
null	null	null	7	2031

?column?	?column?	?column?	grouping	count
t	t	t	0	2
f	t	f	0	148
t	t	f	0	15
f	f	t	0	163
t	f	t	0	19
t	f	f	0	189
f	t	t	0	26
f	f	f	0	1469
t	f	null	1	208
t	t	null	1	17
f	f	null	1	1632
f	t	null	1	174
t	null	null	3	225
f	null	null	3	1806
null	null	null	7	2031