On Fri, Sep 01, 2006 at 10:18:37AM -0400, Tom Lane wrote:
> Martijn van Oosterhout writes:
> >> The server has to prepare the query sometime. The v3 protocol just gives
> >> you
> >> control over when that happens, but it doesn't force you to do it at any
> >> particular time.
>
> > Not really.
On Fri, Sep 01, 2006 at 04:14:32PM +0100, Gregory Stark wrote:
> >> Interesting thought. It might be worth trying. But my big question: is
> >> all this testing and counting actually going to be faster than just
> >> replanning? Postgresql's planner is not that slow.
> >
> > In the best case (which
On Fri, September 1, 2006 22:14, Gregory Stark wrote:
> I think the slow part is trying to figure out whether to count the current
> call as a hit or a miss. How do you determine whether the plan you're
> running
> is the best plan without replanning the query?
The question of knowing which plan
On Fri, Sep 01, 2006 at 10:18:37AM -0400, Tom Lane wrote:
> Sure, but how much does it really matter? If you don't want the plan
> saved for reuse, merely avoiding retransmission of the query text does
> not seem like a major win. Having had time to think about it, I no
> longer think the protoco
On Fri, September 1, 2006 21:30, Tom Lane wrote:
> Yeah. One of the reasons the planner is acceptably fast is that it is
> aggressive about discarding candidate plans as soon as they are clearly
> inferior to other plans. Tracking multiple plans that might be optimal
> under varying assumptions
"Jeroen T. Vermeulen" <[EMAIL PROTECTED]> writes:
> On Fri, September 1, 2006 16:53, Martijn van Oosterhout wrote:
>
>> Interesting thought. It might be worth trying. But my big question: is
>> all this testing and counting actually going to be faster than just
>> replanning? Postgresql's planner
Martijn van Oosterhout writes:
> Interesting thought. It might be worth trying. But my big question: is
> all this testing and counting actually going to be faster than just
> replanning? Postgresql's planner is not that slow.
> ...
> The thing is that number of possible plans is going to be propo
On Fri, Sep 01, 2006 at 11:53:11AM +0200, Martijn van Oosterhout wrote:
> On Fri, Sep 01, 2006 at 03:56:19PM +0700, Jeroen T. Vermeulen wrote:
> > That's a very common thing in processor design as well, and there's a
> > standard trick for it: the saturating two-bit counter. It tends to work
> > p
Martijn van Oosterhout writes:
>> The server has to prepare the query sometime. The v3 protocol just gives you
>> control over when that happens, but it doesn't force you to do it at any
>> particular time.
> Not really. All named prepares are planned straight away, all unnamed
> ones are planned
On Fri, September 1, 2006 16:53, Martijn van Oosterhout wrote:
> Interesting thought. It might be worth trying. But my big question: is
> all this testing and counting actually going to be faster than just
> replanning? Postgresql's planner is not that slow.
In the best case (which of course woul
> > I don't chime in very often, but I do think the refusal to
incorporate
> > hints into the planner system is fantastically stubborn and
> > nonsensical.
>
> What is actually fantastically nonsensical about this is that
> the issues I outlined about prepared statements would merely
> become
> Anyway, your plan assumes that you have information to work
> with. The current system plans prepared queries with no
> information at all about parameters and people are advocating
> to keep it that way. I think a good first step would be the
> plan on first execution, like Oracle does.
Yu
On Fri, Sep 01, 2006 at 03:56:19PM +0700, Jeroen T. Vermeulen wrote:
> That's a very common thing in processor design as well, and there's a
> standard trick for it: the saturating two-bit counter. It tends to work
> pretty well for branch prediction, value prediction etc. Usually it's the
> firs
On Thu, August 31, 2006 21:41, Phil Frost wrote:
>> Is there any kind of pattern at all to this problem? Anything
>> recognizable? A few typical pitfalls?
>
> Frequently I have found preplanning will result in a horrible plan
> because it is assumed parameters may be volatile while in practice t
On Fri, Sep 01, 2006 at 09:26:24AM +0200, Lukas Kahwe Smith wrote:
> AFAIK since Oracle 9i planning is always deferred until the first
> execution. This way they hope to get a better plan, which would
> obviously not be possible if the selectivity varies greatly.
Interesting.
> So are the plans
> >>> I'd wish that we reconsider when and how prepared statements are
> >>> used. The JDBC interface and PL/pgSQL are frequently noticed
> >>> perpetrators, but the problem is really all over the place.
> >> AFAIK those are the only two places where preparation is
> the default
> >> ... what
Martijn van Oosterhout wrote:
On Thu, Aug 31, 2006 at 07:04:07PM -0400, Gregory Stark wrote:
The server has to prepare the query sometime. The v3 protocol just gives you
control over when that happens, but it doesn't force you to do it at any
particular time.
Not really. All named prepares are
On Thu, Aug 31, 2006 at 07:04:07PM -0400, Gregory Stark wrote:
> The server has to prepare the query sometime. The v3 protocol just gives you
> control over when that happens, but it doesn't force you to do it at any
> particular time.
Not really. All named prepares are planned straight away, all
Martijn van Oosterhout writes:
> > Then you would be going very much against the user's expectations.
> >
> > Driver interfaces expose very clearly to the user an explicit interface to
> > prepare and execute a query separately. What your proposing is to go behind
> > the user's back and do wh
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Gregory Stark wrote:
>
> Let's verify that. JDBC and PL/pgSQL have been mentioned.
>
> The JDBC documentation merely contains statements of the sort "A SQL
> statement with or without IN parameters can be pre-compiled and stored
> in a PreparedStat
[EMAIL PROTECTED] writes:
> Does Oracle automatically regenerate prepared plans on occasion?
Not due to statistics changes, only if your schema changes.
(caveat: I last used Oracle back at 8i)
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(
Martijn van Oosterhout writes:
> So what are the options now? A GUC like so:
> prepare_means_plan = [true|false]
> So then a prepare will always parse straightaway, but you can choose
> whether or not you want to plan straightaway or at bind time.
That seems like just a kluge, as you'd typically
Ühel kenal päeval, N, 2006-08-31 kell 20:01, kirjutas Peter Eisentraut:
> For PL/pgSQL, you simply write a query and all the preparing action
> happens implicitly. There is nothing explicit about that interface.
>
> So if users have certain expectations here, they're just making them up.
Or bas
On 31-Aug-06, at 2:58 PM, Tom Lane wrote:
Peter Eisentraut <[EMAIL PROTECTED]> writes:
Gregory Stark wrote:
Driver interfaces expose very clearly to the user an explicit
interface to prepare and execute a query separately.
The JDBC documentation merely contains statements of the sort "A SQ
On Thu, Aug 31, 2006 at 02:16:32PM -0400, Andrew Dunstan wrote:
> According to the docs you can actually choose between server side
> prepare or not on a per call basis. It contains this example:
Yeah, but it also contains this:
Using prepared statements is in theory quite a bit faster: not
On Thu, Aug 31, 2006 at 02:58:48PM -0400, Tom Lane wrote:
> The protocol does let you use OOL parameters without retaining a
> prepared plan, thanks to the hack introduced later to not plan the
> unnamed statement at Parse time, but that's definitely a bit of a wart
> on the original protocol desig
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Gregory Stark wrote:
>> Driver interfaces expose very clearly to the user an explicit
>> interface to prepare and execute a query separately.
> The JDBC documentation merely contains statements of the sort "A SQL
> statement with or without IN parame
On Thu, 2006-08-31 at 10:09 -0400, Theo Schlossnagle wrote:
> On Aug 31, 2006, at 9:25 AM, Peter Eisentraut wrote:
>
> > Am Donnerstag, 31. August 2006 15:05 schrieb Merlin Moncure:
> >> The proposal to supply hints to statements and functions has been
> >> voted down several times due to the argu
Martijn van Oosterhout wrote:
On Thu, Aug 31, 2006 at 06:43:38PM +0100, Gregory Stark wrote:
Peter Eisentraut <[EMAIL PROTECTED]> writes:
- Redefine "prepared" to mean "parsed" rather than "parsed and planned".
Then you would be going very much against the user's expectations.
On Thu, Aug 31, 2006 at 06:43:38PM +0100, Gregory Stark wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > - Redefine "prepared" to mean "parsed" rather than "parsed and planned".
>
> Then you would be going very much against the user's expectations.
>
> Driver interfaces expose very clea
Gregory Stark wrote:
> Then you would be going very much against the user's expectations.
>
> Driver interfaces expose very clearly to the user an explicit
> interface to prepare and execute a query separately. What your
> proposing is to go behind the user's back and do what he's gone out
> of his
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> - Redefine "prepared" to mean "parsed" rather than "parsed and planned".
Then you would be going very much against the user's expectations.
Driver interfaces expose very clearly to the user an explicit interface to
prepare and execute a query sepa
On 8/31/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote:
Am Donnerstag, 31. August 2006 15:05 schrieb Merlin Moncure:
> The proposal to supply hints to statements and functions has been
> voted down several times due to the argument that it is better to fix
> the planner. I think supplying hints
On Thu, Aug 31, 2006 at 06:34:45PM +0200, Lukas Kahwe Smith wrote:
> BTW: PDO has gotten a switch to force client side placeholder
> replacement in favor of using server side prepared statements due to the
> fact that prepared statements side-step the MySQL query cache.
Perl DBD:Pg also has a sw
AgentM wrote:
> On Aug 31, 2006, at 12:04 , Tom Lane wrote:
> > The two major complaints that I've seen are
>
>
>
> > Neither of these problems have anything to do with statistics
> > getting stale.
>
> Not stats-- plans. Plan invalidation has been discussed before, no?
Plan invalidation helps wi
Lukas Kahwe Smith wrote:
Martijn van Oosterhout wrote:
On Thu, Aug 31, 2006 at 11:27:18AM -0400, Tom Lane wrote:
I'd wish that we reconsider when and how prepared statements are
used. The JDBC interface and PL/pgSQL are frequently noticed
perpetrators, but the problem is really all over the p
Martijn van Oosterhout wrote:
On Thu, Aug 31, 2006 at 11:27:18AM -0400, Tom Lane wrote:
I'd wish that we reconsider when and how prepared statements are used. The
JDBC interface and PL/pgSQL are frequently noticed perpetrators, but the
problem is really all over the place.
AFAIK those are the
On Thu, Aug 31, 2006 at 11:27:18AM -0400, Tom Lane wrote:
> > I'd wish that we reconsider when and how prepared statements are used. The
> > JDBC interface and PL/pgSQL are frequently noticed perpetrators, but the
> > problem is really all over the place.
>
> AFAIK those are the only two places
On Aug 31, 2006, at 12:04 , Tom Lane wrote:
The two major complaints that I've seen are
Neither of these problems have anything to do with statistics getting
stale.
Not stats-- plans. Plan invalidation has been discussed before, no?
-M
---(end of broadcast)-
> Neither of these problems have anything to do with statistics getting
> stale.
... and the second one would benefit from a "meta-plan" facility which
puts some "meta-plan" nodes on top of specific plans to dispatch based
on parameter values at runtime.
Incidentally, the dispatch could check the
Andrew Dunstan <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> As noted downthread, we've confused out-of-line parameter value shipping
>> with prepared statements. It might be worth rejiggering the FE/BE
>> protocol to separate those things better.
> Well, that's surely not going to happen in a
AgentM <[EMAIL PROTECTED]> writes:
> On Aug 31, 2006, at 11:18 , [EMAIL PROTECTED] wrote:
>> I'm attempting to understand why prepared statements would be used for
>> long enough for tables to change to a point that a given plan will
>> change from 'optimal' to 'disastrous'.
> Scenario: A web appl
On Thu, Aug 31, 2006 at 11:36:36AM -0400, AgentM wrote:
> On Aug 31, 2006, at 11:18 , [EMAIL PROTECTED] wrote:
> >I'm attempting to understand why prepared statements would be used for
> >long enough for tables to change to a point that a given plan will
> >change from 'optimal' to 'disastrous'.
>
On 2006-08-31, Tom Lane <[EMAIL PROTECTED]> wrote:
>> I'd wish that we reconsider when and how prepared statements are used. The
>> JDBC interface and PL/pgSQL are frequently noticed perpetrators, but the
>> problem is really all over the place.
>
> AFAIK those are the only two places where prep
Tom Lane wrote:
As noted downthread, we've confused out-of-line parameter value shipping
with prepared statements. It might be worth rejiggering the FE/BE
protocol to separate those things better.
Well, that's surely not going to happen in a hurry, is it? Maybe a quick
fix would be
On Aug 31, 2006, at 11:18 , [EMAIL PROTECTED] wrote:
I'm attempting to understand why prepared statements would be used for
long enough for tables to change to a point that a given plan will
change from 'optimal' to 'disastrous'.
Wouldn't this require that the tables are completely re-written,
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> With time, it becomes ever clearer to me that prepared SQL statements
> are just a really bad idea.
That's an overstatement, but I'll agree that they have strong
limitations.
> I'd wish that we reconsider when and how prepared statements are used. T
On Thu, Aug 31, 2006 at 10:09:10AM -0400, Theo Schlossnagle wrote:
> There are many databases out there with better planners than
> PostgreSQL -- likely there will always be. Even those databases have
> query planner hints. Why? Because the authors of those database had
> the humility to r
On Thu, Aug 31, 2006 at 03:36:25PM +0200, Csaba Nagy wrote:
> On Thu, 2006-08-31 at 15:19, Peter Eisentraut wrote:
> > OK, why don't you work out an example. Let's look at this query:
> > SELECT * FROM t1 WHERE a LIKE $1;
> > What two plans would you prepare?
> if substring($1 from 1 for 1) != '%'
On Thu, Aug 31, 2006 at 01:56:29PM +0200, Peter Eisentraut wrote:
> With time, it becomes ever clearer to me that prepared SQL
> statements are just a really bad idea. On some days, it seems like
> half the performance problems in PostgreSQL-using systems are
> because a bad plan was cached somewh
Peter Eisentraut wrote:
Am Donnerstag, 31. August 2006 14:52 schrieb Csaba Nagy:
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.
OK, why don't you work out an ex
Am Donnerstag, 31. August 2006 16:26 schrieb Andrew Dunstan:
> Cached plans etc. might have an impact, but please do not overlook the
> benefits of parameterized queries in avoiding SQL injection attacks, as
> well as often being much cleaner to code.
That might be part of the confusion. Composin
On Aug 31, 2006, at 9:25 AM, Peter Eisentraut wrote:
Am Donnerstag, 31. August 2006 15:05 schrieb Merlin Moncure:
The proposal to supply hints to statements and functions has been
voted down several times due to the argument that it is better to fix
the planner. I think supplying hints does f
Am Donnerstag, 31. August 2006 16:09 schrieb Theo Schlossnagle:
> I don't chime in very often, but I do think the refusal to
> incorporate hints into the planner system is fantastically stubborn
> and nonsensical.
What is actually fantastically nonsensical about this is that the issues I
outlined
On Thu, Aug 31, 2006 at 08:06:57PM +0700, Jeroen T. Vermeulen wrote:
> On Thu, August 31, 2006 18:56, Peter Eisentraut wrote:
>
> > With time, it becomes ever clearer to me that prepared SQL
> > statements are just a really bad idea. On some days, it seems like
> > half the performance problems i
On Aug 31, 2006, at 8:52 , Csaba Nagy wrote:
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 p
On Thu, 2006-08-31 at 15:49, Peter Eisentraut wrote:
> Note that plan 1 can only be created if you know the actual value for $1.
Why would that be so ? The plan can contain functions of $1 (both
constants in plan 1 are a function of $1).
Cheers,
Csaba
---(end of broadca
Am Donnerstag, 31. August 2006 15:36 schrieb Csaba Nagy:
> On Thu, 2006-08-31 at 15:19, Peter Eisentraut wrote:
> > OK, why don't you work out an example. Let's look at this query:
> >
> > SELECT * FROM t1 WHERE a LIKE $1;
> >
> > What two plans would you prepare?
>
> if substring($1 from 1 for 1)
On Thu, 2006-08-31 at 15:19, Peter Eisentraut wrote:
> OK, why don't you work out an example. Let's look at this query:
>
> SELECT * FROM t1 WHERE a LIKE $1;
>
> What two plans would you prepare?
if substring($1 from 1 for 1) != '%' then
use plan 1 (see below);
else
use plan 2 (see below);
> > 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 particularl
Am Donnerstag, 31. August 2006 15:18 schrieb Andreas Pflug:
> Not to mention problems with outdated plans after schema changes. Using
> views unplanned (replanned) when used in joins could lead to improved
> resulting plans (e.g. if the view contains outer joins itself).
Views don't contain execut
Merlin Moncure wrote:
> On 8/31/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote:
>> With time, it becomes ever clearer to me that prepared SQL statements
>> are just
>> a really bad idea. On some days, it seems like half the performance
>> problems
>> in PostgreSQL-using systems are because a bad p
Am Donnerstag, 31. August 2006 14:52 schrieb Csaba Nagy:
> 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.
OK, why don't you work out an example. Let's look at
Am Donnerstag, 31. August 2006 15:06 schrieb Jeroen T. Vermeulen:
> Is there any kind of pattern at all to this problem? Anything
> recognizable? A few typical pitfalls?
If data is not distributed evenly, then any old WHERE foo = $1 is prone to be
the wrong plan for half of the possible values
Am Donnerstag, 31. August 2006 15:05 schrieb Merlin Moncure:
> The proposal to supply hints to statements and functions has been
> voted down several times due to the argument that it is better to fix
> the planner. I think supplying hints does fix the planner, and is a
> balanced solution.
Plann
Peter Eisentraut wrote:
> With time, it becomes ever clearer to me that prepared SQL statements are
> just
> a really bad idea. On some days, it seems like half the performance problems
> in PostgreSQL-using systems are because a bad plan was cached somewhere. I'd
> say, in the majority of ca
On Thu, August 31, 2006 18:56, Peter Eisentraut wrote:
> With time, it becomes ever clearer to me that prepared SQL statements are
> just
> a really bad idea. On some days, it seems like half the performance
> problems
> in PostgreSQL-using systems are because a bad plan was cached somewhere.
Is
thanks .
From: Csaba Nagy <[EMAIL PROTECTED]>
To: Peter Eisentraut <[EMAIL PROTECTED]>
CC: postgres hackers
Subject: Re: [HACKERS] Prepared statements considered harmful
Date: Thu, 31 Aug 2006 14:52:05 +0200
On Thu, 2006-08-31 at 14:32, Peter Eisentraut wrote:
> Am Donne
On 8/31/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote:
With time, it becomes ever clearer to me that prepared SQL statements are just
a really bad idea. On some days, it seems like half the performance problems
in PostgreSQL-using systems are because a bad plan was cached somewhere. I'd
say, i
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 value
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
Peter Eisentraut wrote:
With time, it becomes ever clearer to me that prepared SQL statements are just
a really bad idea. On some days, it seems like half the performance problems
in PostgreSQL-using systems are because a bad plan was cached somewhere. I'd
say, in the majority of cases the ti
> - Redefine "prepared" to mean "parsed" rather than "parsed and planned".
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) pla
On Thu, Aug 31, 2006 at 01:56:29PM +0200, Peter Eisentraut wrote:
> With time, it becomes ever clearer to me that prepared SQL statements are
> just
> a really bad idea. On some days, it seems like half the performance problems
> in PostgreSQL-using systems are because a bad plan was cached som
74 matches
Mail list logo
