On Mon, Mar 17, 2014 at 2:02 PM, Jim Nasby <jim@nasby.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.
> 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.
merlin