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: | 4B79A5BE.6060906@xs4all.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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.
Jeroen
From | Date | Subject | |
---|---|---|---|
Next Message | David E. Wheeler | 2010-02-15 19:52:01 | Re: PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl |
Previous Message | Joachim Wieland | 2010-02-15 19:21:23 | Re: Listen / Notify - what to do when the queue is full |