From: "Sabin Coanda" <sabin.coanda@deuromedia.ro>
Subject: vacuum a lot of data when insert only
Date: Thu, 21 Jun 2007 19:53:54 +0300
Organization: Hub.Org Networking Services
Lines: 54
Message-ID: <f5eaek$19hl$1@news.hub.org>
To: pgsql-performance@postgresql.org

Hi there,

Reading different references, I understand there is no need to vacuum a 
table where just insert actions perform. So I'm surprising to see a table 
with just historical data, which is vacuumed at the nightly cron with a 
simple VACUUM VERBOSE on about 1/3 of indexes amount.

Take a look on the fragment log concerning this table:
INFO:  vacuuming "public.tbTEST"
INFO:  scanned index "tbTEST_pkey" to remove 1357614 row versions
DETAIL:  CPU 0.31s/1.38u sec elapsed 4.56 sec.
INFO:  "tbTEST": removed 1357614 row versions in 16923 pages
DETAIL:  CPU 0.70s/0.13u sec elapsed 2.49 sec.
INFO:  index "tbTEST_pkey" now contains 2601759 row versions in 12384 pages
DETAIL:  1357614 index row versions were removed.
5415 index pages have been deleted, 2452 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "tbTEST": found 1357614 removable, 2601759 nonremovable row versions 
in 49153 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 29900 unused item pointers.
16923 pages contain useful free space.
0 pages are entirely empty.
CPU 2.12s/1.87u sec elapsed 11.41 sec.
INFO:  "tbTEST": truncated 49153 to 32231 pages
DETAIL:  CPU 0.23s/0.06u sec elapsed 0.31 sec.

I found the following statistics in pg_stat_user_tables:
n_tup_ins = 11444229
n_tup_upd = 0
n_tup_del = 0

The structure of the table is the following:
CREATE TABLE "tbTEST"
(
  "PK_ID" integer NOT NULL DEFAULT nextval('"tbTEST_PK_ID_seq"'::regclass),
  "FK_SourceTypeID" integer,
  "SourceID" integer DEFAULT -1,
  "Message" character varying(500) NOT NULL DEFAULT ''::character varying,
  "DateAndTime" timestamp without time zone NOT NULL,
  CONSTRAINT "tbTEST_pkey" PRIMARY KEY ("PK_ID"),
  CONSTRAINT "tbTEST_FK_SourceTypeID_fkey" FOREIGN KEY ("FK_SourceTypeID")
      REFERENCES "tbLISTS" ("PK_ID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Postgres version is 8.2.3.

What's happen ?

TIA,
Sabin