Re: Making hash indexes worthwhile

On Mon, Oct 5, 2009 at 6:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jeff Janes <jeff.janes@gmail.com> writes:
>> Do you know why that should be?  I've done some work with gprof, and
>> the results are pretty suspect, because the total gprof time adds up
>> to only about 1/3 of the total time the backend spends on CPU
>> (according to "top"), and I don't know where the unaccounted for time
>> is going.
>
> Are you sure that gprof is delivering trustworthy numbers at all?
> I've seen cases where it was consistently mis-timing things.
> https://bugzilla.redhat.com/show_bug.cgi?id=151763
> Admittedly that was an old Linux version, but ...

That bug goes in the other direction versus what I am seeing,
reporting more time than the clock on the wall would allow.  I think
things are more or less good, because profiling simple programs gives
the expected answers, where under that bug they wouldn't.  I think
there are some kinds of system calls which are accounted for as
user-space by "top", but which are not interruptable and so don't get
timed by gprof.  But are such events evenly distributed throughout the
code or concentrated in certain places?  I'll need to experiment with
it a bit more.

Any case, I hacked the hash index code to not take heavy locks on
meta-block or on bucket-blocks (lw bufffer content locks are still
taken) and the performance in a nested loop (full table scan outer,
hash-index inner scan) went up by over 50%, with only one backend
active.  And the heavy-weight locking code dropped out of the top
spots in gprof.  So I think I may be on to something.   I want to run
it for many more hours to make sure I'm getting good statistics.

Then it is a question of whether my other ideas for making it safe to
remove the heavy weight locks on a non-read-only system can be
implemented.

Jeff