make_prefetch_test.py

r_size=5000

print "\\timing off"

print "set work_mem = '2GB';"
print "set max_parallel_workers_per_gather = 0;"

for hit_ratio in (1, 2, 4):
  for key_size in (0, 4, 8, 16, 32, 64):
    if key_size == 0:
      key_type = "int"
    else:
      key_type = "text"
    for extra_cols in range(4):
      for s_size in (1000, 10000, 100000, 1000000):
        print "drop table if exists r;"
        print "drop table if exists s;"
        print "create table r (k %s%s);" % (key_type, "".join([", c%d text" % i for i in range(extra_cols)]))
        print "create table s (k %s%s);" % (key_type, "".join([", c%d text" % i for i in range(extra_cols)]))
        if key_type == "int":
          print "insert into r select generate_series(0, %d) %% %d%s;" % (r_size, s_size * hit_ratio, ", 'hello world'" * extra_cols)
          print "insert into s select generate_series(0, %d)%s;" % (s_size, ", 'hello world'" * extra_cols)
        else:
          print "insert into r select to_char(generate_series(0, %d) %% %d, '%s')%s;" % (r_size, s_size * hit_ratio, "0" + "9" * key_size, ", 'hello world'" * extra_cols)
          print "insert into s select to_char(generate_series(0, %d), '%s')%s;" % (s_size, "0" + "9" * key_size, ", 'hello world'" * extra_cols)
        print "analyze;"
        print "select pg_prewarm('r');"
        print "select pg_prewarm('s');"

        print "\\timing on"
        print "\\echo ### BEGIN r_size = %s, s_size = %s, hit_ratio = %s, key_size = %s, extra_cols = %s" % (r_size, s_size, hit_ratio, key_size, extra_cols)
        print "select count(*) from r join s using (k);"
        print "select count(*) from r join s using (k);"
        print "select count(*) from r join s using (k);"
        print "\\echo ### END"
        print "\\timing off"