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: E1TrFyM-0003QW-4L@gemulon.postgresql.org (view raw or flat)
Thread:
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
others.

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

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

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/94afbd5831fbc1926f1c367ac14a45ccc29d313d

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


Responses

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-2014 The PostgreSQL Global Development Group