Re: Avoiding bad prepared-statement plans.

On 02/26/2010 05:20 AM, Jeroen Vermeulen wrote:
> Mark Mielke wrote:
>
>> 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.

You trimmed most of my concerns. :-) Problems:

1) If I do a PREPARE/EXECUTE, the above lengthens the process from
1 generic planning plus 1 generic plan execute to 1 generic planning, 1
specific planning, and 1 specific plan execution. This is still overall
longer than a regular statement and it still may be longer than the
original generic plan on its own. The hope is that the analysis is
somehow detecting the scenario where a generic plan makes no sense, but
the criteria is not about whether the generic plan actually does make
sense - the criteria is "can the customer afford to wait longer for us
to second guess ourselves?" It's a guess. As a guess, it means sometimes
it will be right, and sometimes it will be wrong.

2) Only the "order of magnitude" (by estimate) plans will benefit.
If you set the number to 100X, then most plans won't benefit. If you set
it to less than 100X, you increase the chance of guessing wrong in other
cases. In any case, there is still no guarantee that a specific plan
will be faster, so even in the 100X case, the overall results could be
slower - it's just that you've decided the customer can afford to wait
longer.

>> 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 already said I don't think PostgreSQL could easily evolve here.
However, I wanted to point out that the problem may be architectural.

As for developer time and CPU time, that's not really relevant. If
PREPARE/EXECUTE could be reliably sped up, than the savings is probably
measure in millions of dollars or more, as it is widely used by many
applications throughout the day on hundreds of thousands of computers.

Oh, you mean is it worth scratching my itch? :-) Not really. I was
thinking about it yesterday and decided that such a major change might
just as easily result in a new database engine, and I didn't want to go
there.

Still, if some clever person agrees with me that it is an architecture
problem, and that PostgreSQL could benefit from a clean "from scratch"
caching mechanism for statements (note that what I described could
probably be extended to support automatic prepare of every statement,
and matching of query to prepared statement based on text, similar to
MySQL query caching), and can come up with a way to do this using the
existing architecture - that would be great. Or, they can tell me "too
hard" as you are. That's fine too... :-)

> 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.

What information is missing?

PREPARE sucks in many known situations. It is a documented fact. :-)

Will "guessing" at when the user can afford to wait longer improve the
situation? Maybe or often, but not always.

Cheers,
mark