Re: Discussion on missing optimizations

Stephen Frost wrote:
> * Laurenz Albe (laurenz(dot)albe(at)cybertec(dot)at) wrote:
> > 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.
> >
> > Perhaps it really brings the performance benefits they claim, but
> > a) there have been a number of bugs where the wrong plan got used
> > (you have to keep several plans for the same statement around,
> > since - as you say - different sessions have different environments)
>
> I'm not sure this is really a fair strike against this concept- bugs
> happen, even bugs in planning, and what we need is more testing, imv, to
> reduce the number and minimize the risk as much as we can.

Right, I guess I didn't express my concern properly.
Bugs can certainly happen, but if a certain feature is particularly
rich in them, I take it as an indication that it is something difficult
to get right.

> > b) it is a frequent problem that this shared memory area grows
> > too large if the application does not use prepared statements
> > but dynamic SQL with varying constants.
>
> This just requires that the memory area be managed somehow, not unlike
> how our shared buffers have to deal with evicting out old pages.
> There's a challenge there around making sure that it doesn't make the
> performance of the system be much worse than not having a cache at all,
> naturally, but given that a lot of people use pg_stat_statements to good
> effect and without much in the way of complaints, it seems like it might
> be possible make it work reasonably (just imagining a generic plan being
> attached to pg_stat_statements with some information about if the
> generic plan works well or not, blah blah, hand waving goes here).

pg_stat_statements is quite different, since it ignores constants.

I don't know how often I have seen the advice to use dynamic SQL
because a generic plan was not so great, and in OLAP you usually
want each individual query to be planned.

So I think it is important that a plan is only reused if it matches
the query exactly. Oracle has an option CURSOR_SHARING = FORCE to
reuse plans even if they were planned with different constants, but
their own documentation warns against using it:
http://docs.oracle.com/database/122/TGSQL/improving-rwp-cursor-sharing.
htm#GUID-7FF4E133-06A7-401E-9BFC-3B0B9C902346

Maybe it is an idea to only cache generic plans in a shared area?

Yours,
Laurenz Albe