pgbench examples

pgbench.md

Insert data

insert.sql

\set id random(1, 100000 * :scale)
\set uuid gen_random_uuid()


insert into my_table (id, uuid)
values
(:id, :uuid);

pgbench -h localhost -p 6006 -U $user -d $database -f insert.sql -t 5000 -c 10

Two selects

query-1.sql

\set id random(1, 100000 * :scale)
select * from my_table where id = :id

query-2.sql

\set id_a random(1, 100000 * :scale)
\set id_b random(1, 100000 * :scale)
\set id_c random(1, 100000 * :scale)
select * from my_table where other_field in (:id_a, :id_b, :id_c) 

pgbench -h localhost -p 6006 -U $user -d $database -t 5000 -c 10 -f query-1.sql@1 -f query-2.sql@1

Select using random values from an array

\set offset_1 random(1, 99)
\set offset_2 random(1, 99)
\set offset_3 random(1, 99)

with all_ids as (
 select unnest(array[
   86863,
   93304,
   11034
   ]) as id
),
selected_ids as (
  (select id from all_ids limit 1 offset :offset_1)
  union all
  (select id from all_ids limit 1 offset :offset_2)
  union all
  (select id from all_ids limit 1 offset :offset_3)
)
select * from my_table where id in (select id from selected_ids);

pgbench -h localhost -p 6006 -U $user -d $database --random-seed=0 -t 10 -c 3 -j 3 -f query.sql