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