Skip site navigation (1) Skip section navigation (2)

pgsql: Invent a "one-shot" variant of CachedPlans for betterperformanc

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-committers(at)postgresql(dot)org
Subject: pgsql: Invent a "one-shot" variant of CachedPlans for betterperformanc
Date: 2013-01-04 22:42:42
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-committerspgsql-hackers
Invent a "one-shot" variant of CachedPlans for better performance.

SPI_execute() and related functions create a CachedPlan, execute it once,
and immediately discard it, so that the functionality offered by
plancache.c is of no value in this code path.  And performance measurements
show that the extra data copying and invalidation checking done by
plancache.c slows down simple queries by 10% or more compared to 9.1.
However, enough of the SPI code is shared with functions that do need plan
caching that it seems impractical to bypass plancache.c altogether.
Instead, let's invent a variant version of cached plans that preserves
99% of the API but doesn't offer any of the actual functionality, nor the
overhead.  This puts SPI_execute() performance back on par, or maybe even
slightly better, than it was before.  This change should resolve recent
complaints of performance degradation from Dong Ye, Pavel Stehule, and

By avoiding data copying, this change also reduces the amount of memory
needed to execute many-statement SPI_execute() strings, as for instance in
a recent complaint from Tomas Vondra.

An additional benefit of this change is that multi-statement SPI_execute()
query strings are now processed fully serially, that is we complete
execution of earlier statements before running parse analysis and planning
on following ones.  This eliminates a long-standing POLA violation, in that
DDL that affects the behavior of a later statement will now behave as

Back-patch to 9.2, since this was a performance regression compared to 9.1.
(In 9.2, place the added struct fields so as to avoid changing the offsets
of existing fields.)

Heikki Linnakangas and Tom Lane



Modified Files
doc/src/sgml/spi.sgml               |   25 +++--
src/backend/executor/spi.c          |  144 +++++++++++++++++++++++---
src/backend/utils/cache/plancache.c |  191 +++++++++++++++++++++++++++++------
src/include/executor/spi_priv.h     |    7 ++
src/include/utils/plancache.h       |   19 +++-
5 files changed, 329 insertions(+), 57 deletions(-)


pgsql-hackers by date

Next:From: Josh BerkusDate: 2013-01-04 23:04:07
Subject: Re: dynamic SQL - possible performance regression in 9.2
Previous:From: Peter GeogheganDate: 2013-01-04 22:37:50
Subject: Re: enhanced error fields

pgsql-committers by date

Next:From: Simon RiggsDate: 2013-01-04 23:31:41
Subject: Re: [COMMITTERS] pgsql: Invent a "one-shot" variant of CachedPlans for better performanc
Previous:From: Tom LaneDate: 2013-01-03 23:35:11
Subject: pgsql: Prevent creation of postmaster's TCP socket during pg_upgradete

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group