X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7E5EAD1B557 for ; Thu, 5 Feb 2004 16:23:57 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 59849-05 for ; Thu, 5 Feb 2004 12:23:54 -0400 (AST) Received: from sycorax.affordablehost.com (sycorax.affordablehost.com [216.46.192.84]) by svr1.postgresql.org (Postfix) with ESMTP id 7B946D1B49E for ; Thu, 5 Feb 2004 12:23:53 -0400 (AST) Received: from h0040054d8ee4.ne.client2.attbi.com ([66.31.57.228] helo=e-greek.net) by sycorax.affordablehost.com with asmtp (TLSv1:RC4-MD5:128) (Exim 4.24) id 1AomHs-0003Ul-Qx; Thu, 05 Feb 2004 11:23:33 -0500 Message-ID: <40226E06.2080701@e-greek.net> Date: Thu, 05 Feb 2004 11:23:34 -0500 From: Andrew Biagioni User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en, it, it-ch, fr-fr, fr-ch, de-de, de-ch MIME-Version: 1.0 To: Iain Cc: 'Edoardo Ceccarelli' , pgsql-admin@postgresql.org Subject: Re: R: slow seqscan after vacuum analize References: <2s28b9$kqpmo@mailr-1.tiscali.it> <006401c3ebb1$d4237c60$7201a8c0@mst1x5r347kymb> In-Reply-To: <006401c3ebb1$d4237c60$7201a8c0@mst1x5r347kymb> Content-Type: multipart/alternative; boundary="------------010004010208020209080806" X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - sycorax.affordablehost.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - e-greek.net X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/57 X-Sequence-Number: 12231 This is a multi-part message in MIME format. --------------010004010208020209080806 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Iain wrote: > > >>Yes, you are right but it wasn't the case this time, I have run the >> >> >explain plenty of > > >>times with same results. I think that the reason was that I made a simple >> >> >VACUUM, > > >>after a VACUUM FULL ANALYZE (1h!!) things are ok >> >> > >It's reasonable to expect that a seq scan will perform faster after a full >vacuum, as the physical size and organization of the table has been changed. > >I wouldn't expact a plain vacuum to have any tangible affect on performance, >for the better or for the worse.. > > A plain VACUUM (without ANALYZE) will change the layout of the data without refreshing the optimizer information, so that anything that DOES use the optimizer will often be negatively affected; VACUUM ANALYZE does a vacuum AND refreshes the optimizer information. I usually run VACUUM ANALYZE on an hourly basis on our production system, and it's fast enough and unobtrusive enough that I can't tell that it's running (except by looking at the log, of course). >I'd like to know more about the possibility of plain vacuums harming >performance. This is the first I've heard of it. Vacuum full is not always >an option in a production environment. > > >---------------------------(end of broadcast)--------------------------- >TIP 7: don't forget to increase your free space map settings > --------------010004010208020209080806 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Iain wrote:
  
Yes, you are right but it wasn't the case this time, I have run the
    
explain plenty of
  
times with same results. I think that the reason was that I made a simple
    
VACUUM,
  
after a VACUUM FULL ANALYZE (1h!!) things are ok
    

It's reasonable to expect that a seq scan will perform faster after a full
vacuum, as the physical size and organization of the table has been changed.

I wouldn't expact a plain vacuum to have any tangible affect on performance,
for the better or for the worse..
  
A plain VACUUM (without ANALYZE) will change the layout of the data without refreshing the optimizer information, so that anything that DOES use the optimizer will often be negatively affected;  VACUUM ANALYZE does a vacuum AND refreshes the optimizer information. 

I usually run VACUUM ANALYZE on an hourly basis on our production system, and it's fast enough and unobtrusive enough that I can't tell that it's running (except by looking at the log, of course).
I'd like to know more about the possibility of plain vacuums harming
performance. This is the first I've heard of it. Vacuum full is not always
an option in a production environment.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

--------------010004010208020209080806--