Hello, I can see why that would be a good practice if you do a daily load, but the goal is to have a 'live' dwh. with updated statistics every so-many-hours.
Thank you for the information anyway. I guess I will ask more questions here when time passes. And I'll try to share some knowlegde too! :) cheers, Bert On Wed, Jan 23, 2013 at 4:45 PM, Prashanth Ranjalkar < prashant.ranjal...@gmail.com> wrote: > *Hi Bert,* > ** > *Vaccum analyze operation would be a time consuming activity when it > operates on partitioned table in parent and child relationship by using a > manual vaccum option. When vaccum operation is performed the total > vacuum/analyze time would be total time on completion of the said actvity > on all child table in spite of parent table is empty. If you > are specifically mentioning the child and parent table names in the > script then the timing would be different and independent of each table > which would be less..* > ** > *To answer your second question, it's good to disable autovacuum on table > level for those tables which are part of ETL data load operation to avoid > the interfierence of autovaccum while loading data and it would be good > practice to analyze the tables from ETL script itself so that stats are up > to date to get throughput of the application query perfoamance. * > ** > > ** > *Thanks & Regards,* > *** * > *Prashanth Ranjalkar* > *Database Consultant & Architect* > *Email:prashant.ranjal...@gmail.com* > *Skype:prashanth.ranjalkar* > *Cell: +91 932 568 2271* > > > On Wed, Jan 23, 2013 at 8:16 PM, Bert <bier...@gmail.com> wrote: > >> 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 <ruralhun...@gmail.com>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@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/**mailpref/pgsql-admin<http://www.postgresql.org/mailpref/pgsql-admin> >>> >> >> >> >> -- >> >> > -- Bert Desmet 0477/305361
