Re: [HACKERS] GSoC on WAL-logging hash indexes

On 2014-04-30 11:10:22 -0700, Jeff Janes wrote:
> I've seen the simple pinning and unpinning of the root page (or the fast root,
> whatever the first page we bother to pin on a regular basis is called) be a
> point of contention.  When one index dominates the entire system workload, that
> one page also drives contention on the spin lock that protects the lwlock that
> share-protects whichever buffer mapping partition happens to contain it.

To quite some degree that's an implementation deficiency of our lwlocks
though. I've seen *massive* improvements with my lwlock patch for that
problem. Additionally we need to get rid of the spinlock around
pin/unpin.
That said, even after those optimizations, there remains a significant
amount of cacheline bouncing. That's much easier to avoid for something
like hash indexes than btrees.

I think another advantage is that hash indexes can be *much* smaller
than btree when the individual rows are wide. I wonder though if we
couldn't solve that better by introducing "transforms" around the looked
up data. E.g. allow to *transparently* use a hash(indexed_column) to be
used. If you currently do that a lot of work has to be done in every
query...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services