Re: Avoiding bad prepared-statement plans.

Mark Mielke wrote:

> Re-planning a generic plan with another generic plan may generate zero
> benefit, with a measurable cost. More on this after...

Nobody's talking about doing that any more. I proposed it initially
because I didn't know about changes that made it unnecessary.

> All the points about ms seem invalid to me. There are many reason why ms
> could increase, and many of them have nothing to do with plan
> efficiency. Again, re-planning due to a high ms, or a high ratio of ms,
> does not indicate that re-planning will improve the success of the plan.
> The planning process does not measure ms or predict ms.

That's true, but missing some very basic points about the idea: one, if
we can tell that a query is going to be expensive, then the cost of
re-planning it is marginal. Two, if we can tell that a query is going
to be expensive, then we stand a lot to gain if re-planning turns out to
be useful. It follows that we can afford to re-plan on the off-chance,
without anything more than a vague orders-of-magnitude idea of what
"expensive" means.

What Tom said validates a big assumption I've been making: that we do in
fact have a decent shot at telling in advance that a query is going to
be expensive. Which means we have a decent shot at stopping your 100ms
query from taking seconds just because you prepared it and are missing
out on that tiny partial index. That would be worth the extra planning
time at a 1% hit rate, and there's not much downside if we don't reach that.

> My idea of an optimal system is as follows:
>
> 1) Prepare gathers and caches data about the tables involved in the
> query, including column statistics that are likely to be required during
> the planning process, but prepare does not running the planning process.

It sounds to me like you're in the process of inventing another planning
process. Developer time aside, how much CPU time can you afford to
throw at this?

I don't see any reason to argue over what would be optimal when so much
information is still missing. It just makes the problem look harder
than it is. To me, our best shot at getting something useful is to stay
simple and defensive. After that, if there is still a need, we'll have
code to help us gather more data and figure out how to make it better.
Nothing wrong with the lowest-hanging fruit.

Jeroen