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

help / color / mirror / Atom feed
From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Yet another abort-early plan disaster on 9.3
Date: Wed, 01 Oct 2014 10:11:20 +1300
Message-ID: <542B1C78.5000602@archidevsys.co.nz> (raw)
In-Reply-To: <CAMkU=1z=LKZAxafrHgj0coAzBuwNRF1XazBbUF7Qqq9Xq3KDoQ@mail.gmail.com>
References: <541A2335.3060100@agliodbs.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>
	<5420B71C.6010208@agliodbs.com>
	<CA+U5nMJqYiViG8Xu=OqJo5o7NbKgrBYC=Kn6nx-4AtKXidWvtw@mail.gmail.com>
	<CAHyXU0wFAKiwgybbEAX=597-MxtPGdf0bmQvRSJq9db1KTZ6nA@mail.gmail.com>
	<CA+U5nMKnwZVkvdaQP7Sm15-Emdwt+TgnW7-0xQJvZPtHu2Wsvw@mail.gmail.com>
	<13982.1412031637@sss.pgh.pa.us>
	<542A1170.2010600@archidevsys.co.nz>
	<CAMkU=1z=LKZAxafrHgj0coAzBuwNRF1XazBbUF7Qqq9Xq3KDoQ@mail.gmail.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

On 01/10/14 05:54, Jeff Janes wrote:
> On Mon, Sep 29, 2014 at 7:12 PM, Gavin Flower 
> <GavinFlower@archidevsys.co.nz <mailto:GavinFlower@archidevsys.co.nz>> 
> wrote:
>
>
>     Would it be feasible to get a competent statistician to advise what data to collect, and to analyze it?  Maybe it is possible to get a better estimate on how much of a table needs to be scanned, based on some fairly simple statistics.  But unless research is done, it is probably impossible to determine what statistics might be useful, and how effective a better estimate could be.
>
>     I have a nasty feeling that assuming a uniform distribution, may
>     still end up being the best we can do - but I maybe being unduly
>     pessimistic!.
>
>
> As a semi-competent statistician, my gut feeling is that our best bet 
> would be not to rely on the competence of statisticians for too much, 
> and instead try to give the executor the ability to abandon a 
> fruitless path and pick a different plan instead. Of course this 
> option is foreclosed once a tuple is returned to the client (unless 
> the ctid is also cached, so we can make sure not to send it again on 
> the new plan).
>
> I think that the exponential explosion of possibilities is going to be 
> too great to analyze in any rigorous way.
>
> Cheers,
>
> Jeff
Many moons ago, I passed several 300 level statistics papers.

I looked at this problem and found it was too hard to even properly 
characterise the problem (looks 'simple' - if you don't look too 
closely), and ended up feeling it was definitely 'way above my pay 
grade'!  :-)

It might be possible to tackle it more pragmatically, instead of trying 
to be all analytic and rigorously list all the possible influences, have 
a look at queries of this nature that are taking far too long.  Then get 
a feel for combinations of issues involved and how they contribute.  If 
you have enough data, you might be able to use something like Principle 
Component Analysis (I was fortunate to meet a scientist who had got 
heavily into this area of statistics).  Such an approach might yield 
valuable insights, even if the problem is not fully characterised, let 
alone 'solved'.


Cheers,
Gavin

view thread (60+ messages)  latest in thread

Message-ID: <542B1C78.5000602@archidevsys.co.nz>
Permalink:  ../542B1C78.5000602@archidevsys.co.nz/
Also on:    postgresql.org/message-id/542B1C78.5000602@archidevsys.co.nz

 · 

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: GavinFlower@archidevsys.co.nz, jeff.janes@gmail.com, tgl@sss.pgh.pa.us, simon@2ndquadrant.com, mmoncure@gmail.com, josh@agliodbs.com
  Subject: Re: Yet another abort-early plan disaster on 9.3
  In-Reply-To: <542B1C78.5000602@archidevsys.co.nz>

* 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