Re: Hash Indexes

On Thu, Sep 15, 2016 at 12:43 AM, Jesper Pedersen
<jesper(dot)pedersen(at)redhat(dot)com> wrote:
> Hi,
>
> On 09/14/2016 07:24 AM, Amit Kapila wrote:

>
>>> UPDATE also sees an improvement.
>>>
>>
>> Can you explain this more? Is it more compare to HEAD or more as
>> compare to Btree? Isn't this contradictory to what the test in below
>> mail shows?
>>
>
> 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?

>
> 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, 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. I think we should not
forget that one of the other main reason for this patch is to allow
WAL logging for hash indexes. 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. 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".

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com