データベースのパフォーマンスを低下させる一般的なRailsイディオム

インデックスページ。ほとんどすべてのアプリには少なくとも1つあります。データベースから最新の20レコードをプルし、それらを表示します。もっと簡単なことは何ですか？

レコードをロードするコードは、次のようになります。

@faults = Fault.order(created_at: :desc)

そのためのSQLは次のようになります：

select * from faults order by created_at desc;

それでは分析しましょう：

# explain (analyze, format yaml) select * from faults order by created_at desc;
              QUERY PLAN
--------------------------------------
 - Plan:                             +
     Node Type: "Sort"               +
     Startup Cost: 39162.46          +
     Total Cost: 39218.12            +
     Plan Rows: 22265                +
     Plan Width: 1855                +
     Actual Startup Time: 75.928     +
     Actual Total Time: 86.460       +
     Actual Rows: 22265              +
     Actual Loops: 1                 +
     Sort Key:                       +
       - "created_at"                +
     Sort Method: "external merge"   +
     Sort Space Used: 10752          +
     Sort Space Type: "Disk"         +
     Plans:                          +
       - Node Type: "Seq Scan"       +
         Parent Relationship: "Outer"+
         Relation Name: "faults"     +
         Alias: "faults"             +
         Startup Cost: 0.00          +
         Total Cost: 1784.65         +
         Plan Rows: 22265            +
         Plan Width: 1855            +
         Actual Startup Time: 0.004  +
         Actual Total Time: 4.653    +
         Actual Rows: 22265          +
         Actual Loops: 1             +
   Triggers:                         +
   Total Runtime: 102.288
(1 row)

ここでは、このクエリを実行するたびに、DBが22,265行すべてを並べ替えていることがわかります。ブエノはありません！

デフォルトでは、SQLのすべての「orderby」句により、レコードセットがリアルタイムですぐに並べ替えられます。キャッシュはありません。あなたを救う魔法はありません。

解決策は、インデックスを使用することです。このような単純なケースでは、created_at列にソートされたインデックスを追加すると、クエリがかなり高速化されます。

Railsの移行では、次のように記述できます：

class AddIndexToFaultCreatedAt < ActiveRecord::Migration
  def change
    add_index(:faults, :created_at)
  end
end

次のSQLを実行します：

CREATE INDEX index_faults_on_created_at ON faults USING btree (created_at);

最後に、(created_at) ソート順を指定します。デフォルトでは昇順です。

ここで、並べ替えクエリを再実行すると、並べ替え手順が含まれていないことがわかります。事前に並べ替えられたデータをインデックスから読み取るだけです。

# explain (analyze, format yaml) select * from faults order by created_at desc;
                  QUERY PLAN
----------------------------------------------
 - Plan:                                     +
     Node Type: "Index Scan"                 +
     Scan Direction: "Backward"              +
     Index Name: "index_faults_on_created_at"+
     Relation Name: "faults"                 +
     Alias: "faults"                         +
     Startup Cost: 0.29                      +
     Total Cost: 5288.04                     +
     Plan Rows: 22265                        +
     Plan Width: 1855                        +
     Actual Startup Time: 0.023              +
     Actual Total Time: 8.778                +
     Actual Rows: 22265                      +
     Actual Loops: 1                         +
   Triggers:                                 +
   Total Runtime: 10.080
(1 row)

複数の列で並べ替える場合は、複数の列で並べ替えられるインデックスも作成する必要があります。 Railsの移行では次のようになります。

add_index(:faults, [:priority, :created_at], order: {priority: :asc, created_at: :desc)

より複雑なクエリを実行し始めるときは、explainを実行することをお勧めします。 。早くそして頻繁にそれをしなさい。クエリを簡単に変更したため、postgresがインデックスを使用して並べ替えることができなくなっている場合があります。

Fault.limit(10).offset(100)

select * from faults limit 10 offset 100;

# explain (analyze, format yaml) select * from faults limit 10 offset 100;
              QUERY PLAN
--------------------------------------
 - Plan:                             +
     Node Type: "Limit"              +
     ...
     Plans:                          +
       - Node Type: "Seq Scan"       +
         Actual Rows: 110            +
         ...

# explain (analyze, format yaml) select * from faults limit 10 offset 10000;
              QUERY PLAN
--------------------------------------
 - Plan:                             +
     Node Type: "Limit"              +
     ...
     Plans:                          +
       - Node Type: "Seq Scan"       +
         Actual Rows: 10010          +
         ...

# You could use a date range
Fault.where("created_at > ? and created_at < ?", 100.days.ago, 101.days.ago)

# ...or even an id range
Fault.where("id > ? and id < ?", 100, 200)