From: Greg Stark <gsstark@mit.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Rod Taylor <pg@rbt.ca>
Cc: Greg Stark <gsstark@mit.edu>
Cc: pgsql-hackers@postgresql.org, Gurmeet Manku <manku@cs.stanford.edu>
Subject: Re: [PERFORM] Bad n_distinct estimation; hacks suggested?
Date: 27 Apr 2005 01:59:30 -0400
Message-ID: <87is2869q5.fsf@stark.xeocode.com> (raw)
In-Reply-To: <17677.1114575276@sss.pgh.pa.us>
References: <Pine.LNX.4.44.0504261443340.12051-100000@xenon.Stanford.EDU>
<87u0lt5efo.fsf@stark.xeocode.com>
<1114557011.77587.107.camel@home>
<87oec15d9m.fsf@stark.xeocode.com>
<1114560992.77587.112.camel@home>
<17677.1114575276@sss.pgh.pa.us>
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Rod Taylor <pg@rbt.ca> writes:
> > If when we have partitions, that'll be good enough. If partitions aren't
> > available this would be quite painful to anyone with large tables --
> > much as the days of old used to be painful for ANALYZE.
>
> Yeah ... I am very un-enthused about these suggestions to make ANALYZE
> go back to doing a full scan ...
Well one option would be to sample only a small number of records, but add the
data found from those records to the existing statistics. This would make
sense for a steady-state situation, but make it hard to recover from a drastic
change in data distribution. I think in the case of n_distinct it would also
bias the results towards underestimating n_distinct but perhaps that could be
corrected for.
But I'm unclear for what situation this is a concern.
For most use cases users have to run vacuum occasionally. In those cases
"vacuum analyze" would be no worse than a straight normal vacuum. Note that
this algorithm doesn't require storing more data because of the large scan or
performing large sorts per column. It's purely O(n) time and O(1) space.
On the other hand, if you have tables you aren't vacuuming that means you
perform zero updates or deletes. In which case some sort of incremental
statistics updating would be a good solution. A better solution even than
sampling.
--
greg
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: gsstark@mit.edu, tgl@sss.pgh.pa.us, pg@rbt.ca, manku@cs.stanford.edu
Subject: Re: [PERFORM] Bad n_distinct estimation; hacks suggested?
In-Reply-To: <87is2869q5.fsf@stark.xeocode.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