Re: [GENERAL] WITH and WITH RECURSIVE in single query
On Mon, Dec 5, 2011 at 3:15 PM, David Johnston wrote: > On Dec 4, 2011, at 22:58, Maxim Boguk wrote: > > > > On Mon, Dec 5, 2011 at 2:45 PM, David Johnston < > pol...@yahoo.com> wrote: > >> On Dec 4, 2011, at 22:28, Maxim Boguk < >> maxim.bo...@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. > > > 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.
Re: [GENERAL] WITH and WITH RECURSIVE in single query
On Dec 4, 2011, at 22:58, Maxim Boguk wrote: > > > On Mon, Dec 5, 2011 at 2:45 PM, David Johnston wrote: > On Dec 4, 2011, at 22:28, Maxim Boguk 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.
Re: [GENERAL] WITH and WITH RECURSIVE in single query
On Mon, Dec 5, 2011 at 2:45 PM, David Johnston wrote: > On Dec 4, 2011, at 22:28, Maxim Boguk 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.
Re: [GENERAL] WITH and WITH RECURSIVE in single query
On Dec 4, 2011, at 22:28, Maxim Boguk 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WITH and WITH RECURSIVE in single query
Maxim Boguk 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
