#!/bin/bash

# psql plus connection options
PSQL="/usr/local/pgsql/bin/psql -p 58301 -U postgres database1"
# test rows count
CNT=100

echo
echo ------------------------------------------
echo Creating the test\'s environment...
echo ------------------------------------------
$PSQL <<EOF
CREATE TABLE table1
(
  table1_id serial,
  table1_point point,
  table1_flag smallint,
  CONSTRAINT pk_table1 PRIMARY KEY (table1_id)
)
WITH (OIDS=FALSE);

CREATE INDEX i_table1__table1_point
  ON table1
  USING gist
  (box(table1_point, table1_point))
  WHERE table1_flag = 1;
EOF

echo
echo ------------------------------------------
echo Trying to add $CNT records...
echo ------------------------------------------
i=1
while [ "$i" -le $CNT ]; do
  $PSQL -q -c "INSERT INTO table1 (table1_point, table1_flag) SELECT point(random() * 123, random() * 123), 0;"
  let "i += 1"
done 
$PSQL -q -c "ANALYZE table1;"

echo
echo ------------------------------------------
echo Updating $CNT records...
echo ------------------------------------------
i=1
while [ "$i" -le $CNT ]; do
  $PSQL -q -c "UPDATE table1 SET table1_flag = 1 WHERE table1_id IN (SELECT table1_id FROM table1);"
  let "i += 1"
done 

echo
echo ------------------------------------------
echo 1st time obtaining \"seq-scan\" count and plan...
echo ------------------------------------------
$PSQL -e <<EOF
SELECT table1_flag, count(*) FROM table1
GROUP BY table1_flag;

EXPLAIN ANALYZE SELECT table1_flag, count(*) FROM table1
GROUP BY table1_flag;
EOF

echo ------------------------------------------
echo 1st time obtaining \"index-scan\" count and plan...
echo ------------------------------------------
$PSQL -e <<EOF
SELECT count(*) FROM table1
WHERE table1_flag = 1;

EXPLAIN ANALYZE SELECT count(*) FROM table1
WHERE table1_flag = 1;
EOF

echo
echo ------------------------------------------
echo 2nd time obtaining \"seq-scan\" count and plan...
echo ------------------------------------------
$PSQL -e <<EOF
SELECT table1_flag, count(*) FROM table1
GROUP BY table1_flag;

EXPLAIN ANALYZE SELECT table1_flag, count(*) FROM table1
GROUP BY table1_flag;
EOF

echo ------------------------------------------
echo 2nd time obtaining \"index-scan\" count and plan...
echo ------------------------------------------
$PSQL -e <<EOF
SELECT count(*) FROM table1
WHERE table1_flag = 1;

EXPLAIN ANALYZE SELECT count(*) FROM table1
WHERE table1_flag = 1;
EOF

echo
echo ------------------------------------------
echo Deleting the test\'s environment...
echo ------------------------------------------
$PSQL <<EOF
DROP TABLE table1 CASCADE;
EOF