Question about the WITH RECURSIVE patch

From: "Josh Harrison" <joshques(at)gmail(dot)com>
To: "General postgres mailing list" <pgsql-general(at)postgresql(dot)org>
Subject: Question about the WITH RECURSIVE patch
Date: 2008-11-20 21:10:04
Message-ID: 8d89ea1d0811201310h14df40dcra1a97f7e328676d8@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-11-20 21:21:04 Re: Question about the WITH RECURSIVE patch
Previous Message Bruce Momjian 2008-11-20 21:06:49 Re: [GENERAL] db_user_namespace, md5 and changing passwords