の高速ページネーション更新:2014-11-19:event(channel,id)のインデックスを作成するとどれだけ役立つかを尋ねられた人がいます。 答え:あまりない。IRCBrowseの実装中に、Postgresの組み込みオフセットがあまり高速ではないことがわかりました。ここに私のデータの特性があります:

とサイズ:

ircbrowse=> select count(*) from event; count---------- 28673917

チャンネル1は最大の:

ircbrowse=> select count(*) from event where channel = 1; count---------- 19340467

この規模のデータ(大規模ではあるが「ビッグデータ」ではない)で作業しているとき、PostgreSQLはそれを美しく処理します。 しかし、OFFSET/LIMITの速度は素晴らしいものではありません:

このインデックススキャンは単純に高価すぎると思います。 私はそれに一意のbtreeインデックスを持つidで注文していることに注意してください。 スピードをチェックする:

ircbrowse=> select * from event where channel = 1 order by id offset 1000 limit 30;Time: 0.721 msircbrowse=> select * from event where channel = 1 order by id offset 500000 limit 30;Time: 191.926 ms

あなたは28million行テーブルの500,000行を取捨選択するのに1秒もかからないと思うかもしれませんが、私はそれが吸うと思います。 それはまた欺瞞的です。 1,000,000行に増やしてみましょう(の19,000,00):

ircbrowse=> select * from event where channel = 1 order by id offset 1000000 limit 30;Time: 35022.464 ms

これはますます悪化しています! それはおそらくそのパフォーマンスの低下で線形です。

しかし、解決策があります。 索引表を使用します。 このテーブルを指す外部キーを含む別のテーブル:

今、あなたはチャネルのページネーションインデックスを持つことができます1:

ircbrowse=> select * from event_order_index where idx = 1000 limit 1; id | origin | idx----+--------+------ 1 | 1 | 1000

(チャンネル1にはidx=1000、チャンネル2には2000などを使用しました。 私は同じチャネルの他の数値インデックスのためのスペースを持っているでしょう。)

これで、上記と同じデータに対して非常に効率的なクエリを作成できます。

これは多かれ少なかれ一定の時間です。

そして、あなたはサイト上でこれを実際に見ることができます。 サーバー上でこれを実行すると、ページをロードしてレンダリングするのに約30msかかります:

$ time curl 'http://ircbrowse.net/browse/haskell?events_page=234'real0m0.031suser0m0.000ssys 0m0.004s

もちろん、ブラウザでリクエストを送信するには、接続のオーバーヘッドと資産のために時間がかかりますが、一般的には非常に厄介であることが目標でした。 古いircbrowse.com (親切に私に名前を持たせてくれた別の個人によって)確かに非常に遅かった。 データベースからデータを段階的にロードするページが表示されます。

Anyhooは、ページネーションに関するまともで実用的なPostgreSQL固有の最適化だと思っていました。 それは書く価値があったことを願っています。