pgsql: Improve performance of "simple expressions" in PL/pgSQL.

Previous Topic Next Topic
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

pgsql: Improve performance of "simple expressions" in PL/pgSQL.

Tom Lane-2
Improve performance of "simple expressions" in PL/pgSQL.

For relatively simple expressions (say, "x + 1" or "x > 0"), plpgsql's
management overhead exceeds the cost of evaluating the expression.
This patch substantially improves that situation, providing roughly
2X speedup for such trivial expressions.

First, add infrastructure in the plancache to allow fast re-validation
of cached plans that contain no table access, and hence need no locks.
Teach plpgsql to use this infrastructure for expressions that it's
already deemed "simple" (which in particular will never contain table

The fast path still requires checking that search_path hasn't changed,
so provide a fast path for OverrideSearchPathMatchesCurrent by
counting changes that have occurred to the active search path in the
current session.  This is simplistic but seems enough for now, seeing
that PushOverrideSearchPath is not used in any performance-critical

Second, manage the refcounts on simple expressions' cached plans using
a transaction-lifespan resource owner, so that we only need to take
and release an expression's refcount once per transaction not once per
expression evaluation.  The management of this resource owner exactly
parallels the existing management of plpgsql's simple-expression EState.

Add some regression tests covering this area, in particular verifying
that expression caching doesn't break semantics for search_path changes.

Patch by me, but it owes something to previous work by Amit Langote,
who recognized that getting rid of plancache-related overhead would
be a useful thing to do here.  Also thanks to Andres Freund for review.




Modified Files
src/backend/catalog/namespace.c                |  98 +++++++++--
src/backend/utils/cache/plancache.c            | 154 +++++++++++++++++
src/backend/utils/resowner/resowner.c          |  24 +++
src/include/catalog/namespace.h                |   5 +
src/include/utils/plancache.h                  |   8 +
src/include/utils/resowner.h                   |   1 +
src/pl/plpgsql/src/Makefile                    |   4 +-
src/pl/plpgsql/src/expected/plpgsql_simple.out |  68 ++++++++
src/pl/plpgsql/src/pl_exec.c                   | 225 +++++++++++++++++++------
src/pl/plpgsql/src/pl_handler.c                |  38 +++--
src/pl/plpgsql/src/plpgsql.h                   |  15 +-
src/pl/plpgsql/src/sql/plpgsql_simple.sql      |  61 +++++++
12 files changed, 627 insertions(+), 74 deletions(-)