Question about the WITH RECURSIVE patch

Поиск
Список
Период
Сортировка
От Josh Harrison
Тема Question about the WITH RECURSIVE patch
Дата
Msg-id 8d89ea1d0811201310h14df40dcra1a97f7e328676d8@mail.gmail.com
обсуждение исходный текст
Ответы Re: Question about the WITH RECURSIVE patch  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: wstrzalka
Дата:
Сообщение: Sorting JTA survey results
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Question about the WITH RECURSIVE patch