gen_random_uuid-vs-uuid_generate_v4.sql

-- You must run the benchmark schema localed in 
-- https://gist.github.com/erichosick/eeadc41e33ea2a91bd136b9433e73dc7
-- before running the following benchmark

DO $$
DECLARE d_exec_count int = 20;
DECLARE d_left_sql text;
DECLARE d_right_sql text;
DECLARE d_up_sql text;
DECLARE d_down_sql text;
BEGIN
  d_up_sql = '
    CREATE TABLE IF NOT EXISTS public.gen_random_uuid (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      info bigint
    );

    CREATE TABLE IF NOT EXISTS public.uuid_generate_v4 (
      id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
      info bigint
    );
  ';

  d_down_sql = '
    DROP TABLE IF EXISTS public.gen_random_uuid;
    DROP TABLE IF EXISTS public.uuid_generate_v4;
  ';

  d_left_sql = '
    INSERT INTO public.gen_random_uuid(info)
    SELECT 1
    FROM generate_series(%s,%s) AS series;
  ';

  d_right_sql = '
    INSERT INTO public.uuid_generate_v4(info)
    SELECT 1
    FROM generate_series(%s,%s) AS series;
  ';

  FOR run_number IN 1..4 LOOP
    CALL benchmark.execute(
      FORMAT('gen_random_uuid idx uniq 10^%s', run_number),
      'uuid_generate_v4 idx uniq'::text,
      (10^run_number)::bigint,
      FORMAT(d_left_sql, 1, 10^run_number),
      FORMAT(d_right_sql, 1, 10^run_number),
      d_exec_count, d_up_sql, d_down_sql
    );
  END LOOP;

  FOR run_number IN 1..9 LOOP
    CALL benchmark.execute(
      FORMAT('gen_random_uuid idx uniq %s*10^5',run_number),
      'uuid_generate_v4 idx uniq'::text,
      (run_number*10^5)::bigint,
      FORMAT(d_left_sql, 1, run_number*10^5),
      FORMAT(d_right_sql, 1, run_number*10^5),
      d_exec_count, d_up_sql, d_down_sql
    );
  END LOOP;

END;
$$;

SELECT
  thresh.faster_sql,
  thresh.description_sql_left || ' -  '
  || thresh.description_sql_right || ' | '
  || thresh.times_faster || 'x  | '
  || thresh.average_left || ' ± ' ||
  thresh.standard_deviation_left || ' | ' ||
  thresh.average_right || ' ± ' ||
  standard_deviation_right AS data_row
FROM benchmark.compare_result_outcome AS thresh
INNER JOIN benchmark.compare AS comp
  ON comp.description_sql_left = thresh.description_sql_left
  AND comp.description_sql_right = thresh.description_sql_right
ORDER BY comp.created_on ASC;