vacuum / analyze parent tables on partitioned tables.

Поиск
Список
Период
Сортировка
От Bert
Тема vacuum / analyze parent tables on partitioned tables.
Дата
Msg-id CAFCtE1k+8iRr2OnSRr6vaRC7+ArqMShZ3Tnd_H1tqV-ahhpsng@mail.gmail.com
обсуждение исходный текст
Ответы Re: vacuum / analyze parent tables on partitioned tables.  (Albe Laurenz <laurenz.albe@wien.gv.at>)
Re: vacuum / analyze parent tables on partitioned tables.  (Rural Hunter <ruralhunter@gmail.com>)
Список pgsql-admin
Дерево обсуждения
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

В списке pgsql-admin по дате отправления:

Предыдущее
От: "Benjamin Krajmalnik"
Дата:
Сообщение: Re: Schema design question as it pertains to performance
Следующее
От: Albe Laurenz
Дата:
Сообщение: Re: vacuum / analyze parent tables on partitioned tables.