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