Re: Planner hints in Postgresql

On 3/17/14, 3:32 PM, Merlin Moncure wrote:
> On Mon, Mar 17, 2014 at 2:02 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>> Just being able to detect that something has possibly gone wrong would be
>> useful. We could log that to alert the DBA/user of a potential bad plan. We
>> could even format this in such a fashion that it's suitable for emailing the
>> community with; the query, the plan, the stats, etc. That might make it
>> easier for us to fix the planner (although at this point it seems like we're
>> hitting statistics gathering problems that we simply don't know how to
>> solve).
>
> Again, that's not the case here. The problem is that the server is
> using hard wired assumptions (like, 10% selective) *instead* of
> statistics -- at least in the case discussed above. That being said,
> I think you're on to something: EXPLAIN ANALYZE rowcounts don't
> indicate if the row count was generated from data based assumptions or
> SWAGs. So maybe you could decorate the plan description with an
> indicator that suggests when default selectivity rules were hit.

It occurs to me... it should be cheap for us to track actual rowcounts compared to the estimate... perhaps it's worth doing that and flagging plans when the estimates are off by more than X percent. Though... I suspect that will just tell us what we already know. :(

>> There is another aspect of this though: plan stability. There are lots of
>> cases where users couldn't care less about getting an optimal plan, but they
>> care *greatly* about not getting a brain-dead plan.
>
> Except for cases I noted above, I don't understand how you could flag
> 'sub-optimal' or 'brain-dead' plans. The server always picks the
> best plan it can. The trick is to (in a very simple and
> cpu-unintensive way) indicate when there isn't a lot of confidence in
> the plan -- but that's not the same thing.

Keep in mind that the use case here is critical queries that MUST perform "fast enough". They do NOT need to be optimal, but they definitely can not degrade into something stupid. It's often way better to have a query that's 50-100% slower than optimal as opposed to one that suddenly becomes 100+% slower than it normally is (regardless of if normal is optimal or not).

You could possibly do an "anti-hint": Never use this plan, because we know it sucks.

Even better would be if the planner could estimate how bad a plan will become if we made assumptions that turn out to be wrong.

Another option would be tracking normal execution times (and the plans used) for queries (not completely unreasonable now with pg_stat_statements); if we try to run a query and it takes noticeably longer than normal and it's a different plan then assume the plan is bad, mark it as such, and try again with one of the "known good" plans.

Worst case would be allowing a means to store an acceptable plan and force/strongly suggest that the planner use it.
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net