Re: What is wrong with hashed index usage? - Mailing list pgsql-hackers

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I hate to do that because it makes people think something special is
> > happening for hash, but it isn't.  We could throw an elog(NOTICE)
> > stating that hash is not recommended and btree is faster, or something
> > like that.
>
> I think the only action called for is some improvement in the
> documentation.  Right now the docs are not honest about the state
> of any of the non-btree index methods.  Ain't none of 'em ready
> for prime time IMHO.  GIST is the only one that's getting any
> development attention --- and probably the only one that deserves
> it, given limited resources.  Hash offers no compelling advantage
> over btree AFAICS, and rtree is likewise dominated by GIST (or would
> be, if we shipped rtree-equivalent GIST opclasses in the standard
> distribution).
>
> I do not like "throw an elog" as a substitute for documentation.

OK, documentation changes for hash attached.  Do we need to also throw
a elog(WARNING) about its use?  I don't think everyone is going to see
these documentation changes, and I hate to add it to the FAQ.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: doc/src/sgml/indices.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/indices.sgml,v
retrieving revision 1.31
diff -c -r1.31 indices.sgml
*** doc/src/sgml/indices.sgml    7 Jan 2002 02:29:12 -0000    1.31
--- doc/src/sgml/indices.sgml    21 Jun 2002 03:13:47 -0000
***************
*** 181,192 ****
  </synopsis>
     <note>
      <para>
!      Because of the limited utility of hash indexes, a B-tree index
!      should generally be preferred over a hash index.  We do not have
!      sufficient evidence that hash indexes are actually faster than
!      B-trees even for <literal>=</literal> comparisons.  Moreover,
!      hash indexes require coarser locks; see <xref
!      linkend="locking-indexes">.
      </para>
     </note>
    </para>
--- 181,189 ----
  </synopsis>
     <note>
      <para>
!      Testing has shown that hash indexes are slower than btree indexes,
!      and the size and build time for hash indexes is much worse. For
!      these reasons, hash index use is discouraged.
      </para>
     </note>
    </para>
Index: doc/src/sgml/xindex.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/xindex.sgml,v
retrieving revision 1.25
diff -c -r1.25 xindex.sgml
*** doc/src/sgml/xindex.sgml    29 May 2002 17:36:40 -0000    1.25
--- doc/src/sgml/xindex.sgml    21 Jun 2002 03:13:48 -0000
***************
*** 11,19 ****

    <para>
     The procedures described thus far let you define new types, new
!    functions, and new operators.  However, we cannot yet define a secondary
!    index (such as a B-tree, R-tree, or
!    hash access method) over a new type or its operators.
    </para>

    <para>
--- 11,19 ----

    <para>
     The procedures described thus far let you define new types, new
!    functions, and new operators. However, we cannot yet define a
!    secondary index (such as a B-tree, R-tree, or hash access method)
!    over a new type or its operators.
    </para>

    <para>
Index: doc/src/sgml/ref/create_index.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/create_index.sgml,v
retrieving revision 1.31
diff -c -r1.31 create_index.sgml
*** doc/src/sgml/ref/create_index.sgml    18 May 2002 15:44:47 -0000    1.31
--- doc/src/sgml/ref/create_index.sgml    21 Jun 2002 03:13:48 -0000
***************
*** 329,334 ****
--- 329,339 ----
      an indexed attribute is involved in a comparison using
      the <literal>=</literal> operator.
     </para>
+    <para>
+      Testing has shown that hash indexes are slower than btree indexes,
+      and the size and build time for hash indexes is much worse. For
+      these reasons, hash index use is discouraged.
+    </para>

     <para>
      Currently, only the B-tree and gist access methods support multicolumn