![[アップデート] Amazon Aurora DSQL でパーシャルインデックスが使えるようになりました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-7e1a533ff5b5a6a8eb5d4ae82bacdcd6/5845157a3c941213d245ec7cdf7358e8/amazon-aurora?w=3840&fm=webp)
[アップデート] Amazon Aurora DSQL でパーシャルインデックスが使えるようになりました
いわさです。
Amazon Aurora DSQL でこれまでインデックスは CREATE INDEX ASYNC でテーブルの全行を対象にする形しかなく、一部の行だけを対象にすることはできませんでした。
これが先日のアップデートで、パーシャルインデックスがサポートされました！
通常のインデックスは CREATE INDEX でテーブルの全行をインデックスに登録します。
パーシャルインデックスは WHERE 句を付けて、その条件に合うレコードだけをインデックスに登録できます。
PostgreSQL のドキュメントでは次のように説明されています。
A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicate.
今回こちらを使ってみたので紹介します。
実際にパーシャルインデックスを作ってみる
東京リージョンのクラスターで試します。
検証用に、注文を表す orders テーブルを用意しまして、注文の状態を表す status 列があり、処理中を表す open、完了済みを表す completed のステータスを持つレコードを混在させておきます。
SELECT status, count(*) FROM orders GROUP BY status;
status | count
-----------+-------
open | 100
completed | 33000
件数は適当です。
この orders テーブルに対して、open の行だけを対象にしたインデックスを作りたい時があります。completed が多すぎるような偏りのあるパターン。
今回のアップデートでCREATE INDEX ASYNC の末尾に WHERE status = 'open' を付けることができるようになりまして、まぁこれだけです。
CREATE INDEX ASYNC open_orders_idx ON orders (customer_id) WHERE status = 'open';
ちなみに Aurora DSQL のインデックス作成の場合は非同期なので ASYNC が必須で、実行するとすぐに job_id が返ってバックグラウンドで構築が進みます。
しばらく待ってから定義を確認すると、WHERE (status = 'open'::text) 付きのインデックスができていました。
SELECT indexname, indexdef FROM pg_indexes WHERE tablename='orders';
indexname | indexdef
-----------------+-------------------------------------------------------------------------------------------------------------------
orders_pkey | CREATE UNIQUE INDEX orders_pkey ON public.orders USING btree_index (id) INCLUDE (status, customer_id, created_at)
open_orders_idx | CREATE INDEX open_orders_idx ON public.orders USING btree_index (customer_id) WHERE (status = 'open'::text)
パーシャルインデックスが使われるクエリを発行してみる
パーシャルインデックスは、クエリの絞り込み条件がインデックスの条件の範囲に収まるときにだけ使われます。
Aurora DSQL uses a partial index for any query whose filter falls within the index's condition.
実際に EXPLAIN つけて観察してみましょう。
まずインデックスが使われるであろう status='open' で絞るクエリを発行してみます。
EXPLAIN SELECT * FROM orders WHERE status='open' AND customer_id=42;
QUERY PLAN
----------------------------------------------------------------------------------------
Index Scan using open_orders_idx on orders (cost=200.27..208.28 rows=1 width=40)
Index Cond: (customer_id = 42)
-> Storage Scan on open_orders_idx (cost=200.27..208.28 rows=1 width=40 loops=1)
-> B-Tree Scan on open_orders_idx (cost=200.27..208.28 rows=1 width=40 loops=1)
Index Cond: (customer_id = 42)
-> Storage Lookup on orders (cost=200.27..208.28 rows=1 width=40 loops=1)
Projections: id, status, customer_id, created_at
-> B-Tree Lookup on orders (cost=200.27..208.28 rows=1 width=40 loops=1)
Index Scan using open_orders_idx になっていて、作ったパーシャルインデックスが使われていますね。ええじゃないか。
次に status='completed' で絞ってみましょう。このインデックスは使えないはずですが果たして...
EXPLAIN SELECT * FROM orders WHERE status='completed' AND customer_id=42;
QUERY PLAN
-----------------------------------------------------------------------------
Full Scan (btree-table) on orders (cost=4237.54..5026.11 rows=67 width=40)
-> Storage Scan on orders (cost=4237.54..5026.11 rows=66 width=40)
Projections: id, status, customer_id, created_at
Filters: ((status = 'completed'::text) AND (customer_id = 42))
-> B-Tree Scan on orders (cost=4237.54..5026.11 rows=33100 width=40)
こちらは Full Scan になってますね。
status で絞らず customer_id だけで検索した場合も見てみます。
EXPLAIN SELECT * FROM orders WHERE customer_id=42;
QUERY PLAN
-----------------------------------------------------------------------------
Full Scan (btree-table) on orders (cost=4237.54..4943.36 rows=67 width=40)
-> Storage Scan on orders (cost=4237.54..4943.36 rows=67 width=40)
Projections: id, status, customer_id, created_at
Filters: (customer_id = 42)
-> B-Tree Scan on orders (cost=4237.54..4943.36 rows=33100 width=40)
こちらも Full Scan でした。なるほど！
さいごに
本日は Amazon Aurora DSQL でパーシャルインデックスが使えるようになったので確認してみました。
CREATE INDEX ASYNC に WHERE 句を付けるだけで対象を絞れて、想定どおり条件の内側のクエリでだけ使われました。
今回みたいに処理中は少数で、完了済みが大量にたまるようなテーブルを持っているなら、その一部だけにインデックスを作れば、インデックスサイズを小さく保てるので良さそうです。
