Integer型のカラムにdefaultを設定するとレコードの検索時間はどうなる？

こんにちは。oda@エンジニア１年目です。

先日、マイグレーションファイルを使って、テーブルにInteger型のカラムを追加しました。

必要なレコードにのみデータを入れて、その他はnullにしていたところ、次のような指摘をいただきました。

「default: 0を設定しておくのが自然」

※今回は0とnullが同じ意味合いになるカラムでした。

defaultを設定するか、nullとするかによる違いを調べてみたところ、次のようなものがありそうでした。

レコードの検索にかかる時間
レコードの検索や集計の結果

今回は、以下に焦点を当てて調べてみました。

nullor0でないレコードを検索するときにかかる時間

それでは、実際にプログラムを動かしながら、確認していきたいと思います。

（以下、Rails 7.0.2.3、PostgreSQL14.2を使用しています。）

事前準備

まずは、Products(商品)テーブルを作成します。

bin/rails g model Products name:string

次に、サンプルデータとして、100,000件のレコードを作成します。

100000.times do |i|
  Product.create(name: "product#{i}")
end

このProductsテーブルに、stock(在庫)カラムを追加し、在庫のある商品は在庫数を、ない商品はnullor0を入れていきます。

defaultを設定しない場合

次のマイグレーションファイルを作成、実行します。

今回は1,000件ごとに、在庫を1入れるようにしてみました。

class AddStockToProducts < ActiveRecord::Migration[7.0]
  def change
    add_column :products, :stock, :integer
    products = Product.where('id % 1000 = ?', 0)
    products.update_all(stock: 1)
  end
end

default: 0を設定する場合

次のマイグレーションファイルを作成、実行します。

class AddStockToProducts < ActiveRecord::Migration[7.0]
  def change
    add_column :products, :stock, :integer, default: 0, null: false # ←ここを修正
    products = Product.where('id % 1000 = ?', 0)
    products.update_all(stock: 1)
  end
end

検証

それでは、rails consoleでそれぞれ確認していきます。

defaultを設定しない場合

以下のコマンドを実行します。

Product.where(stock: 1)

実行にかかった時間は次のとおりでした。

１回目：9.9ms　、２回目：11.8ms　、３回目：13.9ms

default: 0を設定する場合

同じく、以下のコマンドを実行します。

Product.where(stock: 1)

実行にかかった時間は次のとおりでした。

１回目：9.8ms　、２回目：12.6ms　、３回目：12.6ms

結果を整理

default	1回目	2回目	3回目
なし	9.9ms	11.8ms	13.9ms
あり	9.8ms	12.6ms	12.6ms

どちらの場合であっても、明確な差はありませんでした。

結果の考察

私はdefaultを設定した方が、時間が短くなると思っていました。

それは、nullが含まれていると検索に時間がかかるという話を聞いたことがあったからです。

今回、思っていたとおりにはならなかったので、その理由を考えてみました。

まず、そもそも、なぜ「nullが含まれていると検索に時間がかかる」のかということを調べてみたところ、IS NULLが含まれているとインデックスが利用できないので、遅くなるとの情報が見つかりました。

私は、上記の検証をした時に、インデックスの設定もIS NULLを含めた検索もしていませんでした。

この点について、再度検証してみます。

再検証

マイグレーションファイルにadd_index :products, :stockを加えて、IS NULLを使う場合と使わない場合を検証してみたところ、結果は、次のようになりました。

IS NULLを使った時にかかる時間（defaultを設定しない）

Product.where(stock: nil)

# 発行されるSQL
# SELECT "products".* FROM "products" WHERE "products"."stock" IS NULL

default	1回目	2回目	3回目
なし	36.3ms	36.3ms	39.7ms

IS NULLを使わなかった時にかかる時間（default: 0を設定する）

Product.where(stock: 0)

# 発行されるSQL
# SELECT "products".* FROM "products" WHERE "products"."stock" = $1  [["stock", 0]]

default	1回目	2回目	3回目
あり	38.0ms	35.7ms	38.5ms

再検証結果の考察

IS NULLを使っても、使わなくても結果に明確な差は現れませんでした。

そこで、今回使用したPostgreSQLのドキュメントを確認してみたところ、IS NULLを使ってもインデックス検索ができるとのことでした。

過去バージョンのドキュメントも見ることができたので、順番に見ていくとver8.2からver8.3に上がった時に、IS NULLを使ってもインデックス検索ができると変わっていました。

このため、結果に明確な差が現れなかったと考えています。

（参考）

ver8.2：https://www.postgresql.jp/document/8.2/html/indexes-types.html

ver8.3：https://www.postgresql.jp/document/8.3/html/indexes-types.html

まとめ

今回は、検証前に自分の考えていた結果とは、違う結果となりました。

それにより、より深く調べたり、違う角度から検証したりすることができました。

今後も自分の手を動かしながら、一つ、一つ学んでいきたいと思います！

最後まで、ご覧いただきありがとうございました！