From: Alvaro Herrera <alvherre@commandprompt.com>
To: Jozsef Szalay <jszalay@storediq.com>
Cc: Bill Moran <wmoran@collaborativefusion.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Simple select hangs while CPU close to 100% - Analyze
Date: Fri, 17 Aug 2007 23:03:49 -0400
Message-ID: <20070818030349.GC13349@alvh.no-ip.org> (raw)
In-Reply-To: <E387E2E9622FDD408359F98BF183879EE4E966@dc1.storediq.com>
References: <20070725162834.c18bd204.wmoran@collaborativefusion.com>
<E387E2E9622FDD408359F98BF183879EE4E966@dc1.storediq.com>
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.
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: alvherre@commandprompt.com, jszalay@storediq.com, wmoran@collaborativefusion.com, pavel.stehule@gmail.com
Subject: Re: Simple select hangs while CPU close to 100% - Analyze
In-Reply-To: <20070818030349.GC13349@alvh.no-ip.org>
* 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