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

Re: VACUUM ANALYZE is faster than ANALYZE?

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
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 19:23:10
Message-ID: CA+U5nMJLDVMqzrVpvrTBjcEYrsXMyPN2AtS668j9XWpr2K-QYQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wed, Feb 22, 2012 at 3:29 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

Entirely right it would seem, since your later comments match my own.

The industry accepted description for non-sequential access is "random
access" whether or not the function that describes the movement is
entirely random. To argue otherwise is merely hairsplitting.

The disk access is not-sequential for ANALYZE. Not-sequential access
is slower on some hardware, and so given a large enough sample it can
account for the observed difference.

Additional access to the disk while the ANALYZE was running would
actually make it fully random, if anyone really cares.


> 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.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2012-02-22 19:38:30
Subject: Re: pg_upgrade --logfile option documentation
Previous:From: Andrew DunstanDate: 2012-02-22 19:14:10
Subject: Re: determining a type oid from the name

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