pgsql: Improve performance of "simple expressions" in PL/pgSQL. - Mailing list pgsql-committers

From Tom Lane
Subject pgsql: Improve performance of "simple expressions" in PL/pgSQL.
Date
Msg-id E1jHbSv-00031x-G8@gemulon.postgresql.org
Whole thread Raw
List pgsql-committers
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
references).

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
cases.

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.

Discussion: https://postgr.es/m/CAFj8pRDRVfLdAxsWeVLzCAbkLFZhW549K+67tpOc-faC8uH8zw@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/8f59f6b9c0376173a072e4fb7de1edd6a26e6b52

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(-)


pgsql-committers by date:

Previous
From: Tom Lane
Date:
Subject: pgsql: Ensure that plpgsql cleans up cleanly during parallel-worker exi
Next
From: Peter Eisentraut
Date:
Subject: pgsql: Update SQL features