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 · Tweet

You might also enjoy

Dokku on DigitalOcean

Package Your JavaScript Libraries With Rollup

navigator.sendBeacon and Rails

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