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

Re: Prepared statements considered harmful

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Prepared statements considered harmful
Date: 2006-08-31 12:52:05
Message-ID: 1157028725.3033.29.camel@coppola.muc.ecircle.de (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, 2006-08-31 at 14:32, Peter Eisentraut wrote:
> Am Donnerstag, 31. August 2006 14:11 schrieb Csaba Nagy:
> > How about "prepared" means really "prepared"... in the sense of parsed,
> > analyzed all sensible plans, and save a meta-plan which based on current
> > statistics and parameter values chooses one of the considered (and
> > cached) plans ?
> 
> I don't think this could solve one particularly frequent problem which is that 
> pattern matching queries don't get along with prepared plans if the search 
> pattern isn't known at planning time.

Why not ? I specifically said you would prepare a few sensible plans
based on statistics/expected variations of the statistics, and parameter
value ranges which would trigger different plans. 

So for the like query case you could save 2 plans, one for the indexable
case, one for the not indexable case. Then at runtime you choose the
proper one based on the pattern value. The meta-plan I mentioned would
be a collection of plans with rules to choose the right one at run time
based on parameter values and perhaps the current statistics.

This of course would need a lot more preparation time than just prepare
one plan, but that's why you want to do it upfront and then cache the
results. A central plan repository mentioned in other posts would fit
nicely here... and you could use prepared plans for non-parameterized
queries too by simply considering the constants as parameters, to
increase the chances for a prepared plan reuse - this of course for
complex enough queries.

Cheers,
Csaba.


In response to

Responses

pgsql-hackers by date

Next:From: Merlin MoncureDate: 2006-08-31 13:05:23
Subject: Re: Prepared statements considered harmful
Previous:From: Teodor SigaevDate: 2006-08-31 12:46:23
Subject: Re: GIN FailedAssertions on Itanium2 with Intel compiler

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