UPDATE: 2014-11-19: sommige mensen vroegen me hoeveel het maken van een index op event(channel,id) helpt. Antwoord: niet veel.

tijdens de implementatie van IRCBrowse ontdekte ik dat de ingebouwde offset van Postgres niet erg snel is.

hier zijn de kenmerken van mijn gegevens:

en de grootte:

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

kanaal 1 is de grootste:

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

wanneer je met data op deze schaal werkt (groot, maar niet “big data”), gaat PostgreSQL er prachtig mee om. Maar de snelheid van OFFSET / LIMIT is niet groot:

ik vind deze indexscan gewoon te duur. Merk op dat ik ben bestellen door id die een unieke btree index op het heeft. Bekijk de snelheid:

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

je zou denken dat minder dan een seconde om te ziften door 500.000 rijen van een 28miljoen rij tafel is vrij goed, maar ik denk dat het zuigt. Het is ook misleidend. Laten we het verhogen tot 1.000.000 rijen (van 19,000,00):

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

dit wordt steeds erger. Het is waarschijnlijk lineair in zijn slechte prestaties.

er is echter een oplossing. Gebruik een indextabel. Een aparte tabel die vreemde sleutels bevat die naar deze tabel verwijzen:

nu kunt u een pagineringsindex voor kanaal hebben 1:

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

(ik gebruikte idx = 1000 voor kanaal 1, 2000 voor kanaal 2, enz. zodat ik ruimte zou hebben voor andere numerieke indexen voor hetzelfde kanaal.)

nu kunt u een zeer efficiënte query maken voor dezelfde gegevens als hierboven:

dit is min of meer constante tijd.

en u kunt dit zien in actie op de site. Duurt ongeveer 30ms te laden en renderen van de pagina als ik dit op de server uitvoeren:

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

het versturen van een verzoek in uw browser zal natuurlijk langer duren vanwege de overhead en activa van de verbinding, maar over het algemeen was het doel om het zeer snel te laten verlopen. De oude ircbrowse.com (door een ander individu, die vriendelijk gaf me de naam) was erg traag inderdaad. Je zou de pagina zien die de gegevens stapsgewijs uit de database laadt.

Anyhoo, vond dat een fatsoenlijke, praktische PostgreSQL-specifieke optimalisatie met betrekking tot paginering. Ik hoop dat het de moeite waard was om op te schrijven.