UPDATE: 2014-11-19: Einige Leute fragten mich, wie viel das Erstellen eines Index auf event(channel,id) hilft. Antwort: nicht viel.

Während der Implementierung von IRCBrowse entdeckte ich, dass der eingebaute Offset von Postgres nicht sehr schnell ist.

Hier sind die Eigenschaften meiner Daten:

Und die Größe:

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

Kanal 1 ist der größte:

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

Wenn Sie mit Daten in dieser Größenordnung arbeiten (groß, aber nicht „Big Data“), behandelt PostgreSQL sie hervorragend. Aber die Geschwindigkeit von OFFSET / LIMIT ist nicht großartig:

Ich finde diesen Index-Scan einfach zu teuer. Beachten Sie, dass ich nach ID bestelle, die einen eindeutigen btree-Index enthält. Überprüfen Sie die Geschwindigkeit:

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

Sie denken vielleicht, dass weniger als eine Sekunde, um 500.000 Zeilen einer 28-Millionen-Zeilen-Tabelle zu durchsuchen, ziemlich gut ist, aber ich denke, es ist scheiße. Es ist auch trügerisch. Erhöhen wir es auf 1.000.000 Zeilen (von 19,000,00):

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

Das wird immer schlimmer! Es ist wahrscheinlich linear in seiner schlechten Leistung.

Es gibt jedoch eine Lösung. Verwenden Sie eine Indextabelle. Eine separate Tabelle, die Fremdschlüssel enthält, die auf diese Tabelle verweisen:

Jetzt können Sie einen Paginierungsindex für den Kanal haben 1:

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

( Ich habe idx = 1000 für Kanal 1, 2000 für Kanal 2 usw. verwendet. damit hätte ich Platz für andere numerische Indizes für denselben Kanal.)

Jetzt können Sie eine sehr effiziente Abfrage für die gleichen Daten wie oben durchführen:

Dies ist mehr oder weniger konstante Zeit.

Und Sie können dies in Aktion auf der Website sehen. Das Laden und Rendern der Seite dauert ungefähr 30 ms, wenn ich dies auf dem Server ausführe:

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

Natürlich dauert das Senden einer Anfrage in Ihrem Browser aufgrund des Verbindungsaufwands und der Assets länger, aber im Allgemeinen war das Ziel, dass sie sehr bissig ist. Das alte ircbrowse.com (von einer anderen Person, die mir freundlicherweise den Namen gab) war in der Tat sehr langsam. Sie sehen, dass die Seite die Daten inkrementell aus der Datenbank lädt.

Anyhoo, dachte, das wäre eine anständige, praktische PostgreSQL-spezifische Optimierung in Bezug auf die Paginierung. Ich hoffe, es hat sich gelohnt zu schreiben.