From: Greg Stark <gsstark@mit.edu>
To: josh@agliodbs.com
Cc: Greg Stark <gsstark@mit.edu>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Fetter <david@fetter.org>
Cc: pgsql-hackers@postgresql.org
Subject: Re: More thoughts about planner's cost estimates
Date: 02 Jun 2006 18:59:18 -0400
Message-ID: <873bendv09.fsf@stark.xeocode.com> (raw)
In-Reply-To: <200606021515.44174.josh@agliodbs.com>
References: <14821.1149104747@sss.pgh.pa.us>
<10464.1149281016@sss.pgh.pa.us>
<87k67zdymk.fsf@stark.xeocode.com>
<200606021515.44174.josh@agliodbs.com>
Josh Berkus <josh@agliodbs.com> writes:
> Greg, Tom,
>
> > But for most users analyze doesn't really have to run as often as
> > vacuum. One sequential scan per night doesn't seem like that big a deal
> > to me.
>
> Clearly you don't have any 0.5 TB databases.
Actually I did not so long ago.
Sequential scans in an OLTP query would be a disaster. But a single sequential
scan run at a controlled time wouldn't concern me as long as *all* of the
following constraints are met:
a) You can run them at your leisure at off-peak times when your i/o bandwidth
isn't in short supply.
b) You don't need the results urgently so you don't care if it takes a while
to run.
c) You don't need many of them at the same time.
Even on your production system surely you occasionally, say, take a full
backup or run "select count(*)" or other sanity checks on the data?
> > > I'd still be worried about the CPU pain though. ANALYZE can afford to
> > > expend a pretty fair number of cycles per sampled tuple, but with a
> > > whole-table sample that's going to add up.
>
> Agreed. Despite conventional wisdom, most PostgreSQL databases ... even
> those with high level OLTP or very large DW ... are CPU-bound. We
> really don't want an ANALYZE which is an order-of-magnitude increase in
> CPU activity.
I don't think Tom was actually expressing concern about ANALYZE becoming more
expensive, but about tying ANALYZE and VACUUM together and making VACUUM more
expensive. VACUUM is something we want to encourage people to think they can
run all day long, not just occasionally.
--
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-hackers@postgresql.org
Cc: gsstark@mit.edu, josh@agliodbs.com, tgl@sss.pgh.pa.us, david@fetter.org
Subject: Re: More thoughts about planner's cost estimates
In-Reply-To: <873bendv09.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