On Sun, Dec 11, 2011 at 9:10 PM, Craig Ringer <ringerc@xxxxxxxxxxxxx> wrote: > On 12/12/2011 09:15 AM, David Johnston wrote: >> >> Use a WITH clause on the SELECT statement. > > Note that WITH is an optimisation fence, so if you're relying on Pg pushing > WHERE clauses down into subqueries or anything like that you may find that > your query runs a LOT slower when broken up as WITH expressions. > > There's been talk of a Pg extension that allows optimisation through WITH, > but it's not currently possible. > > Another option is to wrap things up in SQL functions or views. A note about that: abstracting via views vs functions is a completely different approach. Views will not significantly change the way your query works now -- they are inlined as macros and the final query is going to be more or less the same as your hand rolled one. Breaking your large queries into functions OTOH can make significant changes to the plan, often to the worse. This is because functions, especially complicated plpgsql set returning ones with procedural logic, are black boxes to the sql optimizer. The upshot of this is that functions tend to encourage nestloop style plans because the function has to be serially executed. Functions (also WITH) are great in that they can provide very high levels of abstraction when composing complex queries, but there is a price in the sense that you are taking away some of the database's ability to plan and optimize the query. I prefer views unless there is a good reason not to use them. In the end, the performance of your queries is going to be directly related to how well you map the problem into relational logic...the database thinks relationally, so you (the OP) should learn to do so as well. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
