On Mon, Dec 5, 2011 at 3:15 PM, David Johnston <pol...@yahoo.com> wrote:
> On Dec 4, 2011, at 22:58, Maxim Boguk <maxim.bo...@gmail.com> wrote: > > > > On Mon, Dec 5, 2011 at 2:45 PM, David Johnston < <pol...@yahoo.com> > pol...@yahoo.com> wrote: > >> On Dec 4, 2011, at 22:28, Maxim Boguk < <maxim.bo...@gmail.com> >> 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.
