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

Re: Prepared statements considered harmful

From: Lukas Kahwe Smith <smith(at)pooteeweet(dot)org>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Subject: Re: Prepared statements considered harmful
Date: 2006-08-31 15:14:45
Message-ID: 44F6FCE5.6090309@pooteeweet.org (view raw or flat)
Thread:
Lists: pgsql-hackers
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 example.  Let's look at this query:
> 
> SELECT * FROM t1 WHERE a LIKE $1;
> 
> What two plans would you prepare?

Well I guess for the case that none of the "expected" plans fit you can 
always fallback to generating a new plan on the fly.

Anyways it would of course be cool if pgsql could set an invalid flag if 
it detects that a certain plan performed badly (maybe even automatically 
cause a fresh table analysis) or some DDL/DML was executed that likely 
invalidated the plan.

I am not sure if there is any "philosphie" that pgsql tries to adhere 
to. Does it want to leave the job of tuning to the DBA or does it want 
to do things automatically (which always means that in some situations 
it will do the wrong thing).

tweak planner vs. planner hints
manually analyze vs. automatically analyze
manual vaccum vs autovaccum

Hmm actually its probably not a black and white thing and the ultimate 
goal would be to offer both with maybe some installer checkbox to 
default everything to "DBA-less" automode.

Anyways I never liked the idea of planner hints. I think it makes much 
more sense to give people direct access to plans in that case. Meaning 
they can "partially" hardcode (parameterized) plans they want. I have 
mentioned before that Sybase seems to have such a feature (you can dump 
plans, tweak them and remove pieces that should be done on the fly and 
associate them with stored procedures - not sure if you also do that for 
prepared statements).

regards,
Lukas


In response to

pgsql-hackers by date

Next:From: Joshua D. DrakeDate: 2006-08-31 15:17:29
Subject: Re: GUC settings with units broken?
Previous:From: Peter EisentrautDate: 2006-08-31 15:11:18
Subject: Re: GUC settings with units broken?

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