pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Greg Stark <stark@mit.edu>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Yet another abort-early plan disaster on 9.3
Date: Mon, 22 Sep 2014 16:56:12 -0700
Message-ID: <5420B71C.6010208@agliodbs.com> (raw)
In-Reply-To: <WM!aa8d66f3329082c248d8e57896b3051be5691fe6550231e4f7388b7a9339864c4c89748426e4ef897c484b8998b7f996!@asav-1.01.com>
References: <541A2335.3060100@agliodbs.com>
	<CAHyXU0zSCWLYZ34fsgAGn9x+nFEwOih4ve3kyeYonTY6xG1Xbg@mail.gmail.com>
	<WM!2bfedb9f882c0aee19f96e138ce7ba810dc6737fca13f21c249ba37fd452020e43a863c0516a5aab9c7b1bd4f7201d15!@asav-3.01.com>
	<541C7891.7000902@agliodbs.com>
	<CAM-w4HODbu3jLFbENhUXmU3R6YQUQW2GZQOBzt3vHtVdUO5+hA@mail.gmail.com>
	<WM!1ae41f8d6a3ccc4760aa921f5bee325497ff91ea6bb56959742bfe645e3727f092c347cf598f62aba805d5b6535ebc8f!@asav-1.01.com>
	<541DC87F.3030800@agliodbs.com>
	<CAHyXU0xh8kFG6jiH3CLswTPiZTxJYsP==uaZP_w_FxHc0Kyqxg@mail.gmail.com>
	<WM!aa8d66f3329082c248d8e57896b3051be5691fe6550231e4f7388b7a9339864c4c89748426e4ef897c484b8998b7f996!@asav-1.01.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



view thread (60+ messages)  latest in thread

Message-ID: <5420B71C.6010208@agliodbs.com>
Permalink:  ../5420B71C.6010208@agliodbs.com/
Also on:    postgresql.org/message-id/5420B71C.6010208@agliodbs.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: josh@agliodbs.com, mmoncure@gmail.com, stark@mit.edu
  Subject: Re: Yet another abort-early plan disaster on 9.3
  In-Reply-To: <5420B71C.6010208@agliodbs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox