Hash index not being updated

In Postgres 8.4, I have a table called java_types with two columns,
package_name and class_name. There is another table called java_objects
that defines a column called type whose value matches the concatenation
of package_name and class_name. A typical join and result looks like this:

SELECT package_name, class_name, type FROM java_objects o INNER JOIN
java_types t ON (t.package_name || '.' || t.class_name) = o.type;

package_name, class_name, type
"java.lang" , "String" , "java.lang.String"

The above works, although it is slow for large data sets so I defined
the following index:

CREATE INDEX java_type_hash
ON java_types
USING hash
(((package_name::text || '.'::text) || class_name::text));

I confirmed that my new index is being used by inspecting the query plan
and finding the following:

-> Index Scan using java_type_hash on java_types (cost=0.00..1.22
rows=1 width=49)
Index Cond: ((((java_types.package_name)::text || '.'::text) ||
(java_types.class_name)::text) = (java_objects.type)::text)

This gave me a speed boost and worked initially. A day after defining
the index, however, I inserted a few rows into java_types and then many
rows into java_objects. When I ran the aforementioned query I got 0
results. A month later (without restarting postgres), I ran the same
query and still got 0 results. I suspected the index had either been
corrupted or not updated automatically. I confirmed this by running the
following:

REINDEX INDEX java_type_hash;

And then when I re-ran the same query the correct results came back! I
thought that maybe my system wasn't set to autovacuum but a query for my
current settings confirmed that it was:

autovacuum = on
autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 256
autovacuum_freeze_max_age = 200000000
autovacuum_max_workers = 3
autovacuum_naptime = 60
autovacuum_vacuum_cost_delay = 20
autovacuum_vacuum_cost_limit = -1
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_threshold = 512
track_counts = on

Finally, this query gave me some extra information about when my system
had last been vacuumed/analyzed:

SELECT relname, last_vacuum, last_autovacuum, last_analyze,
last_autoanalyze FROM pg_stat_all_tables WHERE relname = 'java_types'

rename , last_vacuum , last_autovacuum ,
last_analyze , last_autoanalyze
"java_types", "2011-10-04 13:37:03.867-07", "" ,
"2011-10-04 13:21:22.342-07", "2011-08-30 10:15:13.018-07"

The index was created on 8/30/2011, inserts were done on java_types and
java_objects on 8/31/2011, and then 10/4/2011 is when I manually ran
REINDEX (and later VACUUM ANALYZE).

So after all that, the questions is: If I have autovacuum set to true
then I shouldn't have to worry about calling VACUUM/ANALYZE/REINDEX
manually to update my java_types_hash index, right? Maybe my settings
are wrong or I'm making an incorrect assumption about Postgres behavior.

Any pointer in the right direction helps. Thanks in advance.

--

Justin Naifeh Software Developer

Voice:
303-460-7111 x1 Toll Free:
877-444-3074 x1 Cell:
720-363-8874 AIM, Yahoo
justinnaifeh <aim:goim?screenname=justinnaifeh>

www.terraframe.com/products/runwaysdk
<http://www.terraframe.com/products/runwaysdk>
Makers of TerraFrame RUNWAY SDK^(TM), the next-generation
model-driven engineering (MDE) application toolkit for software developers