Postgres 13

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 (field varchar(100));

CREATE TABLE


INSERT INTO table1 
  (field)
VALUES
  ('Hello,world,!'),
  ('Haloa,!'),
  ('Have,a,nice,day,!');

INSERT 0 3


with recursive word_list (field, word, rest, field_id, level) as (             
  select field, 
         substring(field from 1 for position(',' in field) - 1) as word,
         substring(field from position(',' in field) + 1) as rest,
         row_number() over () as field_id,
         1
  from table1
  union all 
  select c.field, 
         case 
            when position(',' in p.rest) = 0 then p.rest
            else substring(p.rest from 1 for position(',' in p.rest) - 1) 
         end as word,
         case 
            when position(',' in p.rest) = 0 then null
            else substring(p.rest from position(',' in p.rest) + 1) 
         end as rest, 
         p.field_id,
         p.level + 1
  from table1 as c
    join word_list p on c.field = p.field and position(',' in p.rest) >= 0
) 
select word
from word_list
order by field_id, level;


word


Hello

world

!

Haloa

!

Have

a

nice

day

!



SELECT 10