pgbench select uuid for custom scripts-CodePudding

I am trying to benchmark a custom dataset with pgbench. all the records I want to select for have uuids as primary keys. Unfortunately all the samples snipets select random records by using random() function - presumably for sequential PKs.

\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)

BEGIN;
UPDATE pgbench_accounts SET abalance = abalance   :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance   :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance   :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;

I wonder if there is a way to select random uuids from the tables into a variable, that's not accounted for in the latency.

CodePudding user response：

You can use gen_random_uuid():

SELECT gen_random_uuid();

Since version 13 that is integrated into core PostgreSQL, for older version you have to install the extension pgcrypto first:

CREATE EXTENSION pgcrypto;

CodePudding user response：

You can just order by random(). Suppose you wanted to pick a sample of 5 rows form a table; lets call it ruuids. Then just (see demo);

select * 
  from ruuids 
 order by random()
 limit 5;