Postgres 11

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 temp table a (myid text, ip inet);

CREATE TABLE


insert into a (myid, ip)
values
  ('0a', '10.10.1.1'),
  ('0a', '10.10.1.2'),
  ('0a', '10.10.1.3'),
  ('0b', '10.10.1.2'),
  ('0b', '10.10.1.4'),
  ('0c', '10.10.1.5'),
  ('0d', '10.10.1.3'),
  ('0e', '10.10.1.6'),
  ('0e', '10.10.1.7'),
  ('0f', '10.10.1.8'),
  ('0f', '10.10.1.9'),
  ('10', '10.10.1.9'),
  ('11', '10.10.1.10'),
  ('12', '10.10.1.11'),
  ('12', '10.10.1.4'),
  ('1a', '10.10.1.2'),
  ('1a', '10.10.1.4'),
  ('1e', '10.10.1.11'),
  ('1f', '10.10.1.12'),
  ('23', '10.10.1.12');

INSERT 0 20


with recursive
  cte_a as
  (
    select
      myid,
      array_agg(distinct ip) as ip
    from
      a
    group by
      myid
  )
, cte_t (myid, pmyid, ip) as
  (
    select
      myid,
      myid,
      ip
    from
      cte_a
    union all
    select
      t.myid,
      a.myid,
      (
        select
          array_agg(sub.n)
        from
          (
            select unnest(t.ip)
            union
            select unnest(a.ip)
          ) as sub (n)
      )
    from
      cte_t as t
      join cte_a as a
        on a.myid <> t.pmyid and t.ip && a.ip and not t.ip @> a.ip
  )
, cte_d as
  (
    select distinct on (myid)
      myid,
      ip
    from
      cte_t
    order by
      myid,
      cardinality(ip) desc
  )
select
  array_agg(myid),
  ip
from
  cte_d
group by
  ip
;


array_agg
ip


{11}
{10.10.1.10}

{0f,10}
{10.10.1.8,10.10.1.9}

{0a,0b,0d,12,1a,1e}
{10.10.1.3,10.10.1.1,10.10.1.11,10.10.1.2,10.10.1.4}

{0e}
{10.10.1.6,10.10.1.7}

{0c}
{10.10.1.5}

{1f,23}
{10.10.1.12}



SELECT 6

array_agg	ip
{11}	{10.10.1.10}
{0f,10}	{10.10.1.8,10.10.1.9}
{0a,0b,0d,12,1a,1e}	{10.10.1.3,10.10.1.1,10.10.1.11,10.10.1.2,10.10.1.4}
{0e}	{10.10.1.6,10.10.1.7}
{0c}	{10.10.1.5}
{1f,23}	{10.10.1.12}