Skip site navigation (1) Skip section navigation (2)

Potential autovacuum optimization: new tables

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Potential autovacuum optimization: new tables
Date: 2012-10-12 23:53:15
Message-ID: 5078AD6B.8060802@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


Responses

pgsql-hackers by date

Next:From: John R PierceDate: 2012-10-13 00:42:03
Subject: Re: Successor of MD5 authentication, let's use SCRAM
Previous:From: Stephen FrostDate: 2012-10-12 23:25:37
Subject: Re: Successor of MD5 authentication, let's use SCRAM

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group