Postgres 16

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




line
criteria


1
a,b

2
b,c,f,h

3
a,b,e,g,h

4
c,e



INSERT 0 4


-- 1. Dynamic SQL
-- create function **once**
CREATE OR REPLACE FUNCTION f_concat_cols(_cols text)
  RETURNS TABLE (result text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
     $q$SELECT concat_ws(',', %s) FROM table1 ORDER BY line$q$, _cols);
END
$func$;

-- For all rows in table2
SELECT DISTINCT line AS t2_line, criteria, t1.*
FROM   table2, f_concat_cols(criteria) t1
ORDER  BY t2_line;

CREATE FUNCTION




t2_line
criteria
result


1
a,b
18,2

1
a,b
12,2

1
a,b
15,2

1
a,b
10,2

1
a,b
20,2

2
b,c,f,h
2,2,2,2

2
b,c,f,h
2,2,0,2

2
b,c,f,h
2,2,0,0

3
a,b,e,g,h
10,2,0,1,2

3
a,b,e,g,h
15,2,0,0,0

3
a,b,e,g,h
20,2,0,0,2

3
a,b,e,g,h
12,2,0,0,0

3
a,b,e,g,h
18,2,0,1,2

4
c,e
2,0



SELECT 14


-- 2. Workaround with conversion to JSON
SELECT DISTINCT t2.line AS t2_line, t2.criteria, c.*
FROM   table2 t2
CROSS  JOIN (SELECT line, to_json(t) AS js FROM table1 t) t1
CROSS  JOIN LATERAL (
   SELECT string_agg(t1.js->>sub, ',') AS result
   FROM   string_to_table(t2.criteria, ',') sub
   ) c
ORDER  BY t2_line;


t2_line
criteria
result


1
a,b
10,2

1
a,b
20,2

1
a,b
15,2

1
a,b
12,2

1
a,b
18,2

2
b,c,f,h
2,2,0,0

2
b,c,f,h
2,2,2,2

2
b,c,f,h
2,2,0,2

3
a,b,e,g,h
20,2,0,0,2

3
a,b,e,g,h
12,2,0,0,0

3
a,b,e,g,h
15,2,0,0,0

3
a,b,e,g,h
18,2,0,1,2

3
a,b,e,g,h
10,2,0,1,2

4
c,e
2,0



SELECT 14


-- 3. Workaround with conversion to Postgres arrays
SELECT DISTINCT t2.line AS t2_line, t2.criteria, c.*
FROM   table2 t2
CROSS  JOIN (SELECT line, ARRAY [a,b,c,d,e,f,g,h] AS arr FROM table1 t) t1
CROSS  JOIN LATERAL (
   SELECT string_agg(t1.arr[idx::int]::text, ',') AS result
   FROM   string_to_table(translate(t2.criteria, 'abcdefgh', '12345678'), ',') idx
   ) c
ORDER  BY t2_line;


t2_line
criteria
result


1
a,b
10,2

1
a,b
12,2

1
a,b
15,2

1
a,b
18,2

1
a,b
20,2

2
b,c,f,h
2,2,0,0

2
b,c,f,h
2,2,0,2

2
b,c,f,h
2,2,2,2

3
a,b,e,g,h
10,2,0,1,2

3
a,b,e,g,h
12,2,0,0,0

3
a,b,e,g,h
15,2,0,0,0

3
a,b,e,g,h
18,2,0,1,2

3
a,b,e,g,h
20,2,0,0,2

4
c,e
2,0



SELECT 14

t2_line	criteria	result
1	a,b	18,2
1	a,b	12,2
1	a,b	15,2
1	a,b	10,2
1	a,b	20,2
2	b,c,f,h	2,2,2,2
2	b,c,f,h	2,2,0,2
2	b,c,f,h	2,2,0,0
3	a,b,e,g,h	10,2,0,1,2
3	a,b,e,g,h	15,2,0,0,0
3	a,b,e,g,h	20,2,0,0,2
3	a,b,e,g,h	12,2,0,0,0
3	a,b,e,g,h	18,2,0,1,2
4	c,e	2,0