Re: CTE vs Subquery

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Oct 25, 2011 at 11:47 AM, Linos <info@xxxxxxxx> wrote:
> El 25/10/11 18:43, Tom Lane escribió:
>> Linos <info@xxxxxxxx> writes:
>>>     i am having any problems with performance of queries that uses CTE, can the
>>> join on a CTE use the index of the original table?
>>
>> CTEs act as optimization fences.  This is a feature, not a bug.  Use
>> them when you want to isolate the evaluation of a subquery.
>>
>>                       regards, tom lane
>>
>
> The truth it is that complex queries seems more readable using them (maybe a
> personal preference no doubt).
>
> Do have other popular databases the same behavior? SQL Server or Oracle for example?

In my experience, SQL server also materializes them -- basically CTE
is short hand for 'CREATE TEMP TABLE foo AS SELECT...' then joining to
foo.  If you want join behavior, use a join (by the way IIRC SQL
Server is a lot more restrictive about placement of ORDER BY).

I like CTE current behavior -- the main place I find it awkward is in
use of recursive queries because the CTE fence forces me to abstract
the recursion behind a function, not a view since pushing the view
qual down into the CTE is pretty horrible:

postgres=# explain select foo.id, (with bar as (select id from foo f
where f.id = foo.id) select * from bar) from foo where foo.id = 11;
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Index Scan using foo_idx on foo  (cost=0.00..16.57 rows=1 width=4)
   Index Cond: (id = 11)
   SubPlan 2
     ->  CTE Scan on bar  (cost=8.28..8.30 rows=1 width=4)
           CTE bar
             ->  Index Scan using foo_idx on foo f  (cost=0.00..8.28
rows=1 width=4)
                   Index Cond: (id = $0)
(7 rows)

whereas for function you can inject your qual inside the CTE pretty
easily.  this is a different problem than the one you're describing
though.  for the most part, CTE execution fence is a very good thing,
since it enforces restrictions that other features can leverage, for
example 'data modifying with' queries (by far my all time favorite
postgres enhancement).

merlin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux