Re: Hash index todo list item

Shreya Bhargava <shreya_bhargav(at)yahoo(dot)com> writes:
> We performed some probe tests on our patch on
> hash index and the original btree index to compare the
> performance between the two.

Interesting, but ...

> From the results obtained, the average of all the hash probes is 141.8ms, the average for btree is 168.5, a difference of about 27.The standard deviations are about 23, so this is a statistically significant difference.

> Our prediction that the hash index would take on the average one
> probe for about 10ms and the btree would take three probes for about 30 ms
> or a difference of about 20ms was pretty well shown by the difference we
> got of about 27.

... unfortunately, a zero-cache situation isn't very reflective of the
real world. In reality, for an index that is getting used often enough
to impact application performance at all, the root page will stay in
cache and likely so will some of the upper tree levels. So I don't
think this experiment proves anything at all about real-world
performance.

What I'd like to see is an aggregate time measurement for millions of
random probes into an index that's noticeably larger than memory.
It would also be interesting to measure the speed of the fully-cached
case (same test, but index smaller than memory).

regards, tom lane