Skip site navigation (1) Skip section navigation (2)

Re: VACUUM ANALYZE is faster than ANALYZE?

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>, "Robert Haas" <robertmhaas(at)gmail(dot)com>
Cc: "Nicolas Barbier" <nicolas(dot)barbier(at)gmail(dot)com>, "PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: VACUUM ANALYZE is faster than ANALYZE?
Date: 2012-02-22 18:57:12
Message-ID: 4F44E6280200002500045A2F@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-hackers
Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
 
> usual pattern in our application is
> 
> create table xx1 as select ....
> analyze xx1
> create table xx2 as select .... from xx1, ....
> analyze xx2
> create table xx3 as select ... from xx3, ....
> analyze xx3
> create table xx4 as select ... from xx1, ...
> 
> tables xx** are use as cache.
> 
> so we have to refresh statistic early.
> 
> in this situation - and I found so in this case VACUUM ANALYZE is
> faster (30%) than ANALYZE. Size of xx** is usually between 500Kb
> and 8Kb
> 
> This is not usual pattern for OLTP - Application is strictly OLAP.
 
Is the VACUUM ANALYZE step faster, or is the overall job faster if
VACUUM ANALYZE is run?  You may be running into the need to rewrite
pages at an inopportune time or order without the VACUUM.  Have you
tried getting a time VACUUM FREEZE ANALYZE on these cache tables
instead of plain VACUUM ANALYZE?
 
-Kevin

In response to

Responses

pgsql-hackers by date

Next:From: Peter GeogheganDate: 2012-02-22 19:11:17
Subject: Re: Publish checkpoint timing and sync files summary data to pg_stat_bgwriter
Previous:From: Marti RaudseppDate: 2012-02-22 18:45:47
Subject: Re: pg_test_timing tool for EXPLAIN ANALYZE overhead

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group