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

Re: VACUUM ANALYZE is faster than ANALYZE?

From: "ktm(at)rice(dot)edu" <ktm(at)rice(dot)edu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Simon Riggs <simon(at)2ndQuadrant(dot)com>,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:35:51
Message-ID: 20120222153551.GN21114@aart.rice.edu (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wed, Feb 22, 2012 at 10:29:56AM -0500, Tom Lane 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.  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
> 

Wouldn't a full sequential scan trigger the kernel read-ahead, which
might not trigger for the analyze block reads, even though they are
in order? That could account for the observation.

Regards,
Ken

In response to

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2012-02-22 15:56:50
Subject: Re: leakproof
Previous:From: Tom LaneDate: 2012-02-22 15:29:56
Subject: Re: VACUUM ANALYZE is faster than ANALYZE?

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