Re: Slow count(*) again...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wednesday 13 October 2010 06:27:34 you wrote:
> On Wed, Oct 13, 2010 at 4:38 AM, Neil Whelchel <neil.whelchel@xxxxxxxxx> 
wrote:
> > There seems to be allot of discussion about VACUUM FULL, and its
> > problems. The overall buzz seems to be that VACUUM FULL is a bad idea (I
> > could be wrong here). It has been some time since I have read the
> > changelogs, but I seem to remember that there have been some major
> > changes to VACUUM FULL recently. Maybe this needs to be re-visited in
> > the documentation.
> 
> In 9.0, VACUUM FULL does something similar to what CLUSTER does.  This
> is a much better idea than what it did in 8.4 and prior.
> 
> > crash:~# time psql -U test test -c "VACUUM FULL log;"
> > VACUUM
> > 
> > real    4m49.055s
> > user    0m0.000s
> > sys     0m0.000s
> > 
> > crash:~# time psql -U test test -c "SELECT count(*) FROM log;"
> >  count
> > ----------
> >  10050886
> > (1 row)
> > 
> > real    0m9.665s
> > user    0m0.000s
> > sys     0m0.004s
> > 
> > A huge improvement from the minute and a half before the VACUUM FULL.
> 
> This is a very surprising result that I would like to understand
> better.  Let's assume that your UPDATE statement bloated the table by
> 2x (you could use pg_relation_size to find out exactly; the details
> probably depend on fillfactor which you might want to lower if you're
> going to do lots of updates).  That ought to mean that count(*) has to
> grovel through twice as much data, so instead of taking 9 seconds it
> ought to take 18 seconds.  Where the heck is the other 1:12 going?
> This might sort of make sense if the original table was laid out
> sequentially on disk and the updated table was not, but how and why
> would that happen?
This is likely due to the table not fitting in memory before the VACUUM FULL.
I am glad that you suggested using pg_relation_size, I somehow didn't think of 
it at the time. I will redo the test and publish the results of 
pg_relation_size.
-Neil-

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux