Skip to content

Instantly share code, notes, and snippets.

@kmoppel
Created December 9, 2022 00:02
Show Gist options
  • Save kmoppel/9ba1f938140448dc1919a21c215196b1 to your computer and use it in GitHub Desktop.
Save kmoppel/9ba1f938140448dc1919a21c215196b1 to your computer and use it in GitHub Desktop.
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;"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment