Postgres 15

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 graph (_id int, relates int[]);

INSERT INTO graph VALUES
    (1, array[2, 3])
  , (2, array[4])
  , (3, array[5, 6])
  , (4, array[3, 7])
  , (5, array[]::integer[])
  , (6, array[]::integer[])
  , (7, array[]::integer[])
;

CREATE TABLE



INSERT 0 7


WITH RECURSIVE cte(id, vals, relates, done) AS (
   SELECT g._id, array[g._id], relates, null::int
     FROM graph AS g
    WHERE _id = 1
    UNION ALL
   SELECT g._id, vals||g._id, g.relates
        , MAX(CASE WHEN 3 IN (done, g._id) THEN 3 END) OVER ()
     FROM cte   AS c
     JOIN graph AS g
       ON g._id     = any(c.relates)
      AND NOT g._id = any(c.vals)   -- Avoid cycles
      AND done IS NULL              -- wait until we're done
     )
SELECT * FROM cte
 WHERE id = 3
;


id
vals
relates
done


3
{1,3}
{5,6}
3



SELECT 1


WITH RECURSIVE cte(id, vals, relates, done) AS (
   SELECT g._id, array[g._id], relates, null::int
     FROM graph AS g
    WHERE _id = 1
    UNION ALL
   SELECT g._id, vals||g._id, g.relates
        , MAX(CASE WHEN 3 IN (done, g._id) THEN 3 END) OVER ()
     FROM cte   AS c
     JOIN graph AS g
       ON g._id     = any(c.relates)
      AND NOT g._id = any(c.vals)   -- Avoid cycles
      AND done IS NULL              -- wait until we're done
     )
SELECT * FROM cte
-- WHERE id = 3            -- Commented to show all generated rows
;


id
vals
relates
done


1
{1}
{2,3}
null

2
{1,2}
{4}
3

3
{1,3}
{5,6}
3



SELECT 3