Re: Yet another abort-early plan disaster on 9.3

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: Greg Stark <stark(at)mit(dot)edu>, postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Yet another abort-early plan disaster on 9.3
Date: 2014-09-22 23:56:12
Message-ID: 5420B71C.6010208@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2014-09-23 00:24:21 Re: WITH CHECK OPTION bug [was RLS Design]
Previous Message Andres Freund 2014-09-22 23:48:55 Re: Should we excise the remnants of borland cc support?

Browse pgsql-performance by date

  From Date Subject
Next Message Ross Elliott 2014-09-23 12:21:31 Slow query
Previous Message Merlin Moncure 2014-09-22 13:55:59 Re: Yet another abort-early plan disaster on 9.3