Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jay Levitt <jay(dot)levitt(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
Date: 2011-11-02 14:38:39
Message-ID: 29918.1320244719@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Jay Levitt <jay(dot)levitt(at)gmail(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Yeb Havinga 2011-11-02 15:04:30 Re: Intel 710 pgbench write latencies
Previous Message Tom Lane 2011-11-02 14:26:50 Re: Performance Problem with postgresql 9.03, 8GB RAM, Quadcore Processor Server--Need help!!!!!!!