From: Tom Lane <tgl@sss.pgh.pa.us>
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?
Date: Wed, 02 Nov 2011 10:38:39 -0400
Message-ID: <29918.1320244719@sss.pgh.pa.us> (raw)
In-Reply-To: <CANSg5mqt+7j1SU2Gu2tZ_c_vdzCJPkymxEqaQ-OjRXfz=Pfimg@mail.gmail.com>
References: <CANSg5mqt+7j1SU2Gu2tZ_c_vdzCJPkymxEqaQ-OjRXfz=Pfimg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, jay.levitt@gmail.com
Subject: Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
In-Reply-To: <29918.1320244719@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox