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 t (
    id int,
    arr_val int[]
);
    
INSERT INTO t (id, arr_val)
VALUES
    (1, '{10,20}'),
    (2, '{20,30}'),
    (3, '{50,5}'),
    (4, '{30,60}'),
    (5, '{1,5}'),
    (6, '{7,6}')
;

CREATE TABLE



INSERT 0 6


select *
from t


id
arr_val


1
{10,20}

2
{20,30}

3
{50,5}

4
{30,60}

5
{1,5}

6
{7,6}



SELECT 6


with recursive nodes as (
      select id, val
      from t cross join
           unnest(arr_val) as val
     ),
     edges as (
      select n1.id as id1, n2.id as id2
      from nodes n1 join
           nodes n2
           on n1.val = n2.val
     ),
     cte as (
      select id1, id2, array[id1] as visited, 1 as lev
      from edges
      where id1 = id2
      union all
      select cte.id1, e.id2, visited || e.id2,
             lev + 1
      from cte join
           edges e
           on cte.id2 = e.id1
      where e.id2 <> all(cte.visited) and lev < 8
     ),
     vals as (
      select id1, array_agg(distinct id2 order by id2) as id2s
      from cte
      group by id1
    )
select *, dense_rank() over (order by id2s)
from vals


id1
id2s
dense_rank


1
{1,2,4}
1

2
{1,2,4}
1

4
{1,2,4}
1

3
{3,5}
2

5
{3,5}
2

6
{6}
3



SELECT 6