To: Jay Levitt <jay.levitt@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Guide to PG's capabilities for inlining, predicate hoisting,
	flattening, etc?
In-reply-to: 
 <CANSg5mqt+7j1SU2Gu2tZ_c_vdzCJPkymxEqaQ-OjRXfz=Pfimg@mail.gmail.com>
References: 
 <CANSg5mqt+7j1SU2Gu2tZ_c_vdzCJPkymxEqaQ-OjRXfz=Pfimg@mail.gmail.com>
Comments: In-reply-to Jay Levitt <jay.levitt@gmail.com>
	message dated "Wed, 02 Nov 2011 10:22:21 -0400"
Date: Wed, 02 Nov 2011 10:38:39 -0400
Message-ID: <29918.1320244719@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Jay Levitt <jay.levitt@gmail.com> 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.

			regards, tom lane