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
