Re: Write Ahead Logging for Hash Indexes

On 09/09/16 14:50, Mark Kirkwood wrote:
>
> Yeah, good suggestion about replacing (essentially) all the indexes 
> with hash ones and testing. I did some short runs with this type of 
> schema yesterday (actually to get a feel for if hash performance vs 
> btree was compareable - does seem tp be) - but probably longer ones 
> with higher concurrency (as high as I can manage on a single socket i7 
> anyway) is a good plan. If Ashutosh has access to seriously large 
> numbers of cores then that is even better :-)
>
>

I managed to find a slightly bigger server (used our openstack cloud to 
run a 16 cpu vm). With the schema modified as follows:

bench=# \d pgbench_accounts   Table "public.pgbench_accounts"  Column  |     Type      | Modifiers
----------+---------------+----------- aid      | integer       | not null bid      | integer       | abalance |
integer      | filler   | character(84) |
 
Indexes:    "pgbench_accounts_pkey" hash (aid)

bench=# \d pgbench_branches   Table "public.pgbench_branches"  Column  |     Type      | Modifiers
----------+---------------+----------- bid      | integer       | not null bbalance | integer       | filler   |
character(88)|
 
Indexes:    "pgbench_branches_pkey" hash (bid)

bench=# \d pgbench_tellers    Table "public.pgbench_tellers"  Column  |     Type      | Modifiers
----------+---------------+----------- tid      | integer       | not null bid      | integer       | tbalance |
integer      | filler   | character(84) |
 
Indexes:    "pgbench_tellers_pkey" hash (tid)

bench=# \d pgbench_history          Table "public.pgbench_history" Column |            Type             | Modifiers
--------+-----------------------------+----------- tid    | integer                     | bid    | integer
      | aid    | integer                     | delta  | integer                     | mtime  | timestamp without time
zone| filler | character(22)               |
 
Indexes:    "pgbench_history_pkey" hash (bid)

performed several 10 hour runs on size 100 database using 32 and 64 
clients. For the last run I rebuilt with assertions enabled. No hangs or 
assertion failures.

regards

Mark