Profiling PostgreSQL

I did a little bit of work Tuesday night and Wednesday profiling PostgreSQL.  I ran two different tests.  The first test was designed just to measure the overhead of repeatedly connecting to the database without doing anything, while the second test looked running pgbench with 36 concurrent threads.  The best thing that can happen to you when you fire up the profiler is to have something pop up the profile that you never would have expected.  At least in my experience, when you see what you expect to see, that typically means it's something you've already thought about optimizing, and is therefore probably reasonably efficient already.  When you see something totally unexpected, it's probably something you've never thought about optimizing, and of course the first optimization is always the easiest.

Anyhow, that's what happened to me with the "repeated connections" test.  It turns out that a big chunk of the CPU time was actually being spent during backend exit, rather than (as I had anticipated) backend startup.  We had a check in there to forcibly release any buffer pins that hadn't been cleaned up properly during normal execution.  Originally, this was probably a necessary and valuable check, but we've subsequently added much more thorough and robust cleanup mechanisms which should mean that this code never finds anything to release.  If it does find something, gracefully cleaning up the pin is the wrong idea: we want the code to yell and scream, so that we find and fix the underlying bug.

So, after some discussion with Tom Lane, I ripped this code out and replaced it with some code that will run only in assert-enabled builds (which are typically used only for development and debugging) that will check for leftover buffer pins and fail an assertion if any are found, which will hopefully make it easier to find any current or future bugs in this area.  In non-assert-enabled builds, we no longer do anything at all here (the best kind of optimization!).

Unfortunately, this was the only really surprising thing that popped up in the profiling results.  Further improvements are going to take a bit more work.

Happy Thanksgiving!