Re: [GENERAL] Question about the WITH RECURSIVE patch
Thomas Kellerer wrote: > Bruce Momjian wrote on 20.11.2008 22:56: > >> From http://umitanuki.net/pgsql/wfv08/design.html I got the impression > >> that > >> windowing functions will make into (at least partially) into 8.4 because > >> on that > >> page several items are marked with [DONE]. > >> > >> I can't see anything in the developer docs regarding them. Does that mean > >> they > >> won't make it or that simply the documentation isnt't yet there? > > > > The patch is still being reviewed for inclusion in 8.4. > > > Thanks for the quick reply. > > I'm keeping my fingers crossed, because that is one of the features I'm > really > waiting for :) I think there is a good chance it will be in 8.4; several community members have done a lot to help test it and make sure it is ready for 8.4. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Question about the WITH RECURSIVE patch
Bruce Momjian wrote on 20.11.2008 22:56: From http://umitanuki.net/pgsql/wfv08/design.html I got the impression that windowing functions will make into (at least partially) into 8.4 because on that page several items are marked with [DONE]. I can't see anything in the developer docs regarding them. Does that mean they won't make it or that simply the documentation isnt't yet there? The patch is still being reviewed for inclusion in 8.4. Thanks for the quick reply. I'm keeping my fingers crossed, because that is one of the features I'm really waiting for :) Cheers Thomas -- 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] Question about the WITH RECURSIVE patch
Thomas Kellerer wrote: > Tom Lane wrote on 20.11.2008 22:21: > > "Josh Harrison" <[EMAIL PROTECTED]> writes: > >> My question is when I don't use the Recursive term does the optimizer just > >> consider it as a subquery or does it work like Oracle's WITH CLAUSE > >> (Subquery Factoring) ? Oracle's WITH CLAUSE boosts the performance of the > >> queries. So does this do the same? > > > > See the fine manual, for instance last para here: > > http://developer.postgresql.org/pgdocs/postgres/queries-with.html > > > > Cool :) > > From http://umitanuki.net/pgsql/wfv08/design.html I got the impression that > windowing functions will make into (at least partially) into 8.4 because on > that > page several items are marked with [DONE]. > > I can't see anything in the developer docs regarding them. Does that mean > they > won't make it or that simply the documentation isnt't yet there? The patch is still being reviewed for inclusion in 8.4. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Question about the WITH RECURSIVE patch
Tom Lane wrote on 20.11.2008 22:21: "Josh Harrison" <[EMAIL PROTECTED]> writes: My question is when I don't use the Recursive term does the optimizer just consider it as a subquery or does it work like Oracle's WITH CLAUSE (Subquery Factoring) ? Oracle's WITH CLAUSE boosts the performance of the queries. So does this do the same? See the fine manual, for instance last para here: http://developer.postgresql.org/pgdocs/postgres/queries-with.html Cool :) From http://umitanuki.net/pgsql/wfv08/design.html I got the impression that windowing functions will make into (at least partially) into 8.4 because on that page several items are marked with [DONE]. I can't see anything in the developer docs regarding them. Does that mean they won't make it or that simply the documentation isnt't yet there? Cheers Thomas -- 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] Question about the WITH RECURSIVE patch
Thanks Tom. This is wonderful On Thu, Nov 20, 2008 at 4:21 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Josh Harrison" <[EMAIL PROTECTED]> writes: > > My question is when I don't use the Recursive term does the optimizer > just > > consider it as a subquery or does it work like Oracle's WITH CLAUSE > > (Subquery Factoring) ? Oracle's WITH CLAUSE boosts the performance of the > > queries. So does this do the same? > > See the fine manual, for instance last para here: > http://developer.postgresql.org/pgdocs/postgres/queries-with.html > >regards, tom lane >
Re: [GENERAL] Question about the WITH RECURSIVE patch
On Nov 20, 2008, at 1:21 PM, Tom Lane wrote: See the fine manual, for instance last para here: http://developer.postgresql.org/pgdocs/postgres/queries-with.html Fine manual indeed... this the best explanation of WITH RECURSIVE I've ever read. Kudos to the documentation writer(s). -- 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] Question about the WITH RECURSIVE patch
"Josh Harrison" <[EMAIL PROTECTED]> writes: > My question is when I don't use the Recursive term does the optimizer just > consider it as a subquery or does it work like Oracle's WITH CLAUSE > (Subquery Factoring) ? Oracle's WITH CLAUSE boosts the performance of the > queries. So does this do the same? See the fine manual, for instance last para here: http://developer.postgresql.org/pgdocs/postgres/queries-with.html 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
[GENERAL] Question about the WITH RECURSIVE patch
Hi, I tried the 8.4-devel version and the CTE (Common Table Expression -WITH RECURSIVE ) patch is working pretty good. I just have a question These are the queries & their plan . The first query uses RECURSIVE keyword (and has a recursive and non-recursive term as CTE) while the second query uses only WITH keyword(and has no recursive term) My question is when I don't use the Recursive term does the optimizer just consider it as a subquery or does it work like Oracle's WITH CLAUSE (Subquery Factoring) ? Oracle's WITH CLAUSE boosts the performance of the queries. So does this do the same? 1. explain analyse WITH RECURSIVE subdepartment AS ( -- non-recursive term SELECT * FROM department WHERE name = 'A' UNION ALL -- recursive term SELECT d.*FROM department AS d JOIN subdepartment AS sd ON (d.parent_department = sd.id) ) SELECT * FROM subdepartment QUERY PLAN - CTE Scan on subdepartment (cost=17.57..18.99 rows=71 width=40) (actual time=0.044..0.590 rows=5 loops=1) InitPlan -> Recursive Union (cost=0.00..17.57 rows=71 width=10) (actual time=0.034..0.536 rows=5 loops=1) -> Seq Scan on department (cost=0.00..1.10 rows=1 width=10) (actual time=0.025..0.031 rows=1 loops=1) Filter: (name = 'A'::text) -> Hash Join (cost=0.33..1.51 rows=7 width=10) (actual time=0.080..0.107 rows=1 loops=4) Hash Cond: (d.parent_department = sd.id) -> Seq Scan on department d (cost=0.00..1.08 rows=8 width=10) (actual time=0.004..0.033 rows=8 loops=4) -> Hash (cost=0.20..0.20 rows=10 width=4) (actual time=0.023..0.023 rows=1 loops=4) -> WorkTable Scan on subdepartment sd (cost=0.00..0.20 rows=10 width=4) (actual time=0.004..0.009 rows=1 loops=4) Total runtime: 0.681 ms 2. explain analyse WITH subdepartment AS ( -- non-recursive term SELECT * FROM department WHERE name = 'A' ) SELECT id,name FROM subdepartment QUERY PLAN --- CTE Scan on subdepartment (cost=1.10..1.12 rows=1 width=36) (actual time=0.037..0.050 rows=1 loops=1) InitPlan -> Seq Scan on department (cost=0.00..1.10 rows=1 width=10) (actual time=0.024..0.030 rows=1 loops=1) Filter: (name = 'A'::text) Total runtime: 0.111 ms Thanks Josh
