Date: Fri, 17 Aug 2007 23:03:49 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Jozsef Szalay <jszalay@storediq.com>
Cc: Bill Moran <wmoran@collaborativefusion.com>,
	Pavel Stehule <pavel.stehule@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Simple select hangs while CPU close to 100% - Analyze
Message-ID: <20070818030349.GC13349@alvh.no-ip.org>
References: <20070725162834.c18bd204.wmoran@collaborativefusion.com>
	<E387E2E9622FDD408359F98BF183879EE4E966@dc1.storediq.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <E387E2E9622FDD408359F98BF183879EE4E966@dc1.storediq.com>
User-Agent: Mutt/1.5.16 (2007-06-11)

Jozsef Szalay escribió:

> I don't know why ANALYZE would have any effect on a sequential scan of a
> table but it does appear to impact both performance and memory usage
> significantly.

It doesn't.  What it does is provide the query optimizer with the
information that it needs to know that the table contains many different
values, which makes it turn the initial hashed aggregation into a sort
plus group aggregation.  This allows the aggregation to use less memory.

As an exercise, see an EXPLAIN of the query, both before and after the
analyze, and study the difference.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.