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.