MIME-Version: 1.0
Date: Wed, 29 Jun 2011 12:55:58 +0100
Message-ID: <BANLkTimwCWg6LGavaNU3YMJNcBiWppX9UQ@mail.gmail.com>
Subject: is parallel union all possible over dblink?
From: Svetlin Manavski <svetlin.manavski@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=001517475e62b959a704a6d877d4

--001517475e62b959a704a6d877d4
Content-Type: text/plain; charset=ISO-8859-1

Hi all,
I am running PostgreSQL 9.0 on a number of nodes in an application level
cluster (there is different data on different machines). Currently a
PL/pgSQL function generates automatically aggregation queries like the
following:

(select * from appqosfe.F_total_utilization(1306918800000000000::INT8, NULL,
60000000000::INT8, NULL))
UNION ALL
(SELECT * from dblink('remote1','select * from
appqosfe.F_total_utilization(1306918800000000000::INT8, NULL,
60000000000::INT8, NULL)') as T1(detectroid numeric, timegroup numeric,
numbytes numeric, numpackets numeric))
order by timegroup asc

The above example supposes that only 2 nodes are active (one local and one
remote). Here I can clearly see that the remote sub-query starts only when
the local one is completed so the total time grows linearly with the number
of nodes.

Question: Is there a way to get the same result from within a PL/pgSQL
function but running all the sub-queries in parallel? In case it is not
directly available, which one would be the simplest way to implement it in
my application? (I am very keen to avoid the obvious solution of an
additional multi-threaded layer which would do it out of the RDBMS)

Thank you,
Svetlin Manavski

--001517475e62b959a704a6d877d4
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi all,<br>I am running PostgreSQL 9.0 on a number of nodes in an applicati=
on level cluster (there is different data on different machines). Currently=
 a PL/pgSQL function generates automatically aggregation queries like the f=
ollowing:=A0 <br>
<br>(select * from appqosfe.F_total_utilization(1306918800000000000::INT8, =
NULL, 60000000000::INT8, NULL)) <br>UNION ALL <br>(SELECT * from dblink(=
9;remote1&#39;,&#39;select * from appqosfe.F_total_utilization(130691880000=
0000000::INT8, NULL, 60000000000::INT8, NULL)&#39;) as T1(detectroid numeri=
c, timegroup numeric, numbytes numeric, numpackets numeric)) <br>
order by timegroup asc<br><br> The above example supposes that only 2 nodes=
 are active (one local and one remote). Here I can clearly see that the rem=
ote sub-query starts only when the local one is completed so the total time=
 grows linearly with the number of nodes. <br>
<br>Question: Is there a way to get the same result from within a PL/pgSQL =
function but running all the sub-queries in parallel? In case it is not dir=
ectly available, which one would be the simplest way to implement it in my =
application? (I am very keen to avoid the obvious solution of an additional=
 multi-threaded layer which would do it out of the RDBMS)<br>
<br>Thank you,<br>Svetlin Manavski<br><br>

--001517475e62b959a704a6d877d4--