UPDATE: 2014-11-19: algumas pessoas me perguntaram quanto a criação de um índice em event(channel,id) ajuda. Resposta: não muito.

durante a implementação do IRCBrowse, descobri que o deslocamento interno do Postgres não é muito rápido.

Aqui estão as características dos meus dados:

E o tamanho:

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

o Canal 1 é o maior:

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

Quando você está trabalhando com dados sobre esta escala (grandes, mas não “big data”), o PostgreSQL lida com isso muito bem. Mas a velocidade de OFFSET/LIMIT não é grande:

acho que essa varredura de índice é simplesmente muito cara. Observe que estou pedindo por id que tem um índice btree exclusivo nele. Confira a velocidade:

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

você pode pensar que menos de um segundo para peneirar 500.000 linhas de uma tabela de 28 milhões de linhas é muito bom, mas acho que é uma merda. Também é enganoso. Vamos aumentá – lo para 1.000.000 linhas (de 19,000,00):

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

isso está ficando cada vez pior! Provavelmente é linear em seu baixo desempenho.

no entanto, há uma solução. Use uma tabela de índice. Uma tabela separada que contém chaves estrangeiras apontando para esta tabela:

Agora você pode ter um índice de paginação do canal 1:

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

(eu usei idx=1000 para o canal 1, de 2000, para o canal 2, etc. para que eu tivesse espaço para outros índices numéricos para o mesmo canal.)

Agora você pode fazer uma consulta muito eficiente para os mesmos dados acima:

este é mais ou menos tempo constante.

e você pode ver isso em ação no site. Leva cerca de 30ms para carregar e renderizar a página se eu executar isso no servidor:

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

claro, o envio de uma solicitação em seu navegador levará mais tempo devido à sobrecarga e aos ativos da conexão, mas geralmente o objetivo era que fosse muito rápido. O velho ircbrowse.com (por outro indivíduo, que gentilmente me deixou ter o nome) foi muito lento de fato. Você veria a página carregando os dados incrementalmente do banco de dados.

Anyhoo, achou que era uma otimização decente e prática específica do PostgreSQL em relação à paginação. Espero que valha a pena escrever.