Re: Avoiding bad prepared-statement plans.

On Fri, Feb 26, 2010 at 4:01 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> It's not going to be easier to implement.  Yeah, it would be easy to
>> provide a global switch via a GUC setting, but that's not going to be
>> helpful, because this is the sort of thing that really needs to be
>> managed per-query.  Almost any nontrivial application is going to have
>> some queries that really need the custom plan and many that don't.
>> If people just turn the GUC on we might as well throw away the plan
>> caching mechanism altogether.
>
> I agree.  A GUC is a really bad idea.

I'm not sure I see why. GUCs can be turned on and off per-query. Even
if you have protocol-level support a GUC could be more convenient in
some cases. Specifically it would be useful for testing explain plans
on queries that you expect to be planned without parameters. Currently
you have to rewrite the query using PREPARE QUERY which is far from
intuitive for users and even once you know how to do it prevents you
from just copying and pasting queries.

>> But putting support for a per-query level
>> of control into the protocol (and then every client library) as well as
>> every PL is going to be painful to implement, and even more painful to
>> use.
>
> I suppose I should have learned by now not to argue with you over
> technical points, but I don't see why this should be painful.  I mean,
> it'll be a lot of work and it'll in the end touch a lot of different
> parts of the code, but work != pain, and I don't see any reason why
> the problem can't be attacked incrementally.  I'm also deeply
> unconvinced that any other solution will be as satisfactory.

I'm not sure we should be so conservative about adding features to the
protocol. Sure it'll take time to filter through to all the drivers
and the better ones will support it before the less well maintained
ones. But that's just a question of time and nobody will be too
surprised by that.

I think we should have a general purpose options field for all our
messages. We can define an enum of options keys and pass an integer
value for each option. In some cases they'll actually be boolean and
other cases they'll be tunables but it seems simpler to make it so the
server can parse all the options out of the message even if it doesn't
understand them all. And that means proxies can pass them on without
understanding what they all mean.

We discussed a while back marking all "latency-critical" queries so
the backend knows that any buffers touched by that query should be
marked as more important to stay in cache. If we got more complex
priority handling we would be able to pass an option for the time
budget for a query or its nice level.

--
greg