full_index_test_10m_rows.sh

export PGHOST=/var/run/postgresql
export PGUSER=postgres
export PGDATABASE=postgres
export PGOPTIONS='-c maintenance_work_mem=4GB' # Helps to speed up CREATE INDEX for most index types
CLIENTS=2
JOBS=1
DURATION=1800


SQL_DDL=$(cat << "EOF"
DROP TABLE IF EXISTS test_table;
CREATE UNLOGGED TABLE IF NOT EXISTS test_table (
  id bigint /*primary key*/ not null, -- don't need the UQ for our test
  text1 text not null, /* 1 KiB of random data */
  text2 text not null, /* 255 bytes of random data */
  /* cardinality columns */
  int1000 bigint not null, /* ranges 0..999, cardinality: 1000 */
  int100 bigint not null, /* 0..99, card: 100 */
  int10 bigint not null /* 0..10, card: 10 */
);
EOF
)
echo "$SQL_DDL"
echo "$SQL_DDL" | psql -X

SQL_INIT_DATA=$(cat << "EOF"
TRUNCATE test_table;
INSERT INTO test_table
SELECT
  id,
  (select string_agg(random()::text,'') from generate_series(1,52)) text1, /* length(random()::text) ~19B */
  (select string_agg(random()::text,'') from generate_series(1,14)) text2,
  random()*1000 int1000,
  random()*100 int100,
  random()*10
FROM
  generate_series(1, 1e7) id;
VACUUM ANALYZE test_table;
EOF
)
echo "$SQL_INIT_DATA"
echo "$SQL_INIT_DATA" | psql -X
echo -e "\nData size:"
psql -X -c "\dt+ test_table"

SQL=$(cat << "EOF"
\set int1000 random(0, 999)
\set int100 random(0, 99)
SELECT count(*) FROM test_table WHERE int1000 = :int1000 AND int100 = :int100;
EOF
)

echo -e "\n\n*** Composite ***\n"
psql -Xc "CREATE INDEX IF NOT EXISTS test_table_composite ON test_table (int1000, int100);"
echo ""
echo "$SQL" | pgbench -n -f- -T $DURATION -c $CLIENTS -j $JOBS
psql -Xc "DROP INDEX IF EXISTS test_table_composite;"

echo -e "\n\n*** Merge / bitmaps scan ***\n"
psql -Xc "CREATE INDEX IF NOT EXISTS test_table_int1000 ON test_table (int1000);"
psql -Xc "CREATE INDEX IF NOT EXISTS test_table_int100 ON test_table (int100);"

echo ""
echo "$SQL" | pgbench -n -f- -T $DURATION -c $CLIENTS -j $JOBS
psql -Xc "DROP INDEX IF EXISTS test_table_int1000;"
psql -Xc "DROP INDEX IF EXISTS test_table_int100;"

echo -e "\n\n*** Covering index ***\n"
psql -Xc "CREATE INDEX IF NOT EXISTS test_table_covering ON test_table (int1000) INCLUDE (int100);"
echo ""
echo "$SQL" | pgbench -n -f- -T $DURATION -c $CLIENTS -j $JOBS
psql -Xc "DROP INDEX IF EXISTS test_table_covering;"

echo -e "\n\n*** Hash ***\n"
psql -Xc "CREATE INDEX IF NOT EXISTS test_table_hash_int1000 ON test_table USING hash (int1000);"
psql -Xc "CREATE INDEX IF NOT EXISTS test_table_hash_int100 ON test_table USING hash(int100);"
echo ""
echo "$SQL" | pgbench -n -f- -T $DURATION -c $CLIENTS -j $JOBS
#psql -Xc "DROP INDEX IF EXISTS test_table_hash_int1000;"
#psql -Xc "DROP INDEX IF EXISTS test_table_hash_int100;"