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

Re: VACUUM ANALYZE is faster than ANALYZE?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Simon Riggs <simon(at)2ndQuadrant(dot)com>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: VACUUM ANALYZE is faster than ANALYZE?
Date: 2012-02-22 15:29:56
Message-ID: 17867.1329924596@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Tue, Feb 21, 2012 at 2:00 PM, 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.

> VACUUM ANALYZE scans the whole table sequentially.

> ANALYZE accesses a random sample of data blocks. Random access is
> slower than sequential access, so at some threshold of sample size and
> sequential/random I/O speed ratio ANALYZE could become slower.

That analysis is entirely wrong.  In the first place, although ANALYZE
doesn't read all the blocks, what it does read it reads in block number
order.  So it's not like there are "random" seeks all over the disk that
would not need to happen anyway.  In the second place, VACUUM ANALYZE
consists of two separate passes, VACUUM and then ANALYZE, and the second
pass is going to be "random" I/O by your definition no matter what.

If the filesystem is hugely biased towards sequential I/O for some
reason, and the VACUUM scan causes the whole table to become resident in
RAM where ANALYZE can read it "for free", then I guess it might be
possible to arrive at Pavel's result.  But it would be an awfully narrow
corner case.  I cannot believe that his statement is true in general,
or even for a noticeably large fraction of cases.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: ktm@rice.eduDate: 2012-02-22 15:35:51
Subject: Re: VACUUM ANALYZE is faster than ANALYZE?
Previous:From: Tom LaneDate: 2012-02-22 15:21:47
Subject: Re: leakproof

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