Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

はじめに

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

ということで、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)50件から“ゴースト“を探す

スクリーンショット 2018-09-14 12.32.26.png
3行目のTask.where(title:"ああああああか").explainをコンソールに入力の結果
  Seq Scan on tasks →シーケンシャルスキャン(上から順に調べる)が使われる:失敗

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

検証1:100万件のデータ(title1~title1000000で“title1000”)を探す

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

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

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

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

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

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

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

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

10種類以上のtitleのデータ型に対応するデータを20種類を1000セット作る(20×1000=200000件)

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を入力...
スクリーンショット 2018-09-14 12.36.27.png

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

まとめ

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

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

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away