Re: functions and execution plan caching

On Thu, Oct 06, 2005 at 08:17:54AM -0500, Kelly Burkhart wrote:
> Given:
> - Processes which are connected to the database for long periods of time
> (transactions are always short).
> - These processes will use some functions to query data.
> - Lots of data is being inserted into tables that these functions query.
> - Vacuums are done frequently.
>
> Am I at risk of degrading performance after some time due to stale
> execution plans?

Yes, because plans are chosen based on the statistics that were
current when the function was first called. For example, if a
sequential scan made sense when you first called the function, then
subsequent calls will also use a sequential scan. You can see this
for yourself with a simple test: create a table, populate it with
a handful of records, and call a function that issues a query that
can (but won't necessarily) use an index. Then add a lot of records
to the table and call the function again. You'll probably notice
that the function runs slower than the same query run from outside
the function, and that the function runs fast if you recreate it
or call it in a new session.

If you set debug_print_plan to on and client_min_messages to debug1,
then you'll see the plan that the function chose (but only on the
first call to the function). If you have statistics enabled, then
you can query pg_stat_user_tables and pg_stat_user_indexes to see
whether subsequent calls use sequential or index scans (this should
be done when nobody else is querying the table so the statistics
represent only what you did).

You can avoid cached plans by using EXECUTE. You'll have to run
tests to see whether the potential gain is worth the overhead.

--
Michael Fuhr