Postgres 9.5

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_table (
  usr_id bigint,
  address varchar[],
  msg_cnt bigint,
  usr_cnt bigint,
  source varchar[],
  last_update timestamp
);
INSERT INTO t_table(usr_id, address, msg_cnt, usr_cnt, source, last_update) 
VALUES 
(1, '{44.154.48.125,81.134.82.111,95.155.38.120,94.134.88.136}', 10, 3, '{src1,src2}', '2019-10-16 22:16:22.163000'),
(2, '{44.154.48.125}', 10, 3, '{src1,src3}', '2019-10-16 22:16:22.163000'),
(3, '{94.134.88.136}', 10, 3, '{src1,src4}', '2019-10-16 22:16:22.163000'),
(4, '{127.0.0.1}', 10, 3, '{src1,src5}', '2019-10-16 22:16:22.163000'),
(5, '{127.0.0.1,5.5.5.5}', 10, 3, '{src1,src3}', '2019-10-16 22:16:22.163000'),
(6, '{1.1.0.9}', 10, 3, '{src1,src2}', '2019-10-16 22:16:22.163000');
6 rows affected

with userlist as (
  select array_agg(t.usr_id) as users, 
         a.address
  from t_table t
    left join unnest(t.address) as a(address) on true
  group by a.address  
), shared_users as (
  select u.address,
         array(select distinct ul.uid
               from userlist u2, unnest(u2.users) as ul(uid)
               where u.users && u2.users
               order by ul.uid) as users
  from userlist u
)
select users, array_agg(distinct address)
from shared_users
group by users;


users
array_agg


{1,2,3}
{44.154.48.125,81.134.82.111,94.134.88.136,95.155.38.120}

{4,5}
{127.0.0.1,5.5.5.5}

{6}
{1.1.0.9}

users	array_agg
{1,2,3}	{44.154.48.125,81.134.82.111,94.134.88.136,95.155.38.120}
{4,5}	{127.0.0.1,5.5.5.5}
{6}	{1.1.0.9}