通販の注文

通販の注文を模した 4 テーブルで始まる。行数は customers が 5,000、products が 1,000、orders が 30,000、order_items が 90,000 である。orders.customer_id と order_items.order_id、order_items.product_id は外部キーで、参照先の主キーを指す。

Postgres は、主キーと一意制約には索引を作るが、外部キーを持つ側の列には作らない。そのため、この 3 列には最初は索引がない。テーブルどうしの関係は、「試す」の「テーブル構成」で図にして見られる。

次の順に実行すると、外部キーの索引の有無で実行計画がどう変わるかを比べられる。

1. 「顧客 1 人の注文と明細」と「顧客ごとの注文数」を実行し、Seq Scan の行と Buffers の値を見る
2. 「外部キーの列に索引を張る」を実行する
3. 1 の 2 つを実行し直し、Index Scan か Bitmap Index Scan に変わったことと、Buffers の値が減ったことを確かめる

「OFFSET で 1,001 ページ目を読む」と「キーセットで同じページを読む」は、同じ 20 行を返す。Index Scan の行の rows を見ると、OFFSET は読み飛ばす 20,000 行も索引から読んでいる。キーセットは、前のページの最後の行の ordered_at と id を条件に渡し、20 行だけを読む。

explain (analyze, buffers)
select o.id, o.ordered_at, i.product_id, i.qty
from orders o
join order_items i on i.order_id = o.id
where o.customer_id = 42;