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: 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:36:38
Message-ID: CAFj8pRBE1XM0pUyhqXTjo2G5r5ay_U-zEBx_nEAfwwXKYHAhow@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
2012/2/22 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Wed, Feb 22, 2012 at 8:13 AM, Nicolas Barbier
> <nicolas(dot)barbier(at)gmail(dot)com> wrote:
>> 2012/2/22 Robert Haas <robertmhaas(at)gmail(dot)com>:
>>
>>> On Tue, Feb 21, 2012 at 9:00 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>>
>>>> I had to reply to query about usage VACUUM ANALYZE or ANALYZE. I
>>>> expected so ANALYZE should be faster then VACUUM ANALYZE.
>>>>
>>>> But is not true. Why?
>>>
>>> I'm pretty sure that VACUUM ANALYZE *will* be faster than ANALYZE in
>>> general, because VACUUM has to scan the whole table, and ANALYZE only
>>> a fixed-size subset of its pages.
>>
>> It sounds like you just said the opposite of what you wanted to say.
>
> Yeah, I did.  Woops.  Let me try that again:
>
> ANALYZE should be faster; reads only some pages.
>
> VACUUM ANALYZE should be slower; reads them all.
>
> Dunno why Pavel's seeing the opposite without more info.

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.

Regards

Pavel

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

In response to

Responses

pgsql-hackers by date

Next:From: Marti RaudseppDate: 2012-02-22 18:45:47
Subject: Re: pg_test_timing tool for EXPLAIN ANALYZE overhead
Previous:From: Thom BrownDate: 2012-02-22 18:36:35
Subject: Re: determining a type oid from the name

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