Re: Yet another abort-early plan disaster on 9.3

On 09/22/2014 06:55 AM, Merlin Moncure wrote:
> Has any progress been made on the performance farm? The problem with
> suggestions like this (which seem pretty reasonable to me) is that
> we've got no way of quantifying the downside.

Yeah, that's certainly an issue. The problem is that we'd need a
benchmark which actually created complex query plans. I believe that
Mark Wong is working on TPCH-based benchmarks, so maybe we'll get that.

> I think this is one
> example of a class of plans that are high risk. Another one off the
> top of my head is nestloop joins based on assumed selectivity of
> multiple stacked quals.

Yeah, that's another good example.

> About 90% of the time, my reflective
> workaround to these types of problems is to 'disable_nestloop' which
> works around 90% of the time and the result are solved with monkeying
> around with 'OFFSET 0' etc. In the past, a GUC controlling planner
> risk has been much discussed -- maybe it's still worth considering?

We've hashed that out a bit, but frankly I think it's much more
profitable to pursue fixing the actual problem than providing a
workaround like "risk", such as:

a) fixing n_distinct estimation
b) estimating stacked quals using better math (i.e. not assuming total
randomness)
c) developing some kind of correlation stats

Otherwise we would be just providing users with another knob there's no
rational way to set.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com