Re: Slow count(*) again...

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

 



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?

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

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