Re: Planner hints in Postgresql

On 3/17/14, 12:58 PM, Stephen Frost wrote:
> * Merlin Moncure (mmoncure(at)gmail(dot)com) wrote:
>> Yeah -- the most common case I see is outlier culling where several
>> repeated low non-deterministic selectivity quals stack reducing the
>> row count estimate to 1. For example:
>> SELECT * FROM foo WHERE length(bar) <= 1000 AND length(bar) >= 2;
>
> This is exactly the issue that I've seen also- where we end up picking a
> Nested Loop because we think only one row is going to be returned and
> instead we end up getting a bunch and it takes forever.

FWIW, I've also seen problems with merge and hash joins at work, but I don't have any concrete examples handy. :(

> There was also some speculation on trying to change plans mid-stream to
> address a situation like that, once we realize what's happening. Not
> sure that's really practical but it would be nice to find some solution.

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

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.
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net