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 data (
  priority text,
  productive integer[],
  unproductive integer[],
  neutral integer[]
)
insert into data values
('High','{1,2}','{}','{4,5}'),
('Medium','{3,4}','{5,7}','{2}'),
('Low','{1}','{2,6}','{}');
3 rows affected

select * from data;


priority
productive
unproductive
neutral


High
{1,2}
{}
{4,5}

Medium
{3,4}
{5,7}
{2}

Low
{1}
{2,6}
{}


with weighted as (
  select
    case priority
      when 'High' then 6
      when 'Medium' then 3
      when 'Low' then 0
    end as priority,
    productive,
    unproductive,
    neutral
  from
    data
), unnested as (
  select
    unnest(productive) as id, priority, 2 as category 
  from
    weighted
  union all
  select
    unnest(unproductive) as id, priority, 1 as category 
  from
    weighted
  union all
  select
    unnest(neutral) as id, priority, 0 as category
  from
    weighted
) 
select
  case category
    when 0 then 'Neutral'
    when 1 then 'UnProductive'
    when 2 then 'Productive'
  end,
  array_agg(id)
from unnested x
where not exists (
  select null
  from unnested
  where id = x.id
  and priority + category > x.priority + x.category
)
group by category, 1
order by category desc;


case
array_agg


Productive
{1,2,3}

UnProductive
{7,6}

Neutral
{4,5}