gistfile1.sql

\unset ECHO
\set QUIET true
\timing off

DROP TABLE IF EXISTS small_integers, integers, big_integers;

\unset QUIET
\set ECHO queries

\echo Creating and Interacting with Integers
\echo

\timing on
\echo Creating Small Integers

CREATE UNLOGGED TABLE small_integers AS
SELECT ((30000 * (x * random() - 1)) / 5000000)::smallint AS id
FROM generate_series(1,5000000) AS x;

\echo
\echo Creating Integers
CREATE UNLOGGED TABLE integers AS
SELECT (random() * x)::int AS id
FROM generate_series(1,5000000) AS x;

\echo
\echo Creating Big Integers
CREATE UNLOGGED TABLE big_integers AS
SELECT (random() * x)::bigint * '1234567890'::bigint AS id
FROM generate_series(1,5000000) AS x;

\echo
\prompt 'Hit [Return] to Continue...' meh
\echo

CREATE INDEX small_integers_id_idx ON small_integers (id);
CREATE INDEX integers_id_idx ON integers (id);
CREATE INDEX big_integers_id_idx ON big_integers (id);

\unset ECHO
\set QUIET
SELECT avg(id) FROM small_integers;
SELECT avg(id) FROM integers;
SELECT avg(id) FROM big_integers;

\timing on
\unset QUIET
\set ECHO queries

\echo
\prompt 'Average for smallint' meh
\echo

SELECT avg(id) FROM small_integers;

\echo
\prompt 'Average for int' meh
\echo

SELECT avg(id) FROM integers;

\echo
\prompt 'Average for bigint' meh
\echo

SELECT avg(id) FROM big_integers;

\echo
\prompt 'Hit [Return] to cleanup...' meh
\timing off
\set QUIET
\unset ECHO
DROP TABLE IF EXISTS small_integers, integers, big_integers;