pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDoes VACUUM FULL need ANALYZE ?
5+ messages / 4 participants
[nested] [flat]
* Does VACUUM FULL need ANALYZE ?
@ 2015-06-20 20:02 Eoin O'Hehir <eoin.ohehir@clavisinsight.com>
2015-06-22 00:30 ` Re: Does VACUUM FULL need ANALYZE ? Venkata Balaji N <nag1010@gmail.com>
2015-06-26 19:40 ` Re: Does VACUUM FULL need ANALYZE ? Kevin Grittner <kgrittn@ymail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Eoin O'Hehir @ 2015-06-20 20:02 UTC (permalink / raw)
To: pgsql-admin
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 ?
Thanks,
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Does VACUUM FULL need ANALYZE ?
2015-06-20 20:02 Does VACUUM FULL need ANALYZE ? Eoin O'Hehir <eoin.ohehir@clavisinsight.com>
@ 2015-06-22 00:30 ` Venkata Balaji N <nag1010@gmail.com>
2015-06-29 07:46 ` Re: Does VACUUM FULL need ANALYZE ? Eoin O'Hehir <eoin.ohehir@clavistechnology.com>
1 sibling, 1 reply; 5+ messages in thread
From: Venkata Balaji N @ 2015-06-22 00:30 UTC (permalink / raw)
To: Eoin O'Hehir <eoin.ohehir@clavisinsight.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Does VACUUM FULL need ANALYZE ?
2015-06-20 20:02 Does VACUUM FULL need ANALYZE ? Eoin O'Hehir <eoin.ohehir@clavisinsight.com>
2015-06-22 00:30 ` Re: Does VACUUM FULL need ANALYZE ? Venkata Balaji N <nag1010@gmail.com>
@ 2015-06-29 07:46 ` Eoin O'Hehir <eoin.ohehir@clavistechnology.com>
0 siblings, 0 replies; 5+ messages in thread
From: Eoin O'Hehir @ 2015-06-29 07:46 UTC (permalink / raw)
To: CAEyp7J8DDnCv2cFWNExq+=8f5QH0ttY2JGBbpBOgBRRMVCn_GA@mail.gmail.com; +Cc: pgsql-admin
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 <mailto:eoin.ohehir@clavisinsight.com>>
> wrote:
>
>
> Eoin O'Hehir <eoin.ohehir@clavisinsight.com
> <mailto: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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Does VACUUM FULL need ANALYZE ?
2015-06-20 20:02 Does VACUUM FULL need ANALYZE ? Eoin O'Hehir <eoin.ohehir@clavisinsight.com>
@ 2015-06-26 19:40 ` Kevin Grittner <kgrittn@ymail.com>
2015-06-29 07:43 ` Re: Does VACUUM FULL need ANALYZE ? Eoin O'Hehir <eoin.ohehir@clavistechnology.com>
1 sibling, 1 reply; 5+ messages in thread
From: Kevin Grittner @ 2015-06-26 19:40 UTC (permalink / raw)
To: Eoin O'Hehir <eoin.ohehir@clavisinsight.com>; pgsql-admin
Eoin O'Hehir <eoin.ohehir@clavisinsight.com> wrote:
> I'm setting up a weekly DB maintenance job for a 9.3 server.
>
> Part of this will be a VACUUM FULL.
That's something to seriously reconsider.
> Is there any point in adding the ANALYZE option to the command ?
That and a non-FULL VACUUM command after VACUUM FULL. Unlike what
you might expect based on the FULL option, a VACUUM FULL does not
do everything that a non-FULL VACUUM does, and actually drops any
existing visibility map and free space map -- potentially making
subsequent queries against the table slower until a normal VACUUM
(or autovacuum occurs). I strongly recommend that if you really do
need to use VACUUM FULL, you immediately follow it with VACUUM
ANALYZE.
You might also want to read this:
http://rhaas.blogspot.com/2014/03/vacuum-full-doesnt-mean-vacuum-but.html
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Does VACUUM FULL need ANALYZE ?
2015-06-20 20:02 Does VACUUM FULL need ANALYZE ? Eoin O'Hehir <eoin.ohehir@clavisinsight.com>
2015-06-26 19:40 ` Re: Does VACUUM FULL need ANALYZE ? Kevin Grittner <kgrittn@ymail.com>
@ 2015-06-29 07:43 ` Eoin O'Hehir <eoin.ohehir@clavistechnology.com>
0 siblings, 0 replies; 5+ messages in thread
From: Eoin O'Hehir @ 2015-06-29 07:43 UTC (permalink / raw)
To: pgsql-admin
Thanks Kevin. That provides a lot of clarity. Cheers.
On 26/06/15 20:40, Kevin Grittner wrote:
> Eoin O'Hehir <eoin.ohehir@clavisinsight.com> wrote:
>
>> I'm setting up a weekly DB maintenance job for a 9.3 server.
>>
>> Part of this will be a VACUUM FULL.
> That's something to seriously reconsider.
>
>> Is there any point in adding the ANALYZE option to the command ?
> That and a non-FULL VACUUM command after VACUUM FULL. Unlike what
> you might expect based on the FULL option, a VACUUM FULL does not
> do everything that a non-FULL VACUUM does, and actually drops any
> existing visibility map and free space map -- potentially making
> subsequent queries against the table slower until a normal VACUUM
> (or autovacuum occurs). I strongly recommend that if you really do
> need to use VACUUM FULL, you immediately follow it with VACUUM
> ANALYZE.
>
> You might also want to read this:
>
> http://rhaas.blogspot.com/2014/03/vacuum-full-doesnt-mean-vacuum-but.html
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2015-06-29 07:46 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-06-20 20:02 Does VACUUM FULL need ANALYZE ? Eoin O'Hehir <eoin.ohehir@clavisinsight.com>
2015-06-22 00:30 ` Venkata Balaji N <nag1010@gmail.com>
2015-06-29 07:46 ` Eoin O'Hehir <eoin.ohehir@clavistechnology.com>
2015-06-26 19:40 ` Kevin Grittner <kgrittn@ymail.com>
2015-06-29 07:43 ` Eoin O'Hehir <eoin.ohehir@clavistechnology.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox