On 3/17/14, 12:58 PM, Stephen Frost wrote:
* Merlin Moncure (mmonc...@gmail.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 j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
