AGGIORNAMENTO: 2014-11-19: Alcune persone mi hanno chiesto quanto sia utile creare un indice su event(channel,id). Risposta: non molto.

Durante l’implementazione di IRCBrowse ho scoperto che l’offset integrato di Postgres non è molto veloce.

Ecco le caratteristiche dei miei dati:

E le dimensioni:

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

Canale 1 è il più grande:

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

Quando lavori con dati su questa scala (grandi, ma non “big data”), PostgreSQL lo gestisce magnificamente. Ma la velocità di OFFSET / LIMIT non è eccezionale:

Penso che questa scansione dell’indice sia semplicemente troppo costosa. Si noti che sto ordinando per id che ha un indice btree univoco su di esso. Controlla la velocità:

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

Potresti pensare che meno di un secondo per setacciare 500.000 righe di una tabella di 28 milioni di righe sia abbastanza buono, ma penso che faccia schifo. È anche ingannevole. Aumentiamo a 1.000.000 righe (di 19,000,00):

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

Questo sta peggiorando sempre di più! Probabilmente è lineare nelle sue scarse prestazioni.

Tuttavia, c’è una soluzione. Utilizzare una tabella di indice. Una tabella separata che contiene chiavi esterne che puntano a questa tabella:

Ora puoi avere un indice di impaginazione per il canale 1:

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

(Ho usato idx = 1000 per il canale 1, 2000 per il canale 2,ecc. in modo da avere spazio per altri indici numerici per lo stesso canale.)

Ora puoi fare una query molto efficiente per gli stessi dati di cui sopra:

Questo è un tempo più o meno costante.

E puoi vederlo in azione sul sito. Richiede circa 30 ms per caricare e rendere la pagina se eseguo questo sul server:

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

Naturalmente, l’invio di una richiesta nel browser richiederà più tempo a causa del sovraccarico di connessione e delle risorse, ma in generale l’obiettivo era che fosse molto scattante. Il vecchio ircbrowse.com (da un altro individuo, che gentilmente mi ha lasciato avere il nome) era davvero molto lento. Vedresti la pagina che carica i dati in modo incrementale dal database.

Anyhoo, pensava che fosse un’ottimizzazione specifica di PostgreSQL decente e pratica per quanto riguarda l’impaginazione. Spero valesse la pena scriverlo.