Re: queries are fast after dump->restore but slow again after some days dispite vacuum

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

 




On 07/19/2012 11:13 AM, Felix Scheicher wrote:
Andrew Dunstan <andrew <at> dunslane.net> writes:

Try running CLUSTER on the relevant tables and see if it makes a
difference. If it does you might want to look into using pg_reorg
periodically.

That worked like a charm! Many thanks. But how comes, the queries are also fast
after a restore without the cluster?




There is probably a lot of unused space in your table. CLUSTER rewrites a fresh copy, as do restore and pg_reorg.

You might also want to try changing the settings on the table so it gets much more aggressively auto-vacuumed, so that dead space is made available much more quickly, and the table has less chance to get bloated.

cheers

andrew

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