#!/bin/bash

# psql plus connection options
PSQL="/usr/local/pgsql/bin/psql -p 5830 -U testdb testdb"
#PSQL="valgrind --callgrind-out-file=/tmp/callgrid.out.%p --tool=callgrind /usr/local/pgsql/bin/postgres --single testdb -D /var/lib/pgdata-8_3"

# steps count
STPCNT=20
# step len (number of ids)
STPLEN=100

echo
echo ------------------------------------------
echo Creating test environment...
echo ------------------------------------------
$PSQL <<EOF
CREATE TABLE table1
(
  table1_id serial,
  table1_fld integer,
  CONSTRAINT pk_table1 PRIMARY KEY (table1_id)
);
EOF

echo
echo ------------------------------------------
echo Adding data...
echo ------------------------------------------
i=1
while [ "$i" -le 5000 ]; do
  $PSQL -q -c "INSERT INTO table1 (table1_fld) SELECT (random() * 1234567);"
  let "i += 1"
done 
$PSQL -q -c "ANALYZE table1;"

echo
echo ------------------------------------------
echo Testing EXPLAIN w/ NOT IN...
echo ------------------------------------------
id="123456789000"; ids=$id; i=1
for ((j=$STPLEN;$j;j=$j-1)); do ids=$ids','$id; done;
idsb=$ids
while [ "$i" -le $STPCNT ]; do
  st=$((`date +'%s%N'`))
  $PSQL <<EOF >/dev/null
    EXPLAIN SELECT 1 FROM table1 WHERE table1_id NOT IN ($idsb);
EOF
  ft=$((`date +'%s%N'`))
  echo -e $(($i*$STPLEN))' ids: '$(( ($ft-$st) / 1000000 ))' ms'
  let "i += 1"
  idsb=$idsb','$ids
done 

echo
echo ------------------------------------------
echo Testing EXPLAIN w/ IN...
echo ------------------------------------------
id="123456789000"; ids=$id; i=1
for ((j=$STPLEN;$j;j=$j-1)); do ids=$ids','$id; done;
idsb=$ids
while [ "$i" -le $STPCNT ]; do
  st=$((`date +'%s%N'`))
  $PSQL <<EOF >/dev/null
    EXPLAIN SELECT 1 FROM table1 WHERE table1_id IN ($idsb);
EOF
  ft=$((`date +'%s%N'`))
  echo -e $(($i*$STPLEN))' ids: '$(( ($ft-$st) / 1000000 ))' ms'
  let "i += 1"
  idsb=$idsb','$ids
done 

echo
echo ------------------------------------------
echo Deleting test environment...
echo ------------------------------------------
$PSQL <<EOF
DROP TABLE table1 CASCADE;
EOF