#!/bin/bash dbname=postgres scale=10000000 echo "select p.id,p.description,sum(s.qty) from sales s inner join part p on p.id = s.partid group by p.id;" > bench.sql echo "explain select p.id,p.description,sum(s.qty) from sales s inner join part p on p.id = s.partid group by p.id;" > bench.explain.sql echo "explain analyze select p.id,p.description,sum(s.qty) from sales s inner join part p on p.id = s.partid group by p.id;" > bench.analyze.sql psql -c "alter system set max_parallel_workers_per_gather = 0;" $dbname psql -c "select pg_reload_conf();" $dbname psql -c "drop table if exists part,sales" $dbname psql -c "create table part (id int primary key, description text not null);" $dbname psql -c "insert into part select x,md5(x::text) from generate_series(1,$scale) x;" $dbname psql -c "vacuum freeze analyze part;" $dbname psql -c "create table sales (partid int, qty int);" $dbname psql -c "show work_mem;" $dbname for i in 1 2 8 16 32 64 128 256 512 1024 2048 4096 8192 16384 32768 65536 131072 262144 524288 1048576 #for i in 512 do echo "Testing $i ..." psql -c "truncate sales;" $dbname # psql -c "insert into sales (partid, qty) select x % ($scale / 100 * $i) + 1, 123 from generate_Series(1,$scale) x;" $dbname psql -c "insert into sales (partid, qty) select x % $i + 1, 123 from generate_Series(1,$scale) x;" $dbname psql -c "vacuum freeze analyze sales;" $dbname psql -c "alter system set enable_resultcache = on;" $dbname psql -c "select pg_Reload_conf();" $dbname echo "resultcache = on;" psql -f bench.analyze.sql $dbname pgbench -n -T 10 -f bench.sql $dbname psql -c "alter system set enable_resultcache = off;" $dbname psql -c "select pg_Reload_conf();" $dbname echo "resultcache = off;" psql -f bench.analyze.sql $dbname pgbench -n -T 10 -f bench.sql $dbname done