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

Re: Avoiding bad prepared-statement plans.

From: Jeroen Vermeulen <jtv(at)xs4all(dot)nl>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Bart Samwel <bart(at)samwel(dot)tk>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Avoiding bad prepared-statement plans.
Date: 2010-02-15 19:51:26
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Tom Lane wrote:

> Well, no, consider the situation where planning takes 50 ms, the generic
> plan costs 100ms to execute, but a parameter-specific plan would take 1ms
> to execute.  Planning is very expensive compared to execution but it's
> still a win to do it.

I think that's a fun and worthwhile problem.  But my limited personal 
aim right now is a stopgap for pathological cases.  I'd like to pick the 
low-hanging fruit; actually squeezing the fat out of prepared statements 
is a job I wouldn't get around to completing.  Sorry for mixing metaphors.

Here's what I like about the really slow plans. (Now why does that sound 
so strange?)  We don't know if re-planning will help, but we do know 
that (1) it won't hurt much relative to execution time, so we really 
don't _care_; and (2) there is lots of potential for improvement, so 
catching just one execution that can be much faster might pay for all 
the extra time spent re-planning.

Where do we draw the line between costly and pathological?  I still like 
Bart's idea of a fixed ratio to planning time, because it reflects a 
self-tuning sense of proportion.  Sure, planning time can vary a lot but 
we're talking about an order-of-magnitude difference, not an exact 19:21 
optimum.  We can be sloppy and still expect to win.

AFAIC a statement could go to "re-planning mode" if the shortest 
execution time for the generic plan takes at least 10x longer than the 
longest planning time.  That gives us a decent shot at finding 
statements where re-planning is a safe bet.  A parameter that we or the 
user would have to tweak would just be a fragile approximation of that.

> A possible scheme is to try it and keep track of whether we ever
> actually do get a better plan.  If, after N attempts, none of the custom
> plans were ever more than X% cheaper than the generic one, then give up
> and stop attempting to produce custom plans.  Tuning the variables might
> be challenging though.

A simple stopgap implementation may also be a useful experimentation 
platform for refinements.  It shouldn't be too complex to rip out when 
something better comes along.


In response to


pgsql-hackers by date

Next:From: David E. WheelerDate: 2010-02-15 19:52:01
Subject: Re: PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
Previous:From: Joachim WielandDate: 2010-02-15 19:21:23
Subject: Re: Listen / Notify - what to do when the queue is full

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