Re: Controlling complexity in queries

On Sun, Dec 11, 2011 at 9:10 PM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> 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