| From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
|---|---|
| To: | Jozsef Szalay <jszalay(at)storediq(dot)com> |
| Cc: | Bill Moran <wmoran(at)collaborativefusion(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: Simple select hangs while CPU close to 100% - Analyze |
| Date: | 2007-08-18 03:03:49 |
| Message-ID: | 20070818030349.GC13349@alvh.no-ip.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Alvaro Herrera | 2007-08-18 03:08:04 | Re: Performance Solaris vs Linux |
| Previous Message | Scott Marlowe | 2007-08-18 01:14:01 | Re: select count(*) performance |