Re: idle process & autovacuum

On 10-07-30 10:52 AM, Gauthier, Dave wrote:
>
> Hi:
>
> I have a perl/dbi script that's loading a lot of data into a DB
> (probably about 8,000,000 records). pg_stat_activity shows the
> script's activity alongside another process with current_query as...
>
> autovacuum: ANALYZE public.instance_pins
>
> The total instance_pins table cardinality is about 60,000,000. The
> script inserts records but never updates or deletes anything. And
> before the script runs, the table is truncated. The script's process
> seems to be stalled in the meantime.
>

The analyze that is running on that table will block the truncate of
that until the analyze completes. You can check pg_locks for to see if
there are any ungranted locks relating to the script you are running.

> This is a new phenomena as of yesterday. The only change is that I
> added an index on that table. But I created the new index yesterday
> (create index...) and it completed OK.
>
> What does this message mean in that context? Is this indicative of
> someting being misconfigured in the DB?
>

The message means that the data in instance_pins has changed enough for
autovacuum to run an analyze on the table. It's doing this to
re-generate statistics for the table for the query planner to use.

--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.