uppdatering: 2014-11-19: vissa människor frågade mig hur mycket att skapa ett index på event(channel,id) hjälper. Svar: inte mycket.

under implementeringen av IRCBrowse upptäckte jag att Postgres inbyggda offset inte är så snabbt.

här är egenskaperna hos mina data:

och storleken:

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

Kanal 1 är den största:

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

när du arbetar med data på denna skala (stor, men inte ”big data”), hanterar PostgreSQL det vackert. Men hastigheten på OFFSET / LIMIT är inte bra:

jag tycker att denna indexsökning helt enkelt är för dyr. Lägg märke till att jag beställer med id som har ett unikt btree-index på det. Kolla in 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 kanske tror att mindre än en sekund att sikta igenom 500 000 rader av ett 28 miljoner radbord är ganska bra, men jag tror att det suger. Det är också vilseledande. Låt oss öka den till 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

detta blir värre och värre! Det är förmodligen linjärt i sin dåliga prestanda.

det finns dock en lösning. Använd en indextabell. En separat tabell som innehåller främmande nycklar som pekar på denna tabell:

nu kan du ha ETT pagineringsindex för Kanal 1:

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

(jag använde idx=1000 för Kanal 1, 2000 för Kanal 2, etc. så att jag skulle ha plats för andra numeriska index för samma kanal.)

nu kan du göra en mycket effektiv Fråga för samma data som ovan:

detta är mer eller mindre konstant tid.

och du kan se detta i aktion på webbplatsen. Tar ungefär 30ms att ladda och göra sidan om jag kör det här på servern:

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

naturligtvis kommer det att ta längre tid att skicka en förfrågan i din webbläsare på grund av anslutningskostnaderna och tillgångarna, men i allmänhet var målet att det skulle vara väldigt snyggt. Den gamla ircbrowse.com (av en annan person, som vänligt lät mig få namnet) var väldigt långsam. Du skulle se sidan laddar data stegvis från databasen.

Anyhoo, trodde att det var en anständig, praktisk PostgreSQL-specifik optimering avseende paginering. Hoppas det var värt att skriva upp.