On Wed, Feb 04, 2009 at 11:22:44PM +0100, Zdenek Kotala wrote: > The main speed improvement is for varchar datatype. I think It should be > mention here as well. IIRC, times are similar with B-Tree for integer > datatype. > > Zdenek > > Kenneth Marshall p????e v st 04. 02. 2009 v 13:57 -0600: > > I had submitted the documentation change as part of my > > hash function patch but it was removed as not relevant. > > (It wasn't really.) I would basically remove the first > > sentence: > > > > Note: Hash index operations are not presently WAL-logged, > > so hash indexes might need to be rebuilt with REINDEX after a > > database crash. For this reason, hash index use is presently > > discouraged. > > > > Ken > > > > > > On Wed, Feb 04, 2009 at 01:22:23PM -0300, Alvaro Herrera wrote: > > > Hi, > > > > > > indices.sgml contains this paragraph about hash indexes: > > > > > > Note: Testing has shown PostgreSQL's hash indexes to perform no > > > better than B-tree indexes, and the index size and build time for hash > > > indexes is much worse. Furthermore, hash index operations are not > > > presently WAL-logged, so hash indexes might need to be rebuilt with > > > REINDEX after a database crash. For these reasons, hash index use is > > > presently discouraged. > > > > > > > > > However, it seems to me that hash indexes are much improved in 8.4, so > > > maybe this needs to be reworded. I'm not sure to what point they have > > > been improved though. > > > > > > -- > > > Alvaro Herrera > > > http://www.CommandPrompt.com/ > > > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > >
The speed improvement applies particularly to any datatype that is larger than an integer (typically 4 bytes). Also the size of fields that can be indexed efficiently is much, much larger than the 2K for Btree. And even 32-bit quantities may be indexed more efficiently than Btrees for large indexes due to the O(1) probe behavior. Btrees typically need to cache/probe the upper levels of the tree to locate the tuple. I have held off on extensive benchmarking until WAL has been implemented. Regards, Ken -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
