Hello,
I'm facing the following problem.
I have a postgres 8.0 DB with a table 'results' containing 6.000.000
records.
This table has 16 indexes. Each one basically created to speed up
different queries.
Because of some glitch in the system there has never been a VACUUM FULL
on this table.
When I try to do a full vacuum (on a dual-processor, 2GB RAM, ...) it
takes forever. I started the
vacuum at 6pm and 15 hours later it was still going on.
Just before starting vacuum full, I did a vacuum analyze (which took
about 15 minutes). I also
checked the amount of diskspace used for the indexes => 33% of 11
available GigaBytes.
After killing the vacuum full my diskspace for the indexes has increased
to 41% of the 11 available GB.
So does anyone know what I can do to fully vacuum my table? Or to at
least decrease the amount
of diskspace used?
cheers, pj
=================================================================
here is the output of the analyze :
/usr/local/pgsql/bin/vacuumdb --dbname Core --analyze --user postgres
--verbose --table 'results'
LOG: statement: VACUUM VERBOSE ANALYZE results;
INFO: vacuuming "public.results"
INFO: index "PRIM_RESULTS_id" now contains 5647429 row versions in
26572 pages
DETAIL: 1992717 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.80s/5.76u sec elapsed 54.99 sec.
INFO: index "CTR_U_RESULTS" now contains 5647429 row versions in 66516
pages
DETAIL: 1992717 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 4.11s/6.78u sec elapsed 134.15 sec.
INFO: index "IDX_RESULTS_accession_nr" now contains 5647429 row
versions in 40281 pages
DETAIL: 1992716 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.53s/5.85u sec elapsed 95.72 sec.
INFO: index "IDX_RESULTS_blinding_flag" now contains 5647429 row
versions in 33408 pages
DETAIL: 1992716 index row versions were removed.
17242 index pages have been deleted, 10350 are currently reusable.
CPU 1.62s/3.94u sec elapsed 56.00 sec.
INFO: index "IDX_RESULTS_inv_code" now contains 5647429 row versions in
44918 pages
DETAIL: 1992716 index row versions were removed.
18738 index pages have been deleted, 11307 are currently reusable.
CPU 2.28s/4.44u sec elapsed 61.94 sec.
INFO: index "IDX_RESULTS_lab_pat_nr" now contains 5647429 row versions
in 41821 pages
DETAIL: 1992716 index row versions were removed.
3102 index pages have been deleted, 1524 are currently reusable.
CPU 2.21s/5.40u sec elapsed 93.97 sec.
INFO: index "IDX_RESULTS_study_code" now contains 5647429 row versions
in 33395 pages
DETAIL: 1992716 index row versions were removed.
17087 index pages have been deleted, 10251 are currently reusable.
CPU 1.61s/3.81u sec elapsed 56.14 sec.
INFO: index "IDX_RESULTS_study_code_accession_nr" now contains 5647429
row versions in 49515 pages
DETAIL: 1992716 index row versions were removed.
5 index pages have been deleted, 0 are currently reusable.
CPU 3.03s/5.96u sec elapsed 105.38 sec.
INFO: index "IDX_RESULTS_study_code_inv_code_site_code" now contains
5647429 row versions in 69415 pages
DETAIL: 1992716 index row versions were removed.
29584 index pages have been deleted, 17798 are currently reusable.
CPU 3.48s/5.36u sec elapsed 101.96 sec.
INFO: index "IDX_RESULTS_study_code_test_code_visit_name" now contains
5647429 row versions in 65217 pages
DETAIL: 1992716 index row versions were removed.
14998 index pages have been deleted, 8690 are currently reusable.
CPU 3.33s/6.09u sec elapsed 118.88 sec.
INFO: index "IDX_RESULTS_study_id" now contains 5647429 row versions in
33354 pages
DETAIL: 1992716 index row versions were removed.
17059 index pages have been deleted, 10217 are currently reusable.
CPU 1.65s/3.96u sec elapsed 55.23 sec.
INFO: index "IDX_RESULTS_study_id_test_code_blinding_flag" now contains
5647429 row versions in 57022 pages
DETAIL: 1992716 index row versions were removed.
22134 index pages have been deleted, 13258 are currently reusable.
CPU 3.15s/4.84u sec elapsed 92.27 sec.
INFO: index "IDX_RESULTS_catflag_one_test_code" now contains 5647429
row versions in 56836 pages
DETAIL: 1992716 index row versions were removed.
22034 index pages have been deleted, 13249 are currently reusable.
CPU 3.39s/5.36u sec elapsed 94.52 sec.
INFO: index "IDX_RESULTS_catflag_two_test_code" now contains 5647429
row versions in 56714 pages
DETAIL: 1992716 index row versions were removed.
21931 index pages have been deleted, 13120 are currently reusable.
CPU 2.92s/5.40u sec elapsed 102.08 sec.
INFO: index "IDX_RESULTS_catflag_three_test_code" now contains 5647429
row versions in 56704 pages
DETAIL: 1992716 index row versions were removed.
21917 index pages have been deleted, 13198 are currently reusable.
CPU 3.02s/5.15u sec elapsed 97.27 sec.
INFO: index "IDX_RESULTS_catflag_four_test_code" now contains 5647429
row versions in 56667 pages
DETAIL: 1992716 index row versions were removed.
21918 index pages have been deleted, 13144 are currently reusable.
CPU 3.09s/5.47u sec elapsed 85.58 sec.
INFO: index "IDX_RESULTS_catflag_five_test_code" now contains 5647429
row versions in 56722 pages
DETAIL: 1992716 index row versions were removed.
21940 index pages have been deleted, 13110 are currently reusable.
CPU 3.34s/5.52u sec elapsed 82.15 sec.
INFO: index "IDX_RESULTS_study_code_specimen_name" now contains 5647429
row versions in 52147 pages
DETAIL: 1992716 index row versions were removed.
22432 index pages have been deleted, 10901 are currently reusable.
CPU 2.60s/4.85u sec elapsed 77.11 sec.
INFO: "results": removed 1992717 row versions in 183179 pages
DETAIL: CPU 9.66s/9.53u sec elapsed 129.92 sec.
INFO: "results": found 1992717 removable, 5647429 nonremovable row
versions in 628928 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 2791954 unused item pointers.
0 pages are entirely empty.
CPU 75.02s/106.32u sec elapsed 1804.40 sec.
INFO: vacuuming "pg_toast.pg_toast_567723"
INFO: index "pg_toast_567723_index" now contains 0 row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_567723": found 0 removable, 0 nonremovable row versions
in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.results"
INFO: "results": scanned 3000 of 628928 pages, containing 27323 live
rows and 0 dead rows; 3000 rows in sample, 5728067 estimated total rows
VACUUM
