SQLiteで列順だけを変える
Python 3のsqlite3を使い、メモリ内DBに1万行のイベントを作ります。tenantは100種類、createdは増加する整数です。tenant=7かつcreated>=8000の行を取得し、created順に並べます。二つの独立したDBで、索引だけを(tenant,created)と(created,tenant)に変えます。同じDBに両方を作ると、どちらを使うかをプランナが選ぶ別の実験になるため、ここでは分けて作ります。実DBへの変更はありません。
結果と実行計画を並べる
コードを実行すると、どちらも20行を返し、その内容が同じであることをassertします。EXPLAIN QUERY PLANの出力では、検索に使う条件やcovering indexの表示を比較します。索引名だけでなく、tenantの等価条件とcreatedの範囲がどこまで検索条件へ入ったかに注目します。ANALYZEでこの小さなデータの統計を作りますが、出力文字列や選択方針はSQLiteの版などで変わります。特定の文言を正解として固定しません。
import sqlite3
for name, columns in [("tenant_time", "tenant, created"),
("time_tenant", "created, tenant")]:
db = sqlite3.connect(":memory:")
db.execute("create table events(tenant integer, created integer)")
db.executemany("insert into events values (?,?)",
[(i % 100, i) for i in range(10000)])
db.execute(f"create index {name} on events({columns})")
db.execute("analyze")
sql = "select tenant, created from events where tenant=? and created>=? order by created"
rows = db.execute(sql, (7, 8000)).fetchall()
plan = db.execute("explain query plan " + sql, (7, 8000)).fetchall()
print(name, "rows", len(rows), "plan", plan)
assert rows == [(7, i) for i in range(8007, 10000, 100)]
db.close()等価条件の後へ範囲を置く理由
tenantが先頭なら、tenant=7の連続した領域を探し、その中でcreated>=8000の開始位置へ進めます。createdが先頭なら、8000以降の多くのtenantを含む範囲を見て、tenant=7を確認する処理が必要になり得ます。ただし、後ろの列を使えないと一般化してはいけません。プランナのskip-scanなど、統計や版による別の選択肢があります。B木のキー順が候補範囲へどう影響するかを説明することが目的です。
別の検索では評価が逆転する
tenantを指定せず全体の新着を読む要求なら、createdが先頭の索引が有用かもしれません。書き込みのたびに索引の維持費用も増えるため、検索一つだけを見てすべての列順を追加しません。SQLiteの結果をPostgreSQLの実行計画と同一視せず、実際のエンジンで再確認します。測定する場合はデータ量、分布、キャッシュ条件をそろえ、実行計画と時間を別々に記録します。小さいメモリ内実験の秒数を本番の改善率として使わないでください。
索引だけで要求した列を返せるcoveringな検索と、行本体を取りに行く検索も分けて考えます。ここでは取得列をtenantとcreatedに絞っています。大きな本文列をSELECTへ追加すると、同じWHERE条件でも必要なアクセスが変わり、比較結果が変わる場合があります。