Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory) - Mailing list pgsql-performance

Update to my case:
I drop and recreate the index and there was no problem this time.
Strange...

# DROP INDEX pkpoai.test_metadata_all;
DROP INDEX
# VACUUM FULL VERBOSE ANALYZE pkpoai.metadata;
INFO:  vacuuming "pkpoai.metadata"
INFO:  "metadata": found 167381 removable, 3133397 nonremovable row
versions in 344179 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 168 to 2032 bytes long.
There were 13392 unused item pointers.
Total free space (including removable row versions) is 174825268 bytes.
9362 pages are or will become empty, including 0 at the end of the
table.
150433 pages containing 166581084 free bytes are potential move
destinations.
CPU 7.07s/1.50u sec elapsed 209.46 sec.
INFO:  index "metadata_pkey" now contains 3133397 row versions in 10501
pages
DETAIL:  88246 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.68s/1.21u sec elapsed 81.89 sec.
INFO:  index "metadata_archive_key" now contains 3133397 row versions
in 45268 pages
DETAIL:  88246 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.28s/1.66u sec elapsed 364.19 sec.
INFO:  index "metadata_oai_identifier" now contains 3133397 row
versions in 36336 pages
DETAIL:  88246 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.85s/1.81u sec elapsed 260.82 sec.
INFO:  "metadata": moved 188118 row versions, truncated 344179 to
327345 pages
DETAIL:  CPU 9.21s/108.65u sec elapsed 1890.56 sec.
INFO:  index "metadata_pkey" now contains 3133397 row versions in 10633
pages
DETAIL:  188118 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.64s/0.60u sec elapsed 52.24 sec.
INFO:  index "metadata_archive_key" now contains 3133397 row versions
in 45597 pages
DETAIL:  188118 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.40s/1.12u sec elapsed 359.17 sec.
INFO:  index "metadata_oai_identifier" now contains 3133397 row
versions in 36624 pages
DETAIL:  188118 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.82s/0.97u sec elapsed 277.56 sec.
INFO:  vacuuming "pg_toast.pg_toast_27007136"
INFO:  "pg_toast_27007136": found 1894 removable, 134515 nonremovable
row versions in 25921 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 37 to 2034 bytes long.
There were 460 unused item pointers.
Total free space (including removable row versions) is 17460524 bytes.
217 pages are or will become empty, including 0 at the end of the table.
22612 pages containing 17416360 free bytes are potential move
destinations.
CPU 0.51s/0.10u sec elapsed 16.05 sec.
INFO:  index "pg_toast_27007136_index" now contains 134515 row versions
in 561 pages
DETAIL:  1894 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 1.22 sec.
INFO:  "pg_toast_27007136": moved 1806 row versions, truncated 25921 to
25554 pages
DETAIL:  CPU 0.03s/0.21u sec elapsed 9.83 sec.
INFO:  index "pg_toast_27007136_index" now contains 134515 row versions
in 569 pages
DETAIL:  1806 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  analyzing "pkpoai.metadata"
INFO:  "metadata": 327345 pages, 90000 rows sampled, 3620548 estimated
total rows
VACUUM
# CREATE INDEX test_metadata_all ON pkpoai.metadata USING gist
(to_tsvector('default_english', coalesce(author,'') ||' '||
coalesce(affiliation,'') ||' '|| coalesce(add_authors,'') ||' '||
coalesce(add_affiliations,'') ||' '|| coalesce(title,'') ||' '||
coalesce(abstract,'') ||' '|| coalesce(discipline,'') ||' '||
coalesce(topic,'') ||' '|| coalesce(publisher,'') ||' '||
coalesce(contributors,'') ||' '|| coalesce(approach,'') ||' '||
coalesce(format,'') ||' '|| coalesce(source,'') ||' '||
coalesce(language,'') ||' '|| coalesce(relation,'') ||' '||
coalesce(coverage,'') ));
NOTICE:  word is too long
NOTICE:  word is too long
NOTICE:  word is too long
CREATE INDEX
# VACUUM FULL VERBOSE ANALYZE pkpoai.metadata;INFO:  vacuuming
"pkpoai.metadata"INFO:  "metadata": found 0 removable, 3133397
nonremovable row versions in 327345 pagesDETAIL:  0 dead row versions
cannot be removed yet.Nonremovable row versions range from 168 to 2032
bytes long.There were 29889 unused item pointers.Total free space
(including removable row versions) is 37861356 bytes.
0 pages are or will become empty, including 0 at the end of the table.
93935 pages containing 28461956 free bytes are potential move
destinations.
CPU 5.81s/1.09u sec elapsed 56.18 sec.
INFO:  index "metadata_pkey" now contains 3133397 row versions in 10633
pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.53s/0.94u sec elapsed 20.25 sec.
INFO:  index "metadata_archive_key" now contains 3133397 row versions
in 45597 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.46s/1.35u sec elapsed 338.74 sec.
INFO:  index "metadata_oai_identifier" now contains 3133397 row
versions in 36624 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.78s/1.33u sec elapsed 237.07 sec.
INFO:  index "test_metadata_all" now contains 3133397 row versions in
93136 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.65s/3.47u sec elapsed 167.03 sec.
INFO:  "metadata": moved 0 row versions, truncated 327345 to 327345
pages
DETAIL:  CPU 0.35s/0.41u sec elapsed 82.11 sec.
INFO:  vacuuming "pg_toast.pg_toast_27007136"
INFO:  "pg_toast_27007136": found 0 removable, 134515 nonremovable row
versions in 25554 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 37 to 2034 bytes long.
There were 665 unused item pointers.
Total free space (including removable row versions) is 14468156 bytes.
0 pages are or will become empty, including 0 at the end of the table.
22041 pages containing 14421368 free bytes are potential move
destinations.
CPU 0.52s/0.03u sec elapsed 16.14 sec.
INFO:  index "pg_toast_27007136_index" now contains 134515 row versions
in 569 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.04u sec elapsed 0.54 sec.
INFO:  "pg_toast_27007136": moved 0 row versions, truncated 25554 to
25554 pages
DETAIL:  CPU 0.00s/0.03u sec elapsed 2.56 sec.
INFO:  analyzing "pkpoai.metadata"
INFO:  "metadata": 327345 pages, 90000 rows sampled, 3620548 estimated
total rows
VACUUM

Cordialement,
Jean-Gérard Pailloncy