Long-running and non-finishing VACUUM ANALYZE on large table - Mailing list pgsql-admin

Hi everyone,

I'm currently running PostgreSQL 9.3.6 on Ubuntu 14.04 Server and have
severe problems to get one large table properly vacuumed (16,743,000,000
reltuples).
That is, autovacuum on that table does not finish (even after six days
it is still running). To narrow down the problem, I stopped autovacuum,
temporarily disabled it (but ONLY on that particular table; not a
permanent solution, I know) and started a manual VACUUM ANALYZE on that
table via PGAdmin instead. In analogy to the autovacuum, the manually
started process is also running virtually endless (until I terminate it
after some days) but PGadmin's verbose output (see below) tells me at
least that it (apparently) tries to process the same table over and over
again. I'm definitely not a Postgres guru, but this can't be the
expected behaviour, even on such a big table, right?Did I overlook
something?

Hoping that this might be of help, you please find the following
information below:

1st) definition of the problematic table
2nd) the PGadmin output

Please let me know, if you need more information (e.g., specific
postgresql.conf settings).

Any help is greatly appreciated!

Kind regards,
Jan


####################
1st)
####################

CREATE TABLE protein_hsps
(
   id bigserial NOT NULL,
   origin bigint NOT NULL,
   bit_score real,
   e_value real,
   alignment_length smallint NOT NULL,
   query_start smallint NOT NULL,
   query_end smallint NOT NULL,
   subject_start integer NOT NULL,
   subject_end integer NOT NULL,
   identical smallint NOT NULL,
   positive smallint NOT NULL,
   reciprocal_best_hit boolean,
   CONSTRAINT protein_hsps_pkey PRIMARY KEY (id),
   CONSTRAINT protein_hsps_origin_fkey FOREIGN KEY (origin)
       REFERENCES protein_comparisons (id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
   OIDS=FALSE
);


-- Index: protein_hsps_clustidx_on_origin
CREATE INDEX protein_hsps_clustidx_on_origin
   ON protein_hsps
   USING btree
   (origin);


####################
2nd)
####################

Output of VACCUM ANALYZE when run in PGadmin:

INFO:  vacuuming "public.protein_hsps"
INFO:  scanned index "protein_hsps_pkey" to remove 178956773 row versions
DETAIL:  CPU 342.46s/2774.51u sec elapsed 4359.26 sec.
INFO:  scanned index "protein_hsps_clustidx_on_origin" to remove
178956773 row versions
DETAIL:  CPU 361.58s/2750.58u sec elapsed 4324.01 sec.
INFO:  "protein_hsps": removed 178956773 row versions in 1880456 pages
DETAIL:  CPU 30.06s/17.31u sec elapsed 160.45 sec.
INFO:  scanned index "protein_hsps_pkey" to remove 178956763 row versions
DETAIL:  CPU 360.96s/2751.12u sec elapsed 4330.33 sec.
INFO:  scanned index "protein_hsps_clustidx_on_origin" to remove
178956763 row versions
DETAIL:  CPU 356.65s/2737.37u sec elapsed 4288.50 sec.
INFO:  "protein_hsps": removed 178956763 row versions in 1902608 pages
DETAIL:  CPU 30.20s/18.78u sec elapsed 174.97 sec.
INFO:  scanned index "protein_hsps_pkey" to remove 178956767 row versions
DETAIL:  CPU 358.25s/2812.84u sec elapsed 4448.54 sec.
INFO:  scanned index "protein_hsps_clustidx_on_origin" to remove
178956767 row versions
DETAIL:  CPU 365.69s/2835.24u sec elapsed 4513.28 sec.
INFO:  "protein_hsps": removed 178956767 row versions in 1899058 pages
DETAIL:  CPU 30.48s/18.15u sec elapsed 160.12 sec.
INFO:  scanned index "protein_hsps_pkey" to remove 178956762 row versions
DETAIL:  CPU 361.19s/2668.89u sec elapsed 4287.59 sec.
INFO:  scanned index "protein_hsps_clustidx_on_origin" to remove
178956762 row versions
DETAIL:  CPU 360.95s/2668.46u sec elapsed 4244.87 sec.
INFO:  "protein_hsps": removed 178956762 row versions in 1891629 pages
DETAIL:  CPU 29.31s/18.06u sec elapsed 136.22 sec.
INFO:  scanned index "protein_hsps_pkey" to remove 178956716 row versions
DETAIL:  CPU 365.00s/2817.88u sec elapsed 4542.31 sec.
INFO:  scanned index "protein_hsps_clustidx_on_origin" to remove
178956716 row versions
DETAIL:  CPU 360.58s/2776.57u sec elapsed 4368.76 sec.
INFO:  "protein_hsps": removed 178956716 row versions in 1877406 pages
DETAIL:  CPU 29.04s/17.29u sec elapsed 119.11 sec.
INFO:  scanned index "protein_hsps_pkey" to remove 178956712 row versions
DETAIL:  CPU 356.50s/2644.50u sec elapsed 4272.66 sec.
INFO:  scanned index "protein_hsps_clustidx_on_origin" to remove
178956712 row versions
DETAIL:  CPU 371.59s/2644.97u sec elapsed 4236.70 sec.
INFO:  "protein_hsps": removed 178956712 row versions in 1917558 pages
DETAIL:  CPU 31.86s/20.42u sec elapsed 161.58 sec.
INFO:  scanned index "protein_hsps_pkey" to remove 178956753 row versions
DETAIL:  CPU 376.24s/2722.70u sec elapsed 4352.62 sec.
INFO:  scanned index "protein_hsps_clustidx_on_origin" to remove
178956753 row versions
DETAIL:  CPU 378.60s/2723.70u sec elapsed 4323.40 sec.
INFO:  "protein_hsps": removed 178956753 row versions in 1922079 pages
DETAIL:  CPU 30.48s/21.27u sec elapsed 136.20 sec.
INFO:  scanned index "protein_hsps_pkey" to remove 178956686 row versions
DETAIL:  CPU 381.81s/2709.34u sec elapsed 4380.95 sec.
INFO:  scanned index "protein_hsps_clustidx_on_origin" to remove
178956686 row versions
DETAIL:  CPU 382.59s/2695.10u sec elapsed 4321.23 sec.
INFO:  "protein_hsps": removed 178956686 row versions in 1924530 pages
DETAIL:  CPU 31.39s/21.19u sec elapsed 135.10 sec.
INFO:  scanned index "protein_hsps_pkey" to remove 178956721 row versions
DETAIL:  CPU 372.96s/2578.62u sec elapsed 4235.85 sec.

(truncated here, process is still running though ...)