TPC-DS with Postgres

TPC-DS is a database benchmark.

git clone https://github.com/gregrahn/tpcds-kit.git
cd tpcds-kit/tools
make OS=MACOS

Create the database and load the schema

createdb tpcds
psql tpcds -f tpcds.sql

Generate data

./dsdgen -FORCE -VERBOSE

Load the data

for i in `ls *.dat`; do
  table=${i/.dat/}
  echo "Loading $table..."
  sed 's/|$//' $i > /tmp/$i
  psql tpcds -q -c "TRUNCATE $table"
  psql tpcds -c "\\copy $table FROM '/tmp/$i' CSV DELIMITER '|'"
done

Generate queries

./dsqgen -DIRECTORY ../query_templates -INPUT ../query_templates/templates.lst \
  -VERBOSE Y -QUALIFY Y -DIALECT netezza

Run queries

psql tpcds -c "ANALYZE VERBOSE"
psql tpcds < query_0.sql

Bonus: Add Indexes with Dexter

Install Dexter

gem install pgdexter

And run

for i in `seq 1 10`; do
  dexter tpcds query_0.sql --input-format sql --create
done

Published April 29, 2018


You might also enjoy

The Safely Pattern

navigator.sendBeacon and Rails

The Two Metrics You Need


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