Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Z9Tm9-0004li-I4 for pgsql-admin@arkaria.postgresql.org; Mon, 29 Jun 2015 07:46:45 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1Z9Tm8-0004QS-Ic for pgsql-admin@arkaria.postgresql.org; Mon, 29 Jun 2015 07:46:44 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1Z9Tm7-0004NV-0H for pgsql-admin@postgresql.org; Mon, 29 Jun 2015 07:46:43 +0000 Received: from mail-wi0-f181.google.com ([209.85.212.181]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1Z9Tm3-0007VA-If for pgsql-admin@postgresql.org; Mon, 29 Jun 2015 07:46:41 +0000 Received: by wicnd19 with SMTP id nd19so90875290wic.1 for ; Mon, 29 Jun 2015 00:46:38 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:message-id:date:from:user-agent:mime-version:to :cc:subject:references:in-reply-to:content-type; bh=aktSqjD69ETaq2zQsVqnas+9AzUcs8bsn5GQ0kTgHxk=; b=CtqDvTXFr5rCdKm/j1S4+cSjxyEzE7YyXDMXROiipLQMqilaCIKyWrwT1aG6rvow0j EOEreYaq+cSrWv7GOgYQPiO7+3NNkCvPBujDDvg7nXhnJWL4T87NlH0zEKe3LWkxP50q uGXSLzu9YB3Y8JbzKp3lcPOwzcRAMcaL6/7yDRPy41qX4CrmgQcFszEBsRpDCw/W5d3Y Dx4GdUEfMgVODZiJoQEw5xgSoyBsPI00W9ajaL5Yks560V9Lpqztgy5LRlv5ptJCuSot kCTNoSXZPdsWB4cFwGGC0xriLuBI6nJBsTP7HR9ul+rjN1dige6G0M8DwwZuX4TZSqtr ATsw== X-Gm-Message-State: ALoCoQn80XtCQLK0W/YXqXQoibiV87TVLPmO6v31C+XJpEgOmJ88GJimHugyg5ozsifLF5v0tJNP X-Received: by 10.180.94.39 with SMTP id cz7mr18721212wib.66.1435563998016; Mon, 29 Jun 2015 00:46:38 -0700 (PDT) Received: from [192.168.21.49] (remote.clavistechnology.com. [87.198.179.238]) by mx.google.com with ESMTPSA id i6sm62730281wjf.29.2015.06.29.00.46.36 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 29 Jun 2015 00:46:37 -0700 (PDT) Message-ID: <5590F7DC.4050902@clavistechnology.com> Date: Mon, 29 Jun 2015 08:46:36 +0100 From: Eoin O'Hehir User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Thunderbird/31.7.0 MIME-Version: 1.0 To: CAEyp7J8DDnCv2cFWNExq+=8f5QH0ttY2JGBbpBOgBRRMVCn_GA@mail.gmail.com CC: pgsql-admin@postgresql.org Subject: Re: Does VACUUM FULL need ANALYZE ? References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------010409070300040502010200" X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------010409070300040502010200 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit Thanks Venkata. On 22/06/15 01:30, Venkata Balaji N wrote: > > On Sun, Jun 21, 2015 at 6:02 AM, Eoin O'Hehir > > > wrote: > > > Eoin O'Hehir > > > 8:59 PM (0 minutes ago) > to pgsql-admin-ow. > > Hi, > > I'd be grateful for some insight into the VACUUM functionality in > 9.3. I read through the documentation and searched around a bit > but could not find a definitive answer to this. > > I'm setting up a weekly DB maintenance job for a 9.3 server. > > Part of this will be a VACUUM FULL. > > Is there any point in adding the ANALYZE option to the command ? > > According to the 9.3 documentation, "/VACUUM FULL rewrites the > entire contents of the table into a new disk file with no extra > space, allowing unused space to be returned to the operating > system./". If the table is completely recreated then do I need to > specifically ask for ANALYZE to happen or are the statistics > updated automatically as the rows are written to the new version > of the table ? > > > VACUUM FULL will only remove the bloats and releases the bloated space > to the disk. ANALYZE updates the statistics which will make > significant difference in the query's cost calculation. > > Regards, > Venkata B N > > Fujitsu Australia --------------010409070300040502010200 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Thanks Venkata.

On 22/06/15 01:30, Venkata Balaji N wrote:

On Sun, Jun 21, 2015 at 6:02 AM, Eoin O'Hehir <eoin.ohehir@clavisinsight.com> wrote:

Eoin O'Hehir <eoin.ohehir@clavisinsight.com>

8:59 PM (0 minutes ago)
to pgsql-admin-ow.
Hi,

I'd be grateful for some insight into the VACUUM functionality in 9.3. I read through the documentation and searched around a bit but could not find a definitive answer to this.

I'm setting up a weekly DB maintenance job for a 9.3 server.

Part of this will be a VACUUM FULL.

Is there any point in adding the ANALYZE option to the command ?

According to the 9.3 documentation, "VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system.". If the table is completely recreated then do I need to specifically ask for ANALYZE to happen or are the statistics updated automatically as the rows are written to the new version of the table ?

VACUUM FULL will only remove the bloats and releases the bloated space to the disk. ANALYZE updates the statistics which will make significant difference in the query's cost calculation.

Regards,
Venkata B N

Fujitsu Australia

--------------010409070300040502010200--