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


CREATE TABLE table1(col1 VARCHAR(200));

INSERT INTO table1 VALUES
('id1/id3/id4/id5'),
('id2/id3/id6/id4/id10'),
('id1/id7/id8'),
('id10/id3/id14/id21/id34');

CREATE TABLE



INSERT 0 4


SELECT
  R,
  t2,
  array_position(string_to_array(col1,'/'), t2) as P
FROM (
  SELECT
     ROW_NUMBER() OVER (ORDER BY col1) R,
     col1
   FROM 
     table1) t1 
  CROSS JOIN unnest(string_to_array(col1,'/')) t2


r
t2
p


1
id10
1

1
id3
2

1
id14
3

1
id21
4

1
id34
5

2
id1
1

2
id3
2

2
id4
3

2
id5
4

3
id1
1

3
id7
2

3
id8
3

4
id2
1

4
id3
2

4
id6
3

4
id4
4

4
id10
5



SELECT 17

r	t2	p
1	id10	1
1	id3	2
1	id14	3
1	id21	4
1	id34	5
2	id1	1
2	id3	2
2	id4	3
2	id5	4
3	id1	1
3	id7	2
3	id8	3
4	id2	1
4	id3	2
4	id6	3
4	id4	4
4	id10	5