I've been thinking about how to redesign the plancache infrastructure to
better support use of transient (one-shot) plans, as we've talked about
various times such as in this thread:
http://archives.postgresql.org/pgsql-hackers/2010-02/msg00607.php
(Note: that thread sorta went off into the weeds a
Tom Lane 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
On Tue, Aug 2, 2011 at 4:47 PM, Tom Lane wrote:
> I've been thinking about how to redesign the plancache infrastructure to
> better support use of transient (one-shot) plans, as we've talked about
> various times such as in this thread:
> http://archives.postgresql.org/pgsql-hackers/2010-02/msg006
On Tue, Aug 2, 2011 at 9:47 PM, Tom Lane 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
Tom Lane writes:
> Anyone have an opinion about that?
I still have this application where PREPARE takes between 50ms and 300ms
and EXECUTE 5ms to 10ms, and I can handle 1 PREPARE for 1 EXECUTE
quite easily. (Yes the database fits in RAM, and yes when that's no
longer the case we just upgrade
Dimitri Fontaine writes:
> Tom Lane writes:
>> Anyone have an opinion about that?
> I still have this application where PREPARE takes between 50ms and 300ms
> and EXECUTE 5ms to 10ms, and I can handle 1 PREPARE for 1 EXECUTE
> quite easily. (Yes the database fits in RAM, and yes when that's
On Wed, Aug 3, 2011 at 12:19 PM, Tom Lane wrote:
> Dimitri Fontaine writes:
>> Tom Lane writes:
>>> Anyone have an opinion about that?
>
>> I still have this application where PREPARE takes between 50ms and 300ms
>> and EXECUTE 5ms to 10ms, and I can handle 1 PREPARE for 1 EXECUTE
>> quite e
Tom Lane writes:
> So yes, it'd get a little worse for that use-case. But you have to
> weigh that against the likelihood that other use-cases will get better.
> If our requirement for a transient-plan mechanism is that no individual
> case can ever be worse than before, then we might as well aba
Robert Haas writes:
> This seems like a good design. Now what would be really cool is if
> you could observe a stream of queries like this:
> SELECT a, b FROM foo WHERE c = 123
> SELECT a, b FROM foo WHERE c = 97
> SELECT a, b FROM foo WHERE c = 236
> ...and say, hey, I could just make a generi
On Wed, Aug 3, 2011 at 3:19 PM, Tom Lane wrote:
> Robert Haas writes:
>> This seems like a good design. Now what would be really cool is if
>> you could observe a stream of queries like this:
>
>> SELECT a, b FROM foo WHERE c = 123
>> SELECT a, b FROM foo WHERE c = 97
>> SELECT a, b FROM foo WHE
Simon Riggs writes:
> I think its possible to tell automatically whether we need to replan
> always or not based upon the path we take through selectivity
> functions.
I don't really believe that, or at least I think it would only detect a
few cases. Examples of parameter-value-sensitive decisio
On Wed, Aug 3, 2011 at 8:33 PM, Tom Lane wrote:
> Simon Riggs writes:
>> I think its possible to tell automatically whether we need to replan
>> always or not based upon the path we take through selectivity
>> functions.
>
> I don't really believe that, or at least I think it would only detect a
On 2011-08-03 21:19, Tom Lane wrote:
Robert Haas writes:
This seems like a good design. Now what would be really cool is if
you could observe a stream of queries like this:
SELECT a, b FROM foo WHERE c = 123
SELECT a, b FROM foo WHERE c = 97
SELECT a, b FROM foo WHERE c = 236
...and say, hey,
On tis, 2011-08-02 at 16:47 -0400, Tom Lane 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
Peter Eisentraut writes:
> How about a new function like SPI_parse that has the new semantics?
Yeah, I'd considered that idea (and even exactly that name for it).
Howver, the disadvantage of inventing a separate entry point is that
it isn't going to be nice for multi-level call chains, of which t
On Tue, 2011-08-02 at 16:47 -0400, Tom Lane 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
On Wed, 2011-08-03 at 12:19 -0400, Tom Lane wrote:
> Of course we could address the worst cases by providing some mechanism
> to tell the plancache code "always use a generic plan for this query"
> or "always use a custom plan". I'm not entirely thrilled with that,
> because it's effectively a pla
On Wed, 2011-08-03 at 13:07 -0400, Robert Haas wrote:
> A little OT here, but (as I think Simon said elsewhere) I think we
> really ought to be considering the table statistics when deciding
> whether or not to replan. It seems to me that the overwhelmingly
> common case where this is going to com
Tom Lane writes:
> I think we'll be a lot better off with the framework discussed last
> year: build a generic plan, as well as custom plans for the first few
> sets of parameter values, and then observe whether there's a significant
> reduction in estimated costs for the custom plans.
Another wa
Jeff Davis writes:
> A control knob sounds limited. For instance, what if the application
> knows that some parameters will be constant over the time that the plan
> is saved? It would be nice to be able to bind some parameters to come up
> with a generic (but less generic) plan, and then execute
On Wed, 2011-08-03 at 15:19 -0400, Tom Lane wrote:
> Robert Haas writes:
> > This seems like a good design. Now what would be really cool is if
> > you could observe a stream of queries like this:
>
> > SELECT a, b FROM foo WHERE c = 123
> > SELECT a, b FROM foo WHERE c = 97
> > SELECT a, b FROM
On Sun, 2011-08-07 at 11:15 +0200, Hannu Krosing wrote:
> On Wed, 2011-08-03 at 15:19 -0400, Tom Lane wrote:
> > Hm, you mean reverse-engineering the parameterization of the query?
>
> Yes, basically re-generate the query after (or while) parsing, replacing
> constants and arguments with another
On Sat, Aug 6, 2011 at 7:29 PM, Dimitri Fontaine wrote:
> Tom Lane writes:
>> I think we'll be a lot better off with the framework discussed last
>> year: build a generic plan, as well as custom plans for the first few
>> sets of parameter values, and then observe whether there's a significant
>>
On 08/07/2011 12:25 PM, Hannu Krosing wrote:
On Sun, 2011-08-07 at 11:15 +0200, Hannu Krosing wrote:
On Wed, 2011-08-03 at 15:19 -0400, Tom Lane wrote:
Hm, you mean reverse-engineering the parameterization of the query?
Yes, basically re-generate the query after (or while) parsing, replacing
c
On Mon, 2011-08-08 at 11:39 +0300, Anssi Kääriäinen wrote:
> On 08/07/2011 12:25 PM, Hannu Krosing wrote:
> > On Sun, 2011-08-07 at 11:15 +0200, Hannu Krosing wrote:
> >> On Wed, 2011-08-03 at 15:19 -0400, Tom Lane wrote:
> >>> Hm, you mean reverse-engineering the parameterization of the query?
> >
On 08/08/2011 01:07 PM, Hannu Krosing wrote:
That is why I think it is best done in the main parser - it has to parse
and analyse the query anyway and likely knows which constants are
"arguments" to the query
As far as I understand the problem, the parsing must transform table
references to sche
Hannu Krosing writes:
>> Hm, you mean reverse-engineering the parameterization of the query?
>
> Yes, basically re-generate the query after (or while) parsing, replacing
> constants and arguments with another set of generated arguments and
> printing the list of these arguments at the end. It may
Tom Lane wrote:
> > Note that the SPI functions are more or less directly exposed in PL/Perl
> > and PL/Python, and there are a number of existing idioms there that make
> > use of prepared plans. Changing the semantics of those functions might
> > upset a lot of code.
>
> Right, but by the same
[ getting back to the planner finally ]
Simon Riggs writes:
> On Wed, Aug 3, 2011 at 8:33 PM, Tom Lane wrote:
>> Simon Riggs writes:
>>> I think its possible to tell automatically whether we need to replan
>>> always or not based upon the path we take through selectivity
>>> functions.
>> I do
On Fri, Aug 19, 2011 at 6:13 PM, Tom Lane wrote:
> [ getting back to the planner finally ]
>
> Simon Riggs writes:
>> On Wed, Aug 3, 2011 at 8:33 PM, Tom Lane wrote:
>>> Simon Riggs writes:
I think its possible to tell automatically whether we need to replan
always or not based upon t
30 matches
Mail list logo
