postgresqlと複合indexとiowait
rails4.2.0
muninを見ているとぺろぺろあんてなのサーバでiowaitが徐々に増えていることに気がついてスロークエリを探しました。
最近https://twitter.com/prprantena に1時間起きで取り込んだ記事を垂れ流すようにしたのでそのネットワークioかと思ったんですが、日々増えているのでDBあたりかなと思って、スロークエリをログに書き出す設定を有効にしてログ見てました。
# /etc/postgresql/9.4/main/postgresql.conf log_min_duration_statement = 500
遅いクエリでてきました。
SELECT "word_tags"."name" FROM "word_tags" INNER JOIN "word_taggings" ON "word_tags"."id" = "word_taggings"."word_tag_id" WHERE "word_taggings"."post_id" = $1 [["post_id", 245009]]
explainするとpost_id の検索でindexが使われていないことが発覚。
2.1.1 :004 > Post.first.word_tags.explain => EXPLAIN for: SELECT "word_tags".* FROM "word_tags" INNER JOIN "word_taggings" ON "word_tags"."id" = "word_taggings"."word_tag_id" WHERE "word_taggings"."post_id" = $1 [["post_id", 243762]] QUERY PLAN --------------------------------------------------------------------------------------- Nested Loop (cost=0.29..33102.66 rows=51 width=19) -> Seq Scan on word_taggings (cost=0.00..32710.34 rows=51 width=4) Filter: (post_id = 243762) -> Index Scan using word_tags_pkey on word_tags (cost=0.29..7.68 rows=1 width=19) Index Cond: (id = word_taggings.word_tag_id) (5 rows)
db/schema.rb を見るとindexを貼った痕跡ある。
add_index "word_taggings", ["post_id"], name: "index_word_taggings_on_post_id", using: :btree add_index "word_taggings", ["word_tag_id", "post_id"], name: "index_word_taggings_on_word_tag_id_and_post_id", using: :btree add_index "word_taggings", ["word_tag_id"], name: "index_word_taggings_on_word_tag_id", using: :btree
psql からindexが貼られているかの確認すると、post_id と word_tag_id にindexがなかった( ;´Д`)
antena_development=# \d index_word_taggings_on_post_id Did not find any relation named "index_word_taggings_on_post_id".
複合index使っていないっぽいので削除して、改めて単一indexをつけました。
class RemoveIndexWordTaggingsOnWordTagIdAndPostId < ActiveRecord::Migration def up remove_index :word_taggings, name: :index_word_taggings_on_word_tag_id_and_post_id # 複合 add_index "word_taggings", ["post_id"], name: "index_word_taggings_on_post_id", using: :btree add_index "word_taggings", ["word_tag_id"], name: "index_word_taggings_on_word_tag_id", using: :btree end def down add_index "word_taggings", ["word_tag_id", "post_id"], name: "index_word_taggings_on_word_tag_id_and_post_id" remove_index :word_taggings, name: :index_word_taggings_on_post_id remove_index :word_taggings, name: :index_word_taggings_on_word_tag_id end end
これでiowait減りそうです。
=> EXPLAIN for: SELECT "word_tags".* FROM "word_tags" INNER JOIN "word_taggings" ON "word_tags"."id" = "word_taggings"."word_tag_id" WHERE "word_taggings"."post_id" = $1 [["post_id", 243762]] QUERY PLAN ------------------------------------------------------------------------------------------------------------ Nested Loop (cost=0.72..455.48 rows=51 width=19) -> Index Scan using index_word_taggings_on_post_id on word_taggings (cost=0.43..63.16 rows=51 width=4) Index Cond: (post_id = 243762) -> Index Scan using word_tags_pkey on word_tags (cost=0.29..7.68 rows=1 width=19) Index Cond: (id = word_taggings.word_tag_id) (5 rows)
複合indexをつけると包括しているキーにはindex貼らない仕様?そもそも複合indexは不要?add_indexのmigrationで単一はindexが貼られなかった?かは不明。
とりあえずpostgresqlよくわからん( ;´Д`)