OPPDATERING: 2014-11-19: Noen spurte meg hvor mye å lage en indeks på event(channel,id) hjelper. Svar: ikke mye.

under implementeringen Av IRCBrowse oppdaget jeg At Postgres innebygde offset ikke er veldig fort.

her er egenskapene til dataene mine:

og størrelsen:

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

Kanal 1 er størst:

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

Når Du jobber med data på denne skalaen (stor, men ikke «big data»), Håndterer PostgreSQL det vakkert. Men hastigheten på OFFSET / LIMIT er ikke stor:

jeg tror at denne indeksskanningen bare er for dyr. Legg merke til at jeg bestiller med id som har en unik btree-indeks på den. Sjekk ut hastigheten:

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 kanskje mindre enn et sekund å sile gjennom 500.000 rader av et 28million radbord er ganske bra, men jeg tror det suger. Det er også villedende. La oss øke den til 1.000.000 rader (av 19,000,00):

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

Dette blir verre og verre! Det er sannsynligvis lineært i sin dårlige ytelse.

det er imidlertid en løsning. Bruk en indekstabell. En egen tabell som inneholder utenlandske nøkler som peker til denne tabellen:

Nå kan du ha en paginering indeks for kanal 1:

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

(jeg brukte idx=1000 for kanal 1, 2000 for kanal 2, etc. slik at jeg ville ha plass til andre numeriske indekser for samme kanal.)

Nå kan du lage en veldig effektiv spørring for de samme dataene som ovenfor:

Dette er mer eller mindre konstant tid.

og du kan se dette i aksjon på nettstedet. Tar ca 30ms å laste og gjengi siden hvis jeg kjører dette på serveren:

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

selvfølgelig vil det ta lengre tid å sende en forespørsel i nettleseren din på grunn av tilkoblingsoverhead og eiendeler, men generelt var målet at det skulle være veldig snappy. Den gamle ircbrowse.com (av en annen person, som ber la meg få navnet) var veldig sakte faktisk. Du vil se at siden laster dataene trinnvis fra databasen.

Anyhoo, trodde det var en anstendig, praktisk PostgreSQL-spesifikk optimalisering angående paginering. Håper det var verdt å skrive.