From: Greg Smith <greg@2ndquadrant.com>
To: david@lang.hm
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Vitalii Tymchyshyn <tivv00@gmail.com>
Cc: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: Mladen Gogala <mladen.gogala@vmsinfo.com>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Craig Ringer <craig@postnewspapers.com.au>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: [HACKERS] Slow count(*) again...
Date: Thu, 03 Feb 2011 17:35:12 -0500
Message-ID: <4D4B2DA0.3040601@2ndquadrant.com> (raw)
In-Reply-To: <alpine.DEB.2.00.1102031243190.10088@asgard.lang.hm>
References: <201102012247.p11Ml6u02682@momjian.us>
<4D48914B.4050403@dunslane.net>
<4D489AD1.1010105@vmsinfo.com>
<AANLkTiknZi97dDwm7sHqy0EL7tpNEP9sY5uPV6AN+q=u@mail.gmail.com>
<4D499E55.4020107@vmsinfo.com>
<AANLkTimo2J-BagObs_V1bGaHOO9qL+kTF6s6-wZ=M3KW@mail.gmail.com>
<AANLkTin7A_Q=Yc9Wf_5=POp2mVqmaiwqUDsFMLG68H45@mail.gmail.com>
<4D4A7B69.2020704@gmail.com>
<alpine.DEB.2.00.1102030208440.8162@asgard.lang.hm>
<AANLkTi=Thet0VZ-X6b7tek+iSLVkE27JjiKrB5D8U0Jd@mail.gmail.com>
<alpine.DEB.2.00.1102031243190.10088@asgard.lang.hm>
david@lang.hm wrote:
> I am making the assumption that an Analyze run only has to go over the
> data once (a seqential scan of the table if it's >> ram for example)
> and gathers stats as it goes.
And that's the part there's some confusion about here. ANALYZE grabs a
random set of samples from the table, the number of which is guided by
the setting for default_statistics_target. The amount of time it takes
is not proportional to the table size; it's only proportional to the
sampling size. Adding a process whose overhead is proportional to the
table size, such as the continuous analyze idea you're proposing, is
quite likely to be a big step backwards relative to just running a
single ANALYZE after the loading is finished.
What people should be doing if concerned about multiple passes happening
is something like this:
CREATE TABLE t (s serial, i integer) WITH (autovacuum_enabled=off);
[populate table]
ANALYZE t;
ALTER TABLE t SET (autovacuum_enabled=on);
I'm not optimistic the database will ever get smart enough to recognize
bulk loading and do this sort of thing automatically, but as the
workaround is so simple it's hard to get motivated to work on trying.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
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: greg@2ndquadrant.com, david@lang.hm, robertmhaas@gmail.com, tivv00@gmail.com, jnelson+pgsql@jamponi.net, mladen.gogala@vmsinfo.com, andrew@dunslane.net, bruce@momjian.us, tgl@sss.pgh.pa.us, craig@postnewspapers.com.au
Subject: Re: [HACKERS] Slow count(*) again...
In-Reply-To: <4D4B2DA0.3040601@2ndquadrant.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