Re: Hash Indexes

On 09/13/2016 07:26 AM, Amit Kapila wrote:
> Attached, new version of patch which contains the fix for problem
> reported on write-ahead-log of hash index thread [1].
>

I have been testing patch in various scenarios, and it has a positive
performance impact in some cases.

This is especially seen in cases where the values of the indexed column
are unique - SELECTs can see a 40-60% benefit over a similar query using
b-tree. UPDATE also sees an improvement.

In cases where the indexed column value isn't unique, it takes a long
time to build the index due to the overflow page creation.

Also in cases where the index column is updated with a high number of
clients, ala

-- ddl.sql --
CREATE TABLE test AS SELECT generate_series(1, 10) AS id, 0 AS val;
CREATE INDEX IF NOT EXISTS idx_id ON test USING hash (id);
CREATE INDEX IF NOT EXISTS idx_val ON test USING hash (val);
ANALYZE;

-- test.sql --
\set id random(1,10)
\set val random(0,10)
BEGIN;
UPDATE test SET val = :val WHERE id = :id;
COMMIT;

w/ 100 clients - it takes longer than the b-tree counterpart (2921 tps
for hash, and 10062 tps for b-tree).

Jeff mentioned upthread the idea of moving the lock to a bucket meta
page instead of having it on the main meta page. Likely a question for
the assigned committer.

Thanks for working on this !

Best regards,
Jesper