pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedfunctions and execution plan caching
2+ messages / 2 participants
[nested] [flat]
* functions and execution plan caching
@ 2005-10-06 13:17 Kelly Burkhart <kelly@tradebotsystems.com>
2005-10-06 19:46 ` Re: functions and execution plan caching Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 2+ messages in thread
From: Kelly Burkhart @ 2005-10-06 13:17 UTC (permalink / raw)
To: pgsql-performance
I am working on a system which will be heavily dependent on functions
(some SQL, some PL/pgSQL). I am worried about the backend caching query
execution plans for long running connections.
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?
Thanks,
-Kelly
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: functions and execution plan caching
2005-10-06 13:17 functions and execution plan caching Kelly Burkhart <kelly@tradebotsystems.com>
@ 2005-10-06 19:46 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2005-10-06 19:46 UTC (permalink / raw)
To: Kelly Burkhart <kelly@tradebotsystems.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-10-06 19:46 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-06 13:17 functions and execution plan caching Kelly Burkhart <kelly@tradebotsystems.com>
2005-10-06 19:46 ` Michael Fuhr <mike@fuhr.org>
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