> pgsql@mohawksoft.com writes:
>> Anyway, IMHO, hash indexes would be dramatically improved if you could
>> specify your own hashing function
>
> That's called a custom operator class.
Would I also be able to query the bucket size and all that?
>
>> and declare initial table size.
>
> It would be interesting to see if setting up the hashtable with about
> the right number of buckets initially would make CREATE INDEX enough
> faster to be a win ... but that doesn't mean I want to make the user
> deal with it. We could probably hack hashbuild() to estimate the
> size of the parent table using the same code that the planner is now
> using (ie, actual size in pages times a possibly-dead-reckoning rows
> per page estimate).
>
I know a linear hash is different than a classic simple hash table, but a
classic simple hash table has some great advantages at the expense of disk
space. IMHO being able to use the hash index in a way that is more of the
classic theoretical hash table and use the linear behavor if the table
grows beyond initial estimates I think would be a big win. It could
actually get to a 1:1 operation data retrieval on properly estimated
tables.
Estimations are a great idea, something like first prime after 2*NROWS
(with a GUC limit, I guess) would probably make hash indexes the fastest
most disk hogging index around.