Re: Views versus user-defined functions: formatting, comments, performance, etc.

On 08/18/2012 06:49 AM, Merlin Moncure wrote:
> For various reasons, this often goes the wrong way. Views are often
> the right way to go.

Indeed. I've had queries speed up *hundreds* of times when I convert a
function the planner didn't seem to want to inline into a view it can
push conditions down into.

The key thing to remember with views is that - unlike CTE "WITH"
expressions - they generally aren't fully evaluated to get all their
rows if most of them aren't needed. The query optimiser can typically
push filters (like "where customer_id = 4" or whatever) down into the
index- and table-scans used by the view, reducing the amount of data
that has to be processed.

That's not always the case, so use of EXPLAIN ANALYZE and some tweaking
of a view or query that uses a view is sometimes necessary. Mostly it
"just works" though.

--
Craig Ringer