On 13 October 2017 at 03:00, Tom Lane <t...@sss.pgh.pa.us> wrote: > Laurenz Albe <laurenz.a...@cybertec.at> writes: >> Robert Haas wrote: >>> One trick that some system use is avoid replanning as much as we do >>> by, for example, saving plans in a shared cache and reusing them even >>> in other sessions. That's hard to do in our architecture because the >>> controlling GUCs can be different in every session and there's not >>> even any explicit labeling of which GUCs control planner behavior. But >>> if you had it, then extra planning cycles would be, perhaps, more >>> tolerable. > >> From my experience with Oracle I would say that that is a can of worms. > > Yeah, I'm pretty suspicious of the idea too. We've had an awful lot of > bad experience with local plan caching, to the point where people wonder > why we don't just auto-replan every time. How would a shared cache > make that better? (Even assuming it was otherwise free, which it > surely won't be.)
One idea I had when working on unique joins was that we could use it to determine if a plan could only return 0 or 1 row by additionally testing baserestrictinfo of each rel to see if an equality OpExpr with a const Const matches a unique constraint which would serve as a guarantee that only 0 or 1 row would match. I thought that these single row plans could be useful as a start for some pro-active plan cache. The plan here should be stable as they're not prone to any data skew that could cause a plan change. You might think it would be very few plans would fit the bill, but you'd likely find that the majority of UPDATE and DELETE queries run in an OTLP application would be eligible, and likely some decent percentage of SELECTs too. I had imagined this would be some backend local cache that saved MRU plans up to some size of memory defined by a GUC, where 0 would disable the feature. I never got any further than those thoughts. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
