Postgres 12

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 t as
    select '2015478 warning occurred at 20201403021545' as col union all
    select '2020179 error occurred at 20201303021545' as col union all
    select '2025480 timeout occurred at 20201203021545' as col union all
    select '2025481 timeout occurred at 20201103021545' as col union all
    select '2020482 error occurred at 20201473021545' as col union all
    select '2020157 timeout occurred at 20201403781545' as col union all
    select '2020154 warning occurred at 20201407851545' as col union all
    select '2027845 warning occurred at 20201403458745' as col
8 rows affected

select which, count(*)
from t cross join lateral
     (values (case when col like '%error%' then 'error' end),
             (case when col like '%warning%' then 'warning' end),
             (case when col like '%timeout%' then 'timeout' end)
     ) v(which)
where which is not null
group by which;


which
count


timeout
3

error
2

warning
3


select split_part(col, ' ', 2) as which, count(*)
from t
group by which;


which
count


warning
3

timeout
3

error
2