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
"Josh Harrison" <joshques@gmail.com> 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
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).
Thanks Tom. This is wonderful
On Thu, Nov 20, 2008 at 4:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
"Josh Harrison" <joshques@gmail.com> 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.htmlregards, tom lane
Tom Lane wrote on 20.11.2008 22:21:
"Josh Harrison" <joshques@gmail.com> 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
Thomas Kellerer wrote:
Tom Lane wrote on 20.11.2008 22:21:
"Josh Harrison" <joshques@gmail.com> 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.htmlCool :)
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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
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
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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +