インデックスを作り計画が変わるか確かめる

目標

さまざまな種類のインデックスを自分で作り、実行計画がどう変わるかを目で確認します。インデックスの作り方よりも、インデックスがいつ使われ、いつ無視されるのかを知ることが、このラボの目的です。

なぜ重要なのか

インデックスを作ったのに計画が変わらないときに、最もよくあるアドバイスは、強制的に使わせるというものです。これは診断ではなく症状の抑え込みで、強制的に使わせると、かえって遅くなるケースが実際に多くあります。インデックススキャンは、一致した行ごとにヒープページをランダムに訪問するので、返す行が多いと、表全体をランダムな順序で読む形になるからです。

そのため、インデックスが使われないときに投げるべき質問は、2つに分かれます。オプティマイザーが正しいので使われないのか、自分のクエリがインデックスを使えない形なので使われないのか。前者は、インデックスを変えるか諦める必要があり、後者は、クエリを直す必要があります。対応が正反対なので、区別が先です。

このラボでは、部分インデックスと式インデックス、カバリングインデックスまで作りながら、「インデックスは1つではなく、何種類もある」という感覚を身につけます。

ステップ

EXPLAIN SELECT count(*) FROM orders WHERE channel = 'partner';の出力を、/root/expではなく/root/plan_seq.txtに保存します。シーケンシャルスキャンが出る必要があります。
orders (ordered_at)に、idx_orders_ordered_atという名前のB-treeインデックスを作ります。
ordered_atに範囲条件をかけた照会のEXPLAIN出力を、/root/plan_idx.txtに保存します。計画にidx_orders_ordered_atが出る必要があります。
order_itemsに、idx_order_items_order_productという名前で、(order_id, product_id)の順序の複合インデックスを作ります。
orders (ordered_at)に、status = 'pending'の条件を付けた部分インデックスidx_orders_pendingを作ります。
customersに、lower(email)に対する式インデックスidx_customers_email_lowerを作ります。
productsに、キーのカラムは(category, price)、INCLUDEのカラムはnameのカバリングインデックスidx_products_cat_priceを作ります。
シーケンシャルスキャンをオフにした状態で、ordered_atの条件でcount(*)を求めるEXPLAIN出力を、/root/plan_only.txtに保存します。Index Only Scanが出る必要があります。

参考

インデックス定義の確認: \d ordersまたはSELECT indexdef FROM pg_indexes WHERE tablename='orders';
インデックスサイズの比較: SELECT pg_size_pretty(pg_relation_size('인덱스이름'));(プレースホルダーはインデックス名です)
シーケンシャルスキャンを一時的にオフにする: SET enable_seqscan = off;。診断用としてだけ使います。本番の設定にしてはいけません。
よくある間違い1: インデックス名を違う名前にすると、採点されません。問題文の名前をそのまま使ってください。
よくある間違い2: 式インデックスは、クエリがまったく同じ式を使わないと使われません。lower(email)のインデックスは、lower(trim(email))の条件には使われません。

インデックスがないときの計画を残す

EXPLAIN SELECT count(*) FROM orders WHERE channel = 'partner';の出力を、/root/expではなく/root/plan_seq.txtに保存します。シーケンシャルスキャンが出る必要があります。

EXPLAINは、クエリの前に付けるだけです。まだインデックスがないカラムを条件に使うと、シーケンシャルスキャンが出ます。

基本のB-treeインデックスを作る

orders (ordered_at)に、idx_orders_ordered_atという名前のB-treeインデックスを作ります。

CREATE INDEX index_name ON table_name (column_name)の形です。インデックス名を正確に合わせないと、採点されません。

インデックスを使う計画を残す

ordered_atに範囲条件をかけた照会のEXPLAIN出力を、/root/plan_idx.txtに保存します。計画にidx_orders_ordered_atが出る必要があります。

範囲の狭い条件をかけると、オプティマイザーがインデックスを選びます。カラムに関数をかぶせると、インデックスを使えません。

複合インデックスのカラム順序を決める

order_itemsに、idx_order_items_order_productという名前で、(order_id, product_id)の順序の複合インデックスを作ります。

等号条件でよく使われるカラムが前に来る必要があります。順序が変わると、別のインデックスです。

部分インデックスでサイズを減らす

orders (ordered_at)に、status = 'pending'の条件を付けた部分インデックスidx_orders_pendingを作ります。

CREATE INDEX ... WHERE conditionの形で、一部の行だけを含められます。サイズを比べてみてください。

式インデックスを作る

customersに、lower(email)に対する式インデックスidx_customers_email_lowerを作ります。

カラムではなく、計算結果でインデックスを作れます。括弧で囲む必要があり、クエリも同じ式を使わないと使われません。

カバリングインデックスでヒープの訪問を減らす

productsに、キーのカラムは(category, price)、INCLUDEのカラムはnameのカバリングインデックスidx_products_cat_priceを作ります。

探索に使うキーのカラムと、結果にだけ必要なカラムを区別して定義できる句があります。

Index Only Scanを観察する

シーケンシャルスキャンをオフにした状態で、ordered_atの条件でcount(*)を求めるEXPLAIN出力を、/root/plan_only.txtに保存します。Index Only Scanが出る必要があります。

表が小さいと、オプティマイザーはシーケンシャルスキャンを選びます。診断の目的で、シーケンシャルスキャンを一時的に止めて、計画を取り直してみてください。