| Hello all! I have a table with weekly partitions, going back about 9 months. This is a rollup table, and I update the values in the table once per day, plus a final refresh for the previous week on Monday. The parent table has no rows, nor should it contain any. The rollup script does this: BEGIN; DELETE FROM summary_show_unique_personas WHERE period >= '2012-12-02' AND period < '2012-12-09'; INSERT INTO summary_show_unique_personas SELECT ... FROM ...; COMMIT; ANALYZE summary_show_unique_personas; The same query runs for the weekly and daily refreshes, with appropriate dates. My question concerns the following paragraph in the ANALYZE command (http://www.postgresql.org/docs/current/static/sql-analyze.html): """ If the table being analyzed has one or more children,
ANALYZE will gather statistics twice:
once on the rows of the parent table only, and a second time on
the rows of the parent table with all of its children. This
second set of statistics is needed when planning queries that
traverse the entire inheritance tree. The autovacuum daemon,
however, will only consider inserts or updates on the parent
table itself when deciding whether to trigger an automatic
analyze for that table. If that table is rarely inserted into or
updated, the inheritance statistics will not be up to date unless
you run ANALYZE manually. """ According to this, I believe the child tables will be refreshed as needed by the autovacuum daemon, and the parent table's statistics when I run the ANALYZE statement manually. I sometimes catch the autovacuum daemon running against the child tables. All the queries I run are run using the following template: SELECT ... FROM summary_show_unique_personas WHERE period >= '...' AND period < '...' Thanks for any tips or pointers! François |
