TPC-H with Postgres

TPC-H is a database benchmark.

git clone
cd tpch-kit/dbgen
make -f Makefile.osx

Create the database and load the schema

createdb tpch
psql tpch -f dss.ddl

Generate data

./dbgen -vf -s 1

Load the data

for i in `ls *.tbl`; do
  echo "Loading $table..."
  sed 's/|$//' $i > /tmp/$i
  psql tpch -q -c "TRUNCATE $table"
  psql tpch -c "\\copy $table FROM '/tmp/$i' CSV DELIMITER '|'"

Generate queries

mkdir /tmp/queries
for i in `ls queries/*.sql`; do
  tail -r $i | sed '2s/;//' | tail -r > /tmp/$i

DSS_QUERY=/tmp/queries ./qgen | sed 's/limit -1//' | sed 's/day (3)/day/' > queries.sql

Run queries

psql tpch -c "ANALYZE VERBOSE"
psql tpch < queries.sql

Bonus: Add Indexes with Dexter

Install Dexter

gem install pgdexter

And run

for i in `seq 1 5`; do
  dexter tpch queries.sql --input-format sql --create

Published April 29, 2018

You might also enjoy

Adding CSP to Rails

Google OAuth with Devise

Anonymizing IPs in Ruby

All code examples are public domain.
Use them however you’d like (licensed under CC0).