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;
"""
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
