Thread: WITH and WITH RECURSIVE in single query
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.
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.
Maxim Boguk <maxim.boguk@gmail.com> writes:
> Is here any way to combine WITH and WITH RECURSIVE into single query?
You have to put RECURSIVE immediately after WITH, but that doesn't force
you to actually make any particular query in the WITH-list recursive.
It just makes it possible for a query to be self-referential, not required.
regards, tom lane
On Dec 4, 2011, at 22:28, Maxim Boguk <maxim.boguk@gmail.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.
On Mon, Dec 5, 2011 at 2:45 PM, David Johnston <polobo@yahoo.com> wrote:
WITH RECURSIVE q1 As (), q2 AS () ...On Dec 4, 2011, at 22:28, Maxim Boguk <maxim.boguk@gmail.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.
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.
On Mon, Dec 5, 2011 at 2:45 PM, David Johnston <polobo@yahoo.com> wrote:WITH RECURSIVE q1 As (), q2 AS () ...On Dec 4, 2011, at 22:28, Maxim Boguk <maxim.boguk@gmail.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.
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.
Read the documentation closely, the syntax definition for WITH is precise and accurate.
No matter how many queries you want to create you write the word WITH one time. If ANY of your queries require iterative behavior you put the word RECURSIVE after the word WITH. Between individual queries you may only put the name, and optional column alias, along with the required comma.
As a side benefit to adding RECURSIVE the order in which the queries appear is no longer relevant. Without RECURSIVE you indeed must list the queries in order of use.
David J.
On Mon, Dec 5, 2011 at 3:15 PM, David Johnston <polobo@yahoo.com> wrote:
On Mon, Dec 5, 2011 at 2:45 PM, David Johnston <polobo@yahoo.com> wrote:WITH RECURSIVE q1 As (), q2 AS () ...On Dec 4, 2011, at 22:28, Maxim Boguk <maxim.boguk@gmail.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.
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.Read the documentation closely, the syntax definition for WITH is precise and accurate.No matter how many queries you want to create you write the word WITH one time. If ANY of your queries require iterative behavior you put the word RECURSIVE after the word WITH. Between individual queries you may only put the name, and optional column alias, along with the required comma.As a side benefit to adding RECURSIVE the order in which the queries appear is no longer relevant. Without RECURSIVE you indeed must list the queries in order of use.David J.
Thank you very much David.
That work like a charm.
another 30% runtime gone.
--
Maxim Boguk
Senior Postgresql DBA.