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 mytable(revenue numeric, series_arr text[], lob_arr text[]);
insert into mytable values
(111,'{sys123,123abc,23r}',null),
(222,'{123abc,23r}','{displays,keyboards}'),
(444,'{123abc,23r}','{soundbars,bluray}')
returning *

CREATE TABLE

revenue
series_arr
lob_arr

111
{sys123,123abc,23r}
null

222
{123abc,23r}
{displays,keyboards}

444
{123abc,23r}
{soundbars,bluray}

INSERT 0 3

select sum(case when '%sys%' like any(series_arr) or 'displays' = any(lob_arr) then revenue else 0 end) as sys_displ_revenue
from mytable

sys_displ_revenue

222

SELECT 1

select sum(case when exists(select from unnest(series_arr)_(e) 
                            where e like '%sys%')
                  or 'displays' = any(lob_arr) 
                then revenue 
                else 0 
           end) as sys_displ_revenue
from mytable;

sys_displ_revenue

333

SELECT 1

select sum(revenue)filter(where exists(select from unnest(series_arr)_(e) 
                                       where e like '%sys%')
                          or 'displays' = any(lob_arr)) as sys_displ_revenue
from mytable

sys_displ_revenue

333

SELECT 1

revenue	series_arr	lob_arr
111	{sys123,123abc,23r}	null
222	{123abc,23r}	{displays,keyboards}
444	{123abc,23r}	{soundbars,bluray}