opdatering: 2014-11-19: nogle mennesker spurgte mig, hvor meget oprettelse af et indeks på event(channel,id) hjælper. Svar: ikke meget.

under implementeringen af Ircbro opdagede jeg, at Postgres indbyggede forskydning ikke er meget hurtig.

her er kendetegnene for mine data:

og størrelsen:

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

kanal 1 er den største:

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

når du arbejder med data på denne skala (stor, men ikke “big data”), håndterer Postgraduate det smukt. Men hastigheden på OFFSET / LIMIT er ikke stor:

jeg synes, at denne indeksscanning simpelthen er for dyr. Bemærk, at jeg bestiller ved id, som har et unikt BTREE-indeks på det. Tjek hastigheden:

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

du tror måske, at mindre end et sekund at Sile gennem 500.000 rækker af et 28 millioner Rækkebord er ret godt, men jeg synes det stinker. Det er også vildledende. Lad os øge det til 1.000.000 rækker (af 19,000,00):

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

det bliver værre og værre! Det er sandsynligvis lineært i sin dårlige præstation.

der er dog en løsning. Brug en indekstabel. En separat tabel, der indeholder udenlandske nøgler, der peger på denne tabel:

nu Kan du have et paginationsindeks for kanal 1:

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

(jeg brugte idk=1000 til kanal 1, 2000 til kanal 2 osv. så jeg ville have plads til andre numeriske indekser for den samme kanal.)

nu kan du lave en meget effektiv forespørgsel for de samme data som ovenfor:

dette er mere eller mindre konstant tid.

og du kan se dette i aktion på hjemmesiden. 30ms at indlæse og gengive siden, hvis jeg kører dette på serveren:

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

selvfølgelig vil det tage længere tid at sende en anmodning på grund af forbindelsesomkostninger og aktiver, men generelt var målet, at det skulle være meget snappy. Den gamle ircbrowse.com (af en anden person, som venligt lod mig få navnet) var meget langsom faktisk. Du vil se siden indlæse dataene trinvist fra databasen.

Anyhoo, troede, det var en anstændig, praktisk Postgres-specifik optimering vedrørende pagination. Håber det var værd at skrive op.