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). Whenever I give a talk on the query optimizer, I'm constantly telling people to take logic out of functions and inline it, avoid CTEs, and generally merge everything into one big query. But as the OP says, that is decidedly less than ideal from a code-beauty-and-maintenance point of view: people WANT to be able to use syntactic sugar and still get good performance. Allowing for the insertion of optimization fences is good and important but it needs to be user-controllable behavior. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
