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

help / color / mirror / Atom feed
From: Tomas Vondra <tv@fuzzy.cz>
To: Greg Stark <stark@mit.edu>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: Peter Geoghegan <peter.geoghegan86@gmail.com>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Yet another abort-early plan disaster on 9.3
Date: Fri, 10 Oct 2014 14:10:13 +0200
Message-ID: <f4c06746b31e468f9550fb9924798da3.squirrel@2.emaily.eu> (raw)
In-Reply-To: <CAM-w4HNfZymQmTu3+TxQQD-e6_410-sDnZBaW8neurmTFh4GbA@mail.gmail.com>
References: <541A2335.3060100@agliodbs.com>
	<5420B71C.6010208@agliodbs.com>
	<CA+U5nMJqYiViG8Xu=OqJo5o7NbKgrBYC=Kn6nx-4AtKXidWvtw@mail.gmail.com>
	<WM!179663f2b73bd99d77bdeeaa9c7a72aa03c039d695004807cd4a621b4ed9ff654caf2caa9f8d529ad85c07db10c93999!@asav-1.01.com>
	<5429D517.3090900@agliodbs.com>
	<CAMkU=1wCMLWZexqVUPXWFeKmPPUDMv6GEEFUVDQnb7gfW9Wamw@mail.gmail.com>
	<CA+U5nMLj0iyYpOM+uF934-joy+6S64=yR+d6cnp94ZWzk2xkug@mail.gmail.com>
	<WM!ce2c941557a0e9090a3cb66d0469128c782bb4d7b1f60d15a5c91279578d4c6fd237efa5b4660e94a0056752c695113e!@asav-2.01.com>
	<542C4E60.3030505@agliodbs.com>
	<CA+U5nMKqqPNpSr2CQ2V0=9bjWVv-OA28o3BoxYP=7YVfyswdzw@mail.gmail.com>
	<CAEYLb_UTz=dtxYOCYF_D1s7wQJfoZUrey6Fjxq08VFueXWoEKQ@mail.gmail.com>
	<WM!341e0135c2f371f48830484cdb3da37506d776453b10db7a9af11d648214c4b437402f04e7ccb3d30918cbc64dfee95d!@asav-1.01.com>
	<542DADEB.2080103@agliodbs.com>
	<CAM-w4HNfZymQmTu3+TxQQD-e6_410-sDnZBaW8neurmTFh4GbA@mail.gmail.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

Dne 10 Říjen 2014, 13:16, Greg Stark napsal(a):
> On Thu, Oct 2, 2014 at 8:56 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> Yes, it's only intractable if you're wedded to the idea of a tiny,
>> fixed-size sample.  If we're allowed to sample, say, 1% of the table, we
>> can get a MUCH more accurate n_distinct estimate using multiple
>> algorithms, of which HLL is one.  While n_distinct will still have some
>> variance, it'll be over a much smaller range.
>
> I've gone looking for papers on this topic but from what I read this
> isn't so. To get any noticeable improvement you need to read 10-50% of
> the table and that's effectively the same as reading the entire table
> -- and it still had pretty poor results. All the research I could find
> went into how to analyze the whole table while using a reasonable
> amount of scratch space and how to do it incrementally.

I think it's really difficult to discuss the estimation without some basic
agreement on what are the goals. Naturally, we can't get a perfect
estimator with small samples (especially when the sample size is fixed and
not scaling with the table). But maybe we can improve the estimates
without scanning most of the table?

FWIW I've been playing with the adaptive estimator described in [1] and
the results looks really interesting, IMHO. So far I was testing it on
synthetic datasets outside the database, but I plan to use it instead of
our estimator, and do some more tests.

Would be helpful to get a collection of test cases that currently perform
poorly. I have collected a few from the archives, but if those who follow
this thread can provide additional test cases / point to a thread
describing related etc. that'd be great.

It certainly won't be perfect, but if it considerably improves the
estimates then I believe it's step forward. Ultimately, it's impossible to
improve the estimates without increasing the sample size.

[1]
http://ftp.cse.buffalo.edu/users/azhang/disc/disc01/cd1/out/papers/pods/towardsestimatimosur.pdf

regards
Tomas



-- 
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: <f4c06746b31e468f9550fb9924798da3.squirrel@2.emaily.eu>
Permalink:  ../f4c06746b31e468f9550fb9924798da3.squirrel@2.emaily.eu/
Also on:    postgresql.org/message-id/f4c06746b31e468f9550fb9924798da3.squirrel@2.emaily.eu

 · 

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: tv@fuzzy.cz, stark@mit.edu, josh@agliodbs.com, peter.geoghegan86@gmail.com, simon@2ndquadrant.com, jeff.janes@gmail.com, mmoncure@gmail.com
  Subject: Re: Yet another abort-early plan disaster on 9.3
  In-Reply-To: <f4c06746b31e468f9550fb9924798da3.squirrel@2.emaily.eu>

* 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