On Wednesday 02 Nov 2011 16:13:09 Robert Haas wrote: > On Wed, Nov 2, 2011 at 10:38 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > Jay Levitt <jay.levitt@xxxxxxxxx> writes: > >> So you can see where I'm going. I know if I break everything into > >> elegant, composable functions, it'll continue to perform poorly. If I > >> write one big hairy, it'll perform great but it will be difficult to > >> maintain, and it will be inelegant and a kitten will die. My tools > >> are CTEs, subqueries, aliases, SQL functions, PL/pgSQL functions, and > >> views (and other tools?) What optimizations do each of those prevent? > > > > plpgsql functions are black boxes to the optimizer. If you can express > > your functions as single SQL commands, using SQL-language functions is > > usually a better bet than plpgsql. > > > > CTEs are also treated as optimization fences; this is not so much an > > optimizer limitation as to keep the semantics sane when the CTE contains > > a writable query. > > I wonder if we need to rethink, though. We've gotten a number of > reports of problems that were caused by single-use CTEs not being > equivalent - in terms of performance - to a non-CTE formulation of the > same idea. It seems necessary for CTEs to behave this way when the > subquery modifies data, and there are certainly situations where it > could be desirable otherwise, but I'm starting to think that we > shouldn't do it that way by default. Perhaps we could let people say > something like WITH x AS FENCE (...) when they want the fencing > behavior, and otherwise assume they don't (but give it to them anyway > if there's a data-modifying operation in there). +1. I avoid writing CTEs in many cases where they would be very useful just for that reasons. I don't even think some future inlining necessarily has to be restricted to one-use cases only... +1 for making fencing behaviour as well. Currently there is no real explicit method to specify this which is necessarily future proof (WITH, OFFSET 0)... Andres -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
