PostgreSQL インデックス作成オプション
次のブログ記事では複数のインデックス使用時のパフォーマンスについて記載しています。
パフォーマンス測定用のスクリプトは後述しています
こちらが私のLinux環境におけるスクリプトの実行結果です
| インデックス | 実行時間(平均) | サイズ |
|---|---|---|
| 複合 | 0.121ms | 10,264 |
| マージ | 2.542ms | 7,274+7,004 |
| カバーリング | 0.247ms | 31,563 |
| ハッシュ | 0.2698ms | 49,070 + 46,137 |
パフォーマンス測定用スクリプト
$ 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 ""
#psql -Xc "DROP INDEX IF EXISTS test_table_hash_int100;""$JOBS
出力
*** Composite ***
CREATE INDEX
pgbench (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
transaction type: -
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 1
duration: 1800 s
number of transactions actually processed: 29713610
latency average = 0.121 ms
initial connection time = 6.156 ms
tps = 16507.616402 (without initial connection time)
DROP INDEX
*** Merge / bitmaps scan ***
CREATE INDEX
CREATE INDEX
pgbench (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
transaction type: -
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 1
duration: 1800 s
number of transactions actually processed: 1426322
latency average = 2.524 ms
initial connection time = 5.858 ms
tps = 792.402766 (without initial connection time)
DROP INDEX
DROP INDEX
*** Covering index ***
CREATE INDEX
pgbench (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
transaction type: -
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 1
duration: 1800 s
number of transactions actually processed: 14574199
latency average = 0.247 ms
initial connection time = 5.795 ms
tps = 8096.802439 (without initial connection time)
DROP INDEX
*** Hash ***
CREATE INDEX
CREATE INDEX
pgbench (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
transaction type: -
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 1
duration: 1800 s
number of transactions actually processed: 1334541
latency average = 2.698 ms
initial connection time = 5.773 ms
tps = 741.413178 (without initial connection time)
postgres@masami-L:~$