2012/2/22 Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>:
> 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?
vacuum freeze analyze is slower as expected. vacuum analyze is little
bit faster or same in any step then analyze.
I expected so just analyze should be significantly faster and it is not.
Tom's demonstration is enough for me. ANALYZE doesn't read complete
table, but uses random IO. VACUUM ANALYZE reads complete table, but it
uses seq IO and vacuum is fast (because it does nothing) in our case.
In response to
pgsql-hackers by date
|Next:||From: Andrew Dunstan||Date: 2012-02-22 19:14:10|
|Subject: Re: determining a type oid from the name|
|Previous:||From: Peter Geoghegan||Date: 2012-02-22 19:11:17|
|Subject: Re: Publish checkpoint timing and sync files summary data