From: Rural Hunter <ruralhunter@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: vacuum / analyze parent tables on partitioned tables.
Date: Wed, 23 Jan 2013 22:40:25 +0800
Message-ID: <50FFF659.6070003@gmail.com> (raw)
In-Reply-To: <CAFCtE1k+8iRr2OnSRr6vaRC7+ArqMShZ3Tnd_H1tqV-ahhpsng@mail.gmail.com>
References: <CAFCtE1k+8iRr2OnSRr6vaRC7+ArqMShZ3Tnd_H1tqV-ahhpsng@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
I'm doing the same thing. In my case, the vacuum part on parent is very
quick while analyzing takes a bit longer since it runs rough analyzes
all children tables. You can see the behavior by "analyze verbose".
Maybe the bigger part of your vacuum/analyze is on analyze so that you
are seeing this result.
δΊ 2013/1/23 19:43, Bert ει:
> Hello,
>
> I first wrote, by mistake, to the sql mailing list. But here is my
> e-mail:
>
> I wrote a script to make sure all tables are vacuumed and analyzed
> every evening. This works very well.
> I save in a table the start and end time of a vacuum/analyze. This way
> I can measure what tables take a long time to vaccum/analyze, and what
> tables are slow. (and much more).
>
> But I have noticed that the parent table of a partitioned table also
> takes a long time. Here is a snap shot of the following table
>
> table_name ; avg runt time ; max run time ; min
> run time
> "f_transaction_1" ; "00:03:07.8" ; "00:03:10" ; "00:03:03"
> "f_transaction" ; "00:02:19.8" ; "00:02:25" ; "00:02:16"
>
> f_tranaction_1 is 16GB data + 12GB of indexes. (I know, a lot of
> indexes). f_tranaction is totally empy, but also contains all indexes.
> Which means 0B table zise, and 140kB index size.
>
> Does anyone has an idea why in this case the vacuum/analyze takes
> almost as long on the parent table as on the biggest child table? (the
> other child tables are smaller than f_tranaction_1, and their
> vacuum/analyze time is much shorter).
>
> wkr,
> Bert
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ruralhunter@gmail.com
Subject: Re: vacuum / analyze parent tables on partitioned tables.
In-Reply-To: <50FFF659.6070003@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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