aktualizacja: 2014-11-19: niektórzy ludzie pytali mnie, jak bardzo pomaga tworzenie indeksu na event(channel,id). Odpowiedź: niewiele.

podczas implementacji IRCBrowse odkryłem, że wbudowany offset Postgresa nie jest zbyt szybki.

oto cechy moich danych:

i rozmiar:

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

Kanał 1 jest największym:

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

kiedy pracujesz z danymi w tej skali (dużymi, ale nie „dużymi danymi”), PostgreSQL radzi sobie z nimi pięknie. Ale prędkość OFFSET / LIMIT nie jest wielka:

myślę, że ten skan indeksu jest po prostu za drogi. Zauważ, że zamawiam według id, który ma unikalny indeks btree. Sprawdź prędkość:

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

możesz pomyśleć, że mniej niż sekunda przesiać przez 500 000 wierszy tabeli 28 milionów wierszy jest całkiem dobra, ale myślę, że jest do bani. To również zwodnicze. Zwiększmy go do 1 000 000 wierszy (z 19,000,00):

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

jest coraz gorzej! Prawdopodobnie jest liniowy w słabej wydajności.

Jednak jest rozwiązanie. Użyj tabeli indeksów. Osobna tabela zawierająca klucze obce wskazujące na tę tabelę:

teraz możesz mieć indeks paginacji dla kanału 1:

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

(użyłem idx=1000 dla kanału 1, 2000 dla kanału 2 itd. więc miałbym miejsce na inne indeksy numeryczne dla tego samego kanału.)

teraz możesz zrobić bardzo wydajne Zapytanie o te same dane, co powyżej:

jest to mniej więcej stały czas.

i można to zobaczyć w akcji na stronie. Ładowanie i renderowanie strony zajmuje około 30ms, jeśli uruchomię to na serwerze:

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

oczywiście wysłanie żądania w przeglądarce zajmie więcej czasu ze względu na koszty połączenia i zasoby, ale ogólnie celem było, aby było to bardzo szybkie. Stare ircbrowse.com (przez inną osobę, która uprzejmie dała mi to imię) było naprawdę bardzo powolne. Zobaczysz stronę ładującą dane stopniowo z bazy danych.

anyhoo, myślałem, że to przyzwoita, praktyczna optymalizacja specyficzna dla Postgresqla pod kątem paginacji. Mam nadzieję, że warto było to napisać.