Ivan Sergio Borgonovo wrote: > On Fri, 21 Mar 2008 12:15:19 +0100 > "Albe Laurenz" <laurenz.albe@xxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
