Re: vacuum / analyze parent tables on partitioned tables.

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

 



Yes, at the moment that is still the case.

We are migrating from db2 to postgres, and are still in progress of migrating our ETL tool. So there isn't much moment in those tables at the moment.

We got big big problems with db2 auto runstats (sort of auto analyze) kicking in at random moments, that's the main reason we want to control it with postgres too.

I am still wondering why the children need to be analyzed, if we vacuum/analyze the childs seperatly.

but thank you for giving me some clarification.

cheers,
Bert


On Wed, Jan 23, 2013 at 3:40 PM, Rural Hunter <ruralhunter@xxxxxxxxx> wrote:
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



--


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux