synth.sql

application/sql

Download

/*
fsync = off
max_parallel_workers_per_gather = 8
min_parallel_table_scan_size = 0
min_parallel_index_scan_size = 0
max_worker_processes = 64
work_mem='256MB'
jit_above_cost=0
jit_optimize_above_cost=0
jit_inline_above_cost=0
 */
 
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (x1 text, x2 text, x3 text, x4 text, x5 numeric,
  val1 numeric,
  val2 numeric,
  val3 numeric,
  val4 numeric,
  val5 numeric,
  val6 numeric,
  val7 numeric,
  val8 numeric,
  val9 numeric,
  val10 numeric,
  val11 numeric,
  val12 numeric,
  val13 numeric,
  val14 numeric,
  val15 numeric,
  val16 numeric,
  val17 numeric,
  val18 numeric
);
INSERT INTO t1 (x1,x2,x3,x4,x5,val1)
  SELECT
    repeat('a',75)||(x%2),
    repeat('b',75)||(x%4),
	repeat('c',75)||(x%8),
	repeat('d',75)||(x%64),
	x%20000,
	random()
  FROM generate_series(1,1E5) AS x;

CREATE TABLE t2 (x1 text, x2 text, x3 text, x4 text, x5 numeric, counter numeric);
INSERT INTO t2 (x1,x2,x3,x4,x5,counter)
  SELECT
    repeat('a',75)||(x%2),
    repeat('b',75)||(x%4),
	repeat('c',75)||(x%8),
	repeat('d',75)||(x%64),
	x%20000,
	random()
  FROM generate_series(1,1E5) AS x;
VACUUM ANALYZE t1,t2;

EXPLAIN ANALYZE
SELECT t1.x1,t1.x2,t1.x3,t1.x4,t1.x5,
  sum(t1.val1*t2.counter),
  sum(t1.val2*t2.counter),
  sum(t1.val3*t2.counter),
  sum(t1.val4*t2.counter),
  sum(t1.val5*t2.counter),
  sum(t1.val6*t2.counter),
  sum(t1.val7*t2.counter),
  sum(t1.val8*t2.counter),
  sum(t1.val9*t2.counter),
  sum(t1.val10*t2.counter),
  sum(t1.val11*t2.counter),
  sum(t1.val12*t2.counter),
  sum(t1.val13*t2.counter),
  sum(t1.val14*t2.counter),
  sum(t1.val15*t2.counter),
  sum(t1.val16*t2.counter),
  sum(t1.val17*t2.counter),
  sum(t1.val18*t2.counter)
FROM t1 JOIN t2 ON (t1.x1=t2.x1 AND t1.x2=t2.x2 AND t1.x3=t2.x3 AND t1.x4=t2.x4)
GROUP BY t1.x1,t1.x2,t1.x3,t1.x4,t1.x5;