Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc? - Mailing list pgsql-performance

From Tom Lane
Subject Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
Date
Msg-id 29918.1320244719@sss.pgh.pa.us
Whole thread Raw
In response to Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?  (Jay Levitt <jay.levitt@gmail.com>)
Responses Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?  (Robert Haas <robertmhaas@gmail.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!
Next
From: Yeb Havinga
Date:
Subject: Re: Intel 710 pgbench write latencies