Re: SELECT all the rows where id is children of other node. - Mailing list pgsql-general

From Rob Sargent
Subject Re: SELECT all the rows where id is children of other node.
Date
Msg-id B66E84D8-D03A-4684-95BD-88FD82D8E8C9@gmail.com
Whole thread Raw
In response to SELECT all the rows where id is children of other node.  (pabloa98 <pabloa98@gmail.com>)
List pgsql-general



On Aug 19, 2019, at 7:42 PM, pabloa98 <pabloa98@gmail.com> wrote:

Hello,

I have a huge table (100 million rows) of relations between nodes by id in a Postgresql 11 server. Like this:

CREATE TABLE relations (
    pid INTEGER NOT NULL,
    cid INTEGER NOT NULL,
)

This table has parent-child relations references between nodes by id. Like:

pid -> cid
n1 -> n2
n1 -> n3
n1 -> n4
n2 -> n21
n2 -> n22
n2 -> n23
n22 -> n221
n22 -> n222

I would like to get a list of all the nodes being children (direct or indirect) of any other node.

Example. The children of:

1) n3: []  (n3 has not children)
2) n22: [n221, n222]  (n22 has 2 children: n221 and n222)
3) n1: [n2, n21, n22, n23, n221, n222]  (n1 has 6 children including indirect children).

this pseudo SQL: 

SELECT *
FROM relations
WHERE has_parent(myId)

It can be solved with a recursive function or stored procedure. But that requires several passes. Is it possible to solve it in one pass? Perhaps using some low-level function or join or some index expression or auxiliary columns?

It is OK to create an index or similar using recursive expressions. However, the SELECT expressions should be solved in one pass because of speed.


Pablo

Are you asking for just the function (always with a seed Id) or the complete transformation in a single select? Would you like the descendants on one line (eg n22,[n221,n222])?
I wonder if you might add  n3 -> null to explicitly terminate the hierarchy ?

pgsql-general by date:

Previous
From: Vik Fearing
Date:
Subject: Re: Retroactively adding send and recv functions to a type?
Next
From: Rob Sargent
Date:
Subject: Re: SELECT all the rows where id is children of other node.