Re: Hash Indexes

On 09/15/2016 02:03 AM, Amit Kapila wrote:
>> Same thing here - where the fields involving the hash index aren't updated.
>>
>
> Do you mean that for such cases also you see 40-60% gain?
>

No, UPDATEs are around 10-20% for our cases.

>>
>> I have done a run to look at the concurrency / TPS aspect of the
>> implementation - to try something different than Mark's work on testing the
>> pgbench setup.
>>
>> With definitions as above, with SELECT as
>>
>> -- select.sql --
>> \set id random(1,10)
>> BEGIN;
>> SELECT * FROM test WHERE id = :id;
>> COMMIT;
>>
>> and UPDATE/Indexed with an index on 'val', and finally UPDATE/Nonindexed w/o
>> one.
>>
>> [1] [2] [3] is new_hash - old_hash is the existing hash implementation on
>> master. btree is master too.
>>
>> Machine is a 28C/56T with 256Gb RAM with 2 x RAID10 SSD for data + wal.
>> Clients ran with -M prepared.
>>
>> [1]
>> https://www.postgresql.org/message-id/CAA4eK1+ERbP+7mdKkAhJZWQ_dTdkocbpt7LSWFwCQvUHBXzkmA@mail.gmail.com
>> [2]
>> https://www.postgresql.org/message-id/CAD__OujvYghFX_XVkgRcJH4VcEbfJNSxySd9x=1Wp5VyLvkf8Q@mail.gmail.com
>> [3]
>> https://www.postgresql.org/message-id/CAA4eK1JUYr_aB7BxFnSg5+JQhiwgkLKgAcFK9bfD4MLfFK6Oqw@mail.gmail.com
>>
>> Don't know if you find this useful due to the small number of rows, but let
>> me know if there are other tests I can run, f.ex. bump the number of rows.
>>
>
> It might be useful to test with higher number of rows because with so
> less data contention is not visible,

Attached is a run with 1000 rows.

> but I think in general with your,
> jeff's and mine own tests it is clear that there is significant win
> for read-only cases and for read-write cases where index column is not
> updated.  Also, we don't find any regression as compare to HEAD which
> is sufficient to prove the worth of patch.

Very much agreed.

> I think we should not
> forget that one of the other main reason for this patch is to allow
> WAL logging for hash indexes.

Absolutely. There are scenarios that will have a benefit of switching to
a hash index.

> I think for now, we have done
> sufficient tests for this patch to ensure it's benefit, now if any
> committer wants to see something more we can surely do it.

Ok.

>  I think
> the important thing at this stage is to find out what more (if
> anything) is left to make this patch as "ready for committer".
>

I think for CHI is would be Robert's and others feedback. For WAL, there
is [1].

[1]
https://www.postgresql.org/message-id/5f8b4681-1229-92f4-4315-57d780d9c128%40redhat.com

Best regards,
  Jesper