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

Re: VACUUM ANALYZE is faster than ANALYZE?

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, 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 19:12:35
Message-ID: CAFj8pRB7Qd=Ew4CUOx6J4U+ujjTcyv3m24_kneOCEQfe2Ti7mQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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?
>
> -Kevin

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.

Thank You

Pavel

In response to

Responses

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2012-02-22 19:14:10
Subject: Re: determining a type oid from the name
Previous:From: Peter GeogheganDate: 2012-02-22 19:11:17
Subject: Re: Publish checkpoint timing and sync files summary data to pg_stat_bgwriter

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