rungisttests.sh

#!/bin/bash

NROWS=$1

echo "BEGIN;"
echo "DROP TABLE IF EXISTS points, points_ordered;"
echo "CREATE TABLE points (x int4, y int4);"

cat > /tmp/creategistdata.c <<EOF
#include <stdio.h>
#include <stdlib.h>

int main(int argc, char **argv)
{
  int nrows;
  int i;

  if (argc != 2)
    exit(1);

  nrows = atoi(argv[1]);
  for (i = 0; i < nrows; i++)
    printf("%d\t%d\n", random() % 100000, random() % 100000);
}
EOF
gcc /tmp/creategistdata.c -o /tmp/creategistdata

echo "COPY points FROM stdin;"
/tmp/creategistdata $NROWS
echo "\."

echo "CREATE TABLE points_ordered AS SELECT * FROM points ORDER BY x, y;";

echo "COMMIT;"

echo "CREATE TABLE results (testname text, nrows int4, begintime timestamp, endtime timestamp, blks_accessed int4, totalrowsmatched int4);"

# Run tests

function measureindexquality {
cat <<EOF
  set enable_seqscan=off;
  SELECT pg_stat_reset_single_table_counters('testindex'::regclass);
  DO \$\$
    declare
      totalrows int4 := 0;
      xx int4;
      yy int4;
      ordered bool;
    begin
      -- Figure out if this test was on the ordered data set
      ordered := ((SELECT indrelid::regclass::text FROM pg_index where indexrelid='testindex'::regclass) = 'points_ordered');

      FOR xx IN 1..100000 BY 1000 LOOP
        FOR yy IN 1..100000 BY 1000 LOOP
          IF ordered THEN
            totalrows = totalrows + (SELECT COUNT(*) FROM points_ordered WHERE point(x,y) <@ box(point(xx-20, yy-20), point(xx+20, yy+20)));
          ELSE
            totalrows = totalrows + (SELECT COUNT(*) FROM points WHERE point(x,y) <@ box(point(xx-20, yy-20), point(xx+20, yy+20)));
          END IF;
        END LOOP;
      END LOOP;
      UPDATE results SET totalrowsmatched = totalrows WHERE totalrowsmatched IS NULL;
    end;
  \$\$;
  SELECT pg_sleep(1); -- Sleep a bit to make sure we get fresh results from pg_statio
  UPDATE results SET blks_accessed = 
        (SELECT idx_blks_hit + idx_blks_read FROM pg_statio_user_indexes WHERE indexrelname='testindex')
  WHERE blks_accessed IS NULL;
EOF
}

function runtest {
  ITERATIONS=1
  TESTNAME=$1
  TESTSQL=$2
  for ((n=1; n <= $ITERATIONS; n++))
  do
    echo "CHECKPOINT;"
    echo "INSERT INTO results (testname, nrows, begintime) VALUES ('$TESTNAME', $NROWS, now());"
    echo $TESTSQL
    echo "UPDATE results SET endtime = now() WHERE endtime IS NULL;"
    measureindexquality
    echo "DROP INDEX testindex;"
  done;
}

runtest "points ordered buffered" "CREATE INDEX testindex ON points_ordered USING gist (point(x,y)) WITH (buffering = 'on');"
runtest "points unordered buffered" "CREATE INDEX testindex ON points USING gist (point(x,y)) WITH (buffering = 'on');"

runtest "points ordered auto" "CREATE INDEX testindex ON points_ordered USING gist (point(x,y)) WITH (buffering = 'auto');"
runtest "points unordered auto" "CREATE INDEX testindex ON points USING gist (point(x,y)) WITH (buffering = 'auto');"

runtest "points ordered unbuffered" "CREATE INDEX testindex ON points_ordered USING gist (point(x,y)) WITH (buffering = 'off');"
runtest "points unordered unbuffered" "CREATE INDEX testindex ON points USING gist (point(x,y)) WITH (buffering = 'off');"


cat <<EOF
SELECT testname, nrows,
       MIN(endtime - begintime) AS mintime,
       AVG(endtime - begintime) AS avgtime,
       MAX(endtime - begintime) AS maxtime,
       MIN(blks_accessed) AS minaccesses,
       MAX(blks_accessed) AS maxaccesses,
       MIN(totalrowsmatched) AS minmatches,
       MAX(totalrowsmatched) AS maxmatches
FROM results GROUP BY testname, nrows ORDER BY nrows, testname;
EOF