Profiling insertion of UUIDs with btree and hash indices

uuid-insert-profile.md

Is this a fair benchmark?

• I'm comparing the speed of inserting UUIDs into columns which are indexed with btree vs hash
• Want to ensure that I'm measuring both insert queries at their best performance
• Profiled each of them to check whether there is interference by server misconfiguration

Flame graph results

Both were sampled with perf record -F 99 -u postgres -g --call-graph dwarf -- sleep 180

Inserting with btree index

insert into with_btree (select gen_random_uuid() from generate_series(1, 1024*1024*1024/64));

Inserting with hash index

insert into with_hash (select gen_random_uuid() from generate_series(1, 1024*1024*1024/64));

Machine stats

• ami-aa2ea6d0
  • Ubuntu Server 16.04 LTS (HVM),EBS General Purpose (SSD) Volume Type
  • c3.xlarge
  • vCPU: 4
  • RAM GiB: 7.5
  • Disk GB: 2 x 40 (SSD)

PostgreSQL configuration

• Built from source https://ftp.postgresql.org/pub/source/v10.1/postgresql-10.1.tar.gz
• Using CFLAGS="-O3 -g3 -ggdb3 -gdwarf-4 -fno-omit-frame-pointer"
• Stock configuration except:
  • max_wal_size='10GB';
  • checkpoint_timeout='2h';
  • synchronous_commit='off';

Schema

create table with_btree ( u uuid default gen_random_uuid() );
create table with_hash  ( u uuid default gen_random_uuid() );
create index on with_btree using btree (u);
create index on with_hash  using hash  (u);