On Tue, Sep 5, 2017 at 1:38 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > The complaint I have about PRAGMA is that it's yet another syntax for > accomplishing pretty much the same thing. If you don't like the GUC > solution, we've already got the "comp_option" syntax for static options > in plpgsql. Sure, that's not too pretty, but that's not a good reason > to invent yet another way to do it.
On the general question of whether we should have something like this, I expressed a lot of doubt when e6faf910d75027bdce7cd0f2033db4e912592bcc first went in about whether that algorithm was really going to work, and nothing has happened since then to remove any of that doubt. It's pretty clear to me from experiences with customer problems that the heuristics we have often fail to do the right thing, either because queries with no hope of benefiting still replan 5 times - which can waste a ton of CPU when there are many different queries in the plan cache and many sessions - or because queries that would benefit in some cases give up on replanning before they hit a case where a parameter-specific plan helps. I don't think we can just indefinitely continue to resist providing manual control over this behavior on the theory that some day we'll fix it. It's been six years and we haven't made any significant progress. In some cases, a long delay without any progress might just point to a lack of effort that should have been applied, but in this case I think it's because the problem is incredibly hard. I think what we ideally want to do is notice whether the new bind variables cause a change in selectivity which is sufficient to justify a re-plan. If we annotated the original plan with markers indicating that it was valid for all values with a frequency of more than X and less than Y, for example, we could cover most cases involving equality; range queries would need some other kind of annotation. However, it's unclear how the planner could produce such annotations, and it's unclear how expensive checking against them would be if we had them. Barring somebody having a brilliant insight about how to make some system that's way better than what we have right now, I think we can't hold out much hope of any better fix than a manual knob. I think a GUC is a decent, though not perfect, mechanism for this. This problem isn't restricted to PL/pgsql; indeed, the cases I've seen have come via prepared queries, not PL/pgsql functions. Even without that, one advantage of a GUC is that they are fairly broadly understood and experienced users understand what they can do with them. They can be set at various different scopes (system, user, database, SET clause for a particular function) and it's relatively convenient to do so. Some kind of PL/pgsql-specific PRAGMA syntax is more likely to be overlooked by users who would actually benefit from it, and also won't cover non-PL/pgsql cases. If we were going to go the PRAGMA route, it would make more sense to me to define that as a way of setting a GUC for the scope of one PL/pgsql block, like PRAGMA SETTING(custom_plan_tries, 0). I think it is in general unfortunate that we don't have a mechanism to change a GUC for the lifespan of one particular query, like this: LET custom_plan_tries = 0 IN SELECT ... I bet a lot of people would find that quite convenient. The problem of needing to set a planner GUC for one particular query is pretty common, and Pavel is absolutely right that having to do SET beforehand and RESET afterward is ugly. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
