postgresql vacuum analyze many tables

Question

I have a medium-size table (about 15 billion rows) that is made of roughly 200 sub-tables (i.e. each sub-table INHERITS the mother table, and there are constraint CHECKS to optimize the partitioning).

The plpgsql code that loads each table also produces indexes on each of the sub-tables, but unfortunately (because it is a function), it cannot also execute a vacuum analyze subtable; for the sub-table it has just loaded and indexed.

So, until the next vacuum analyze of the whole DB is done, all the queries on the mother table suck and don't use the indexes (as revealed by explain).

Note that this is all with PostgreSQL 8.2.14.

Questions

1. Is there a way to have the plpgsql function say, please pretty please with sugar on top, end transaction block and perform a vacuum analyze subtable?
2. Is there a way to vacuum/analyze many tables at once using some wildcard (vacuum analyze schema.subtables*)?
3. Alternatively, is it possible to vacuum/analyze just one schema?
4. Is there any other way to vacuum/analyze the 200 sub-tables in a programmatic fashion from within postgresql (yes, I can dump the name of all sub-tables, toss and season to taste with some perl into a psql script, and execute that, but it's kinda ugly).

Answer 1

Note that this is all with PostgreSQL 8.2.14.

8.2 went out of support last year, after a five year run, and 8.2.14 is missing lots of fixes security vulnerabilities and bugs which were discovered after 2009-03-16. You desperately need to move onto a version from this decade.

Is there any other way to vacuum/analyze the 200 sub-tables in a programmatic fashion from within postgresql (yes, I can dump the name of all sub-tables, toss and season to taste with some perl into a psql script, and execute that, but it's kinda ugly).

You could create entries in pg_autovacuum and leave it to the autovacuum daemon. Of course, that daemon has gotten a lot better in recent years, so that's another reason to update.