We are using version 6.4.2 on RedHat Linux 5.1. We are using the Perl Interface for most applications. We have a search
indexthat we build from many different data sources. It is as follows:
Table = search
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| id | text not null | var |
| group_id | text | var |
| date_created | date | 4 |
| time_created | time | 8 |
| word | text | var |
| count | int4 | 4 |
| sort | text | var |
| type | char() | 1 |
| type_data | text | var |
+----------------------------------+----------------------------------+-------+
Indices: search_pkey
search_type_key
search_word_key
From time to time, we must empty the table and do a full rebuild. We do this as follows:
=> delete from search;
=> vacuum verbose analyze search;
However the vacuum normally hangs. The process state changes to 'Resident' and it has to be manually killed from a
shell.The only way to fix the problem is to drop and re create the search table. The table size is only about 2.5MB.
Belowis the vacuum output. Any suggestions would be greatly appreciated.
NOTICE: --Relation search--
NOTICE: Pages 291: Changed 160, Reapped 132, Empty 0, New 0; Tup 6644: Vac 5532, Crash 0, UnUsed 0, MinLen 52, MaxLen
222;Re-using: Free/Avail. Space 1052572/
1052572; EndEmpty/Avail. Pages 0/132. Elapsed 0/0 sec.
NOTICE: Index search_word_key: Pages 35; Tuples 6644: Deleted 0. Elapsed 0/0 sec.
NOTICE: Index search_type_key: Pages 71; Tuples 6644: Deleted 0. Elapsed 0/0 sec.
Please include me in any replies as I am not currently a member of the mailing lists.
Thanks,
Cullin J Wible