Re: WITH and WITH RECURSIVE in single query

On Mon, Dec 5, 2011 at 3:15 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:

> On Dec 4, 2011, at 22:58, Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> wrote:
>
>
>
> On Mon, Dec 5, 2011 at 2:45 PM, David Johnston < <polobo(at)yahoo(dot)com>
> polobo(at)yahoo(dot)com> wrote:
>
>> On Dec 4, 2011, at 22:28, Maxim Boguk < <maxim(dot)boguk(at)gmail(dot)com>
>> 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.
>
>
> 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.