Re: dynamically generated SQL and planner/performance

Ivan Sergio Borgonovo wrote:
> On Fri, 21 Mar 2008 12:15:19 +0100
> "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
>
> > The execution plan of dynamic queries from PL/pgSQL will not be
> > cached; the query will be prepared at execution time every time you
> > execute it.
>
> Pardon my ignorance but I really have a very vague idea of what the
> planner does and how it works.
>
> Does the planner "compose" plans or does it have one plan for each
> statement?

The planner takes a query and creates a plan. Later, the executor takes
a plan and executes it, producing results. For example you can create
plans with the PREPARE command, and later execute them with the EXECUTE
command (note that the EXECUTE SQL command is different from the EXECUTE
plpgsql command).

PL/pgSQL can present a query to the planner, and get a plan. This plan
can be cached by PL/pgSQL. So if you execute a function twice, the
first time PL/pgSQL caches the plan and passes it to the executor; the
second time PL/pgSQL gets the plan from the cache and passes it to the
executor.

Except if you use plpgsql's EXECUTE: when you do that, PL/pgSQL does no
caching at all for that query, and asks the planner for a new plan each
time.

Also, new in 8.3 is a facility for "plan invalidation", which means that
every time the server executes something that makes that plan stale, the
cache is dropped and the plan is rebuilt next time.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.