はじめに

rubyを学びはじめて３ヶ月目！
キータを使ってみたかった！マークダウンで書いてみたかった！

ということで、railsで簡単なタスク管理アプリを作る際に起きたことを共有します。

PostgreSQL（以下SQL）でインデックスを追加したのに、テストデータによって使われてないことがあります。

インデックスを使って検索されるために、いくつかの条件でデータを作って調べてみました。

【SQL】インデックスとは

特定のカラムにインデックスを追加すると、データベースのテーブルから高速で取り出せるそう。
https://tech.nikkeibp.co.jp/it/members/ITPro/ITBASIC/20000919/1/　

ということで、titleカラムにインデックスをつけました。

schema.rb

create_table "tasks", force: :cascade do |t|
    t.string "title", limit: 500, default: "", null: false
 #省略
    t.index ["title"], name: "index_tasks_on_title"
    t.index ["user_id"], name: "index_tasks_on_user_id"
  end

ただ本当に、インデックスを使ってデータを取り出しているか気になったので調べてみました。

【SQL】Explainというステートメント

ExplainはSQLでの実行計画が効率的に処理できているかを調べるステートメントです。

実行計画の処理については、
Seq Scan・・・テーブルを最初から最後といったように調べます。
Index Scan・・・Index情報から検索条件に合うインデックスを調べてくれます。
などがあります。

詳しく説明をすると一つの記事になりそうなので割愛します。

以下の記事がとてもわかりやすいスライドでまとめられています。
https://www.slideshare.net/MikiShimogai/postgre-sql-explain

【titleにインデックスつけたのにインデックス使われてない！からはじまった検証】

検証0：ダミーデータ(facker:Pokemon.name)５０件から“ゴースト“を探す

３行目のTask.where(title:"ああああああか").explainをコンソールに入力の結果
　 Seq Scan on tasks →シーケンシャルスキャン（上から順に調べる）が使われる：失敗

○データ数が足りないのかと条件変更

検証１：１００万件のデータ（title1~title1000000で“title1000”)を探す

１００万件のデータが浮かばれないのでコード非表示
　→シーケンシャルスキャン（上から順に調べる）が使われる：失敗

○検証１でtitleが繰り返されて生成されている(密度が薄い)のがだめだということで条件変更

下記記事より引用
インデックス使用状況の検証

非常に小さなテストデータを使用することも、結果に特に致命的な影響を与えます。

100,000行から1,000行を選択する場合は、インデックスが使用される可能性がありますが、100行から1行を選択する場合はインデックスはまず使用されません。

なぜなら、100行はおそらく1つのディスクページに収まるため、1ページを逐次読み取るよりも高速な計画は存在しないからです。
https://www.postgresql.jp/document/10/html/indexes-examine.html

ということで、君のデータは密度が薄かったのだよといわれていたので、fackerを駆使しschemaファイルに濃いデータを生成する。

検証２：２０万件のデータ(200件以上の異なるデータ)から“hogehoge”を探す

10種類以上のtitleのデータ型に対応するデータを２０種類を１０００セット作る（２０×1000=２０００００件）

seed.rb

1000.times do |n|

  10.times do |n|
    title = Faker::Pokemon.name
    content = Faker::Pokemon.name
    limit_time = Faker::Date.between(2.days.ago, Date.today)
    status = Faker::Number.between(1, 3)
    priority_color = Faker::Color.color_name

    Task.create!(title: title,
                 content: content,
                 limit_time: limit_time,
                 status: status,
                 priority_color: priority_color,
                 )
  end

  10.times do |n|
    title = Faker::Color.name
省略
end

Task.where(title:"hogehoge").explainを入力...

Bitmap Index Scan→インデックス使用される！：成功

まとめ

１.SQLでは最も効率の良いデータの実行計画（検索方法）を探します。
　Explainを使うと、どの実行計画を使おうとしているか表示してくれます。

2.単純な（１００件から1件を探してくるような）データを探す時は、シーケンシャルスキャン（上から順に探す）を使っている（たとえ、インデックスをつけたとしても！）

3.SQLでインデックスが使われるのは、データ数が多くてなおかつ、そのデータの種類が多い場合です。

【PostgreSQL】インデックス必要？explainで確認できない