Potential autovacuum optimization: new tables

Folks,

One chronic problem users encounter is this one:

1. User creates new table
2. User inserts 45 records into new table.
3. Time passes.
4. User creates a query which joins against new table.
5. Planner uses estimate of 1000 rows for the new table.
6. User gets very bad query plan.

Now, I look at this, and ask myself: why didn't autoanalyze kick in at
step 3? After all, this was a table which had 0 rows, we inserted 45
rows, making the table infinitely larger. It should have got on the
autoanalyze list, no?

Well, no. It seems that any table with less than
autovacuum_analyze_threshold rows will NEVER be autoanalyzed. Ever.

postgres=# create table thirty_rows ( val int );
CREATE TABLE ^
postgres=# insert into thirty_rows select i from generate_series(1,30)
as gs(i);
INSERT 0 30

postgres=# create table onetwenty_rows ( val int );
CREATE TABLE
postgres=# insert into onetwenty_rows select i from
generate_series(1,120) as gs(i);
INSERT 0 120

postgres=# create table twocent_rows ( val int );
CREATE TABLE
postgres=# insert into twocent_rows select i from generate_series(1,200)
as gs(i);

... wait 5 min ...

postgres=# select relname, last_autoanalyze from pg_stat_user_tables
where relname like '%_rows';
relname | last_autoanalyze
----------------+-------------------------------
thirty_rows |
twocent_rows | 2012-10-12 16:46:45.025647-07
onetwenty_rows | 2012-10-12 16:46:45.014084-07

postgres=# select * from pg_stats where tablename = 'thirty_rows';
schemaname | tablename | attname | inherited | null_frac | avg_width |
n_distinct | most_common_vals | most_common_freqs | histogram_bounds |
correlation | most_common_elems | most_common_elem_freqs |
elem_count_histogram
(0 rows)

This seems easy to fix. If a table has no stats and has any write stats
at all, it should automatically go on the autoanalyze list. Or if it's
easier, one where last_autoanalyze is null.

Objections/complications/alternatives?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com