Thread: 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

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.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  <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. +