From: | Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> |
---|---|
To: | David Johnston <polobo(at)yahoo(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: WITH and WITH RECURSIVE in single query |
Date: | 2011-12-05 03:58:19 |
Message-ID: | CAK-MWwRq=4tC=xm1T87ZcErAtZd1quJGitMgjqKmqhmEMqWubw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Dec 5, 2011 at 2:45 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> On Dec 4, 2011, at 22:28, Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> wrote:
>
> > Hi.
> >
> > Is here any way to combine WITH and WITH RECURSIVE into single query?
> >
> > Something like:
> >
> > WITH t AS (some complicated select to speed up recursive part),
> > RECURSIVE r AS
> > (
> > ...
> > UNION ALL
> > ...
> > )
> >
> > ?
> >
> > --
> > Maxim Boguk
> > Senior Postgresql DBA.
>
> WITH RECURSIVE q1 As (), q2 AS () ...
>
> Add RECURSIVE after the WITH; it then applies to any/all the CTEs.
>
> Look at the specification (and description) in the SELECT documentation
> closely.
>
> David J.
Trouble is I trying to precalculate some data through WITH syntax (non
recursive).
To be used later in WITH RECURSIVE part (and keep a single of that data
instead of N).
Something like:
WITH _t AS (some complicated select to speed up recursive part),
RECURSIVE r AS
(
...
UNION ALL
SELECT * FROM r
JOIN t ON ...
)
So I need have precalculated t table before I start an iterator.
Now instead of _t I using record[] + unnest but that appoach very memory
hungry for long iterations:
WITH RECURSIVE r AS
(
SELECT ...
ARRAY(SELECT ROW(t.*) FROM some complicated select to speed up
recursive part) as _t_array
FROM ...
UNION ALL
SELECT
...,
_t_array
FROM r
JOIN (unnest(_t_array) ...) ON something
)
However that approach lead to having copy of the _t_array per each final
row, so can use a lot of memory.
PS: Yes I know about pl/pgsql but WITH RECURSIVE iterators can give 2-10
time performance gains over implemenation of the same algorythm inside
pl/pgsql.
--
Maxim Boguk
Senior Postgresql DBA.
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2011-12-05 04:03:48 | Re: Questions about setting an array element value outside of the update |
Previous Message | David Johnston | 2011-12-05 03:45:18 | Re: WITH and WITH RECURSIVE in single query |