Postgres 14

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();

create table messages (
  id int,
  message text
);

insert into messages values 
  (1, 'Very looooooooooooooooong text'), 
  (2, 'Short text');
  


version


PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit



SELECT 1



CREATE TABLE



INSERT 0 2


  select m.*, gs.posn, substring(m.message, gs.posn, 15) as split_message 
    from messages m
         cross join lateral generate_series(1, length(message), 15) gs(posn);


id
message
posn
split_message


1
Very looooooooooooooooong text
1
Very looooooooo

1
Very looooooooooooooooong text
16
oooooooong text

2
Short text
1
Short text



SELECT 3


with recursive by_words as (
  select m.*, s.n, s.word, length(s.word) as word_len,
         max(s.n) over (partition by m.id) as num_words
    from messages m
         cross join lateral regexp_split_to_table(m.message, '\s+') 
           with ordinality as s(word, n)
), rejoin as (
  select id, n, array[word] as words, word_len as cum_word_len, 
         word_len >= 15 as keep 
    from by_words
   where n = 1
  union all
  select p.id, c.n, 
         case 
           when p.cum_word_len >= 15 then array[c.word]
           else p.words||c.word
         end as words, 
         case 
           when p.cum_word_len >= 15 then c.word_len
           else p.cum_word_len + c.word_len + 1
         end as cum_word_len,
         (p.cum_word_len + c.word_len + 1 >= 15)
           or (c.n = c.num_words) as keep
    from rejoin p
         join by_words c on (c.id, c.n) = (p.id, p.n + 1)
)
select id, 
       row_number() over (partition by id
                              order by n) as segnum,
       array_to_string(words, ' ') as split_message
  from rejoin 
 where keep
 order by 1, 2
;


id
segnum
split_message


1
1
Very looooooooooooooooong

1
2
text

2
1
Short text



SELECT 3

id	message	posn	split_message
1	Very looooooooooooooooong text	1	Very looooooooo
1	Very looooooooooooooooong text	16	oooooooong text
2	Short text	1	Short text