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

Re: Transient plans versus the SPI API

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Transient plans versus the SPI API
Date: 2011-08-03 03:12:32
Message-ID: CA+U5nML3einLVpjTmCJp9o1ZhX8zOXFNkKFSh7csavZf_OODrw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, Aug 2, 2011 at 9:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> The most straightforward way to reimplement things within spi.c would be
> to redefine SPI_prepare as just doing the parse-and-rewrite steps, with
> planning always postponed to SPI_execute.  In the case where you just
> prepare and then execute a SPIPlan, this would come out the same or
> better, since we'd still just do one planning cycle, but the planner could
> be given the actual parameter values to use.  However, if you SPI_prepare,
> SPI_saveplan, and then SPI_execute many times, you might come out behind.
> This is of course the same tradeoff we are going to impose at the SQL level
> anyway, but I wonder whether there needs to be a control knob available to
> C code to retain the old plan-once-and-always-use-that-plan approach.

The problems only occur <1% of the time, so this penalises everyone to
avoid real but rare problems.

This will cause a massive loss of performance in most apps, though I
understand the annoyance and why you make the suggestion.

http://www.db2ude.com/?q=node/73 for some more background on how this
is handled elsewhere
Control knob == hint, so I've avoided suggesting such an approach myself.

I think its possible to tell automatically whether we need to replan
always or not based upon the path we take through selectivity
functions.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2011-08-03 03:40:53
Subject: Re: cataloguing NOT NULL constraints
Previous:From: Robert HaasDate: 2011-08-03 02:54:49
Subject: Re: WIP fix proposal for bug #6123

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