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

David Madore <david(dot)madore(at)ens(dot)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