Re: Hash index not being updated

Hi.

Right direction is to use btree index.

Hash indexes are sensitive to power failures.

2011/10/5, Justin Naifeh <jnaifeh(at)terraframe(dot)com>:
> 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
>
>

--
------------
pasman