Re: CTE vs Subquery - Mailing list pgsql-performance
From | Linos |
---|---|
Subject | Re: CTE vs Subquery |
Date | |
Msg-id | 4EA7CC40.9000200@linos.es Whole thread Raw |
In response to | Re: CTE vs Subquery (Merlin Moncure <mmoncure@gmail.com>) |
Responses |
Re: CTE vs Subquery
|
List | pgsql-performance |
El 25/10/11 19:11, Merlin Moncure escribió: > On Tue, Oct 25, 2011 at 11:47 AM, Linos <info@linos.es> wrote: >> El 25/10/11 18:43, Tom Lane escribió: >>> Linos <info@linos.es> 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 > ok, i get the idea, but i still don't understand what Tom says about isolate evaluation, apart from the performance and the readability, if i am not using writable CTE or recursive CTE, what it is the difference in evaluation (about being isolate) of a subquery vs CTE with the same text inside. I have been using this form lately: WITH inv (SELECT item_id, SUM(units) AS units FROM invoices), quo AS (SELECT item_id, SUM(units) AS units FROM quotes) SELECT items.item_id, CASE WHEN inv.units IS NOT NULL THEN inv.units ELSE 0 END AS units_invoices, CASE WHEN quo.units IS NOT NULL THEN quo.units ELSE 0 END AS units_quotes FROM items LEFT JOIN inv ON inv.item_id = items.item_id LEFT JOIN quo ON quo.item_id = items.item_id Well this is oversimplified because i use much more tables and filter based on dates, but you get the idea, it seems that this type of query should use subqueries, no? Regards, Miguel Angel.
pgsql-performance by date: