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

create table table1(line int, a  int, b  int, c  int, d  int, e  int, f  int, g  int, h  int);
insert into table1 values
(1,18,2,2,22,0,2,1,2),
(2,20,2,2,2,0,0,0,2),
(3,10,2,2,222,0,2,1,2),
(4,12,2,2,3,0,0,0,0),
(5,15,2,2,3,0,0,0,0)
  returning *;

CREATE TABLE




line
a
b
c
d
e
f
g
h


1
18
2
2
22
0
2
1
2

2
20
2
2
2
0
0
0
2

3
10
2
2
222
0
2
1
2

4
12
2
2
3
0
0
0
0

5
15
2
2
3
0
0
0
0



INSERT 0 5


create table table2(line int, criteria text);
insert into table2 values
(1,'a,b'),
(2,'b,c,f,h'),
(3,'a,b,e,g,h'),
(4,'c,e')
  returning *;

CREATE TABLE




line
criteria


1
a,b

2
b,c,f,h

3
a,b,e,g,h

4
c,e



INSERT 0 4


SELECT DISTINCT(SELECT criteria FROM table2) 
FROM table1

ERROR:  more than one row returned by a subquery used as an expression