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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
