Re: HASH index method not correctly handling NULL text values?

David Madore <david.madore@ens.fr> writes:
> table has 345442 rows, of which 344339 have a non NULL value in the
> column in question.  The index creation proceeded without trouble, but
> a subsequent "VACUUM VERBOSE ANALYZE" command gave the following
> warning: "NUMBER OF INDEX' TUPLES (344339) IS NOT THE SAME AS HEAP'
> (345442)" with the advice to recreate the index.  Essentially, my
> question is: is the warning incorrect or is the index so?

The warning should be suppressed for hash indexes, since they don't
include nulls.  I believe this is fixed in 7.2.

> Incidentally, I might ask, which is the best choice of indexing method
> considering that the only comparison I will ever make on this column
> is equality (this is what led me to prefer HASH initially) and
> considering that the column takes of the order of magnitude of a few
> hundred distinct values (as opposed to the third-of-a-million rows of
> the table)?  The BTREE index creation was considerably faster than the
> HASH index creation.

Our BTREE implementation is a lot better than our HASH implementation;
I have a difficult time recommending the latter for anything.

            regards, tom lane