Avoid taking a new snapshot for an immutable simple expression in plpgsql

Enterprise / PostgreSQL - Tom Lane [sss.pgh.pa.us] - 22 November 2019 20:02 UTC

We already used this optimization if the plpgsql function is read-only. But it seems okay to do it even in a read-write function, if the expression contains only immutable functions/operators. There would only be a change of behavior if an "immutable" called function depends on seeing database updates made during the current plpgsql function. That's enough of a violation of the promise of immutability that anyone who complains won't have much of a case.

The benefits are significant --- for simple cases like

while i < 10000000 loop i := i + 1; end loop;

I see net performance improvements around 45%. Of course, real-world cases won't get that much faster, but it ought to be noticeable. At the very least, this removes much of the performance penalty that used to exist for forgetting to mark a plpgsql function non-volatile.

Konstantin Knizhnik, reviewed by Pavel Stehule, cosmetic changes by me

Discussion: https://postgr.es/m/ed9da20e-01aa-d04b-d085-e6c16b14b9d7@postgrespro.ru

73b06cf893 Avoid taking a new snapshot for an immutable simple expression in plpgsql.
src/pl/plpgsql/src/pl_exec.c | 20 +++++++++++++++-----
src/pl/plpgsql/src/plpgsql.h | 1 +
2 files changed, 16 insertions(+), 5 deletions(-)

Upstream: git.postgresql.org

  • Share