Re: Multiple Aggregations Order

--0000000000002e01c0059c1fed19
Content-Type: text/plain; charset="UTF-8"

I'm aiming to serialize some graph data into a JSON format, and some of the
data needed for the serialization is in the relation tables, like, "this
node connects to this other node in this way". These are served to IOT
devices and the data changes a lot, so there's a ton of requests and
caching is not that efficient. Due to that, it would be ideal if I could
fetch everything in a single query, with the data needed aggregated in
arrays, so that I can denormalize them later in code (eg.:
zip(data['child_id_set'], data['child_kind_set']) in python).

Each query should have from 100~1000 items from the 'tb' table. The
amount of child relations each item has vary a lot depending on the node,
so it can be from 1~10000.

The tables themselves have some millions of rows, but I don't have access
to the production database to check how many exactly. Due to this, although
I can share the query plan, it wouldn't be so meaningful, since it is
considering my dev env, which only have ~100 rows, and it's throwing seq
scans for everything.

The query is the following:
WITH RECURSIVE tree(tree_id, tree_path) AS (
    SELECT "conn"."child_id",
           ARRAY["conn"."parent_id"]::integer[]
      FROM "conn"
     WHERE "parent_id" IN (643) -- Starting point of graph
     UNION
    SELECT DISTINCT ON ("conn"."child_id")
           "conn"."child_id",
           "tree"."tree_path" || "conn"."parent_id"
      FROM "tree", "conn"
     WHERE "conn"."parent_id" = "tree"."tree_id"
       AND NOT "conn"."child_id" = ANY("tree"."tree_path")
) SELECT "tb".*,
         array_length("tree"."tree_path", 1) AS "depth",
         array_agg("conn"."child_id" ORDER BY ("conn"."order",
"conn"."kind")) FILTER (WHERE "conn"."child_id" IS NOT NULL) AS
"child_id_set",
         array_agg("conn"."kind" ORDER BY ("conn"."order", "conn"."kind"))
FILTER (WHERE "conn"."child_id" IS NOT NULL) AS "child_kind_set",
         array_agg("conn"."restrictions" ORDER BY ("conn"."order",
"conn"."kind")) FILTER (WHERE "conn"."child_id" IS NOT NULL) AS
"child_restrictions_set",
         array_agg("conn"."meta" ORDER BY ("conn"."order", "conn"."kind"))
FILTER (WHERE "conn"."child_id" IS NOT NULL) AS "child_meta_set"
    FROM "tb"
    LEFT OUTER JOIN "conn"
      ON "tb"."id" = "conn"."parent_id"
   INNER JOIN (SELECT DISTINCT ON ("tree_id") * FROM "tree") AS "tree"
      ON "tree"."tree_id" = "tb"."id"
GROUP BY "tb"."id", "tree"."tree_path";

I'm currently using Postgres 11

On Tue, Jan 14, 2020 at 4:49 PM Michael Lewis <[email protected]> wrote:

> "handle this aggregated data later in code"
>
> What is your end goal though? Also, approx how many rows in these tables?
> Can you share an example query and plan? What version are you using?
>
>>

--0000000000002e01c0059c1fed19
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">I&#39;m aiming to serialize some graph da=
ta into a JSON format, and some of the data needed for the serialization is=
 in the relation tables, like, &quot;this node connects to this other node =
in this way&quot;. These are served to IOT devices and the data changes a l=
ot, so there&#39;s a ton of requests and caching is not that efficient. Due=
 to that, it would be ideal if I could fetch everything in a single query, =
with the data needed aggregated in arrays, so that I can denormalize them l=
ater in code (eg.: zip(data[&#39;child_id_set&#39;], data[&#39;child_kind_s=
et&#39;]) in python).<div><br></div><div>Each query should have from 100~10=
00 items from the &#39;tb&#39; table. The amount=C2=A0of child relations ea=
ch item has vary a lot depending on the node, so it can be from 1~10000.</d=
iv><div><br></div><div>The tables themselves have some millions of rows, bu=
t I don&#39;t have access to the production database to check how many exac=
tly. Due to this, although I can share the query plan, it wouldn&#39;t be s=
o meaningful, since it is considering my dev env, which only have ~100 rows=
, and it&#39;s throwing seq scans for everything.</div><div><br></div><div>=
The query is the following:</div><font face=3D"monospace">WITH RECURSIVE tr=
ee(tree_id, tree_path) AS (<br>=C2=A0 =C2=A0 SELECT &quot;conn&quot;.&quot;=
child_id&quot;,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ARRAY[&quot;con=
n&quot;.&quot;parent_id&quot;]::integer[]<br>=C2=A0 =C2=A0 =C2=A0 FROM &quo=
t;conn&quot;<br>=C2=A0 =C2=A0 =C2=A0WHERE &quot;parent_id&quot; IN (643) --=
 Starting point of graph<br>=C2=A0 =C2=A0 =C2=A0UNION<br>=C2=A0 =C2=A0 SELE=
CT DISTINCT ON (&quot;conn&quot;.&quot;child_id&quot;)<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0&quot;conn&quot;.&quot;child_id&quot;,<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&quot;tree&quot;.&quot;tree_path&quot; ||=
 &quot;conn&quot;.&quot;parent_id&quot;<br>=C2=A0 =C2=A0 =C2=A0 FROM &quot;=
tree&quot;, &quot;conn&quot;<br>=C2=A0 =C2=A0 =C2=A0WHERE &quot;conn&quot;.=
&quot;parent_id&quot; =3D &quot;tree&quot;.&quot;tree_id&quot;<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0AND NOT &quot;conn&quot;.&quot;child_id&quot; =3D ANY(&=
quot;tree&quot;.&quot;tree_path&quot;)<br>) SELECT &quot;tb&quot;.*,<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0array_length(&quot;tree&quot;.&quot;tree_pat=
h&quot;, 1) AS &quot;depth&quot;,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0arra=
y_agg(&quot;conn&quot;.&quot;child_id&quot; ORDER BY (&quot;conn&quot;.&quo=
t;order&quot;, &quot;conn&quot;.&quot;kind&quot;)) FILTER (WHERE &quot;conn=
&quot;.&quot;child_id&quot; IS NOT NULL) AS &quot;child_id_set&quot;,<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0array_agg(&quot;conn&quot;.&quot;kind&quo=
t; ORDER BY (&quot;conn&quot;.&quot;order&quot;, &quot;conn&quot;.&quot;kin=
d&quot;)) FILTER (WHERE &quot;conn&quot;.&quot;child_id&quot; IS NOT NULL) =
AS &quot;child_kind_set&quot;,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0array_a=
gg(&quot;conn&quot;.&quot;restrictions&quot; ORDER BY (&quot;conn&quot;.&qu=
ot;order&quot;, &quot;conn&quot;.&quot;kind&quot;)) FILTER (WHERE &quot;con=
n&quot;.&quot;child_id&quot; IS NOT NULL) AS &quot;child_restrictions_set&q=
uot;,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0array_agg(&quot;conn&quot;.&quot=
;meta&quot; ORDER BY (&quot;conn&quot;.&quot;order&quot;, &quot;conn&quot;.=
&quot;kind&quot;)) FILTER (WHERE &quot;conn&quot;.&quot;child_id&quot; IS N=
OT NULL) AS &quot;child_meta_set&quot;<br>=C2=A0 =C2=A0 FROM &quot;tb&quot;=
<br>=C2=A0 =C2=A0 LEFT OUTER JOIN &quot;conn&quot;<br>=C2=A0 =C2=A0 =C2=A0 =
ON &quot;tb&quot;.&quot;id&quot; =3D &quot;conn&quot;.&quot;parent_id&quot;=
<br>=C2=A0 =C2=A0INNER JOIN (SELECT DISTINCT ON (&quot;tree_id&quot;) * FRO=
M &quot;tree&quot;) AS &quot;tree&quot;<br>=C2=A0 =C2=A0 =C2=A0 ON &quot;tr=
ee&quot;.&quot;tree_id&quot; =3D &quot;tb&quot;.&quot;id&quot;<br>GROUP BY =
&quot;tb&quot;.&quot;id&quot;, &quot;tree&quot;.&quot;tree_path&quot;;</fon=
t><div><font face=3D"monospace"><br></font><div>I&#39;m currently using Pos=
tgres 11</div></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" c=
lass=3D"gmail_attr">On Tue, Jan 14, 2020 at 4:49 PM Michael Lewis &lt;<a hr=
ef=3D"mailto:[email protected]">[email protected]</a>&gt; wrote:<br></div=
><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border=
-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">&quot;h=
andle this aggregated data later in code&quot;<div dir=3D"ltr"><br></div><d=
iv>What is your end goal though? Also, approx how many rows in these tables=
? Can you share an example query and plan? What version are you=C2=A0using?=
</div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex">
</blockquote></div></div>
</blockquote></div></div>

--0000000000002e01c0059c1fed19--