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
