Message-ID: <4D4B2DA0.3040601@2ndquadrant.com>
Date: Thu, 03 Feb 2011 17:35:12 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: david@lang.hm
CC: Robert Haas <robertmhaas@gmail.com>,
	Vitalii Tymchyshyn <tivv00@gmail.com>,
	Jon Nelson <jnelson+pgsql@jamponi.net>,
	Mladen Gogala <mladen.gogala@vmsinfo.com>,
	Andrew Dunstan <andrew@dunslane.net>, Bruce Momjian <bruce@momjian.us>,
	Tom Lane <tgl@sss.pgh.pa.us>, Craig Ringer <craig@postnewspapers.com.au>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: [HACKERS] Slow count(*) again...
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>
In-Reply-To: <alpine.DEB.2.00.1102031243190.10088@asgard.lang.hm>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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