MISE À JOUR: 19/11/2014: Certaines personnes m’ont demandé à quel point créer un index sur event(channel,id) aide. Réponse : pas grand-chose.

Lors de l’implémentation d’IRCBrowse, j’ai découvert que le décalage intégré de Postgres n’était pas très rapide.

Voici les caractéristiques de mes données :

Et la taille:

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

Canal 1 est le plus grand:

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

Lorsque vous travaillez avec des données à cette échelle (grandes, mais pas « big data »), PostgreSQL les gère magnifiquement. Mais la vitesse de OFFSET / LIMIT n’est pas grande:

Je pense que cette analyse d’index est tout simplement trop chère. Notez que je commande par id qui a un index btree unique dessus. Découvrez la vitesse:

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

Vous pourriez penser que moins d’une seconde pour passer au crible 500 000 lignes d’une table de lignes de 28 millions est plutôt bien, mais je pense que ça craint. C’est aussi trompeur. Augmentons-le à 1 000 000 lignes (de 19,000,00):

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

C’est de pire en pire! C’est probablement linéaire dans ses mauvaises performances.

Cependant, il existe une solution. Utilisez une table d’index. Une table séparée qui contient des clés étrangères pointant vers cette table:

Maintenant, vous pouvez avoir un index de pagination pour le canal 1:

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

( J’ai utilisé idx = 1000 pour le canal 1, 2000 pour le canal 2, etc. pour que j’aie de l’espace pour d’autres index numériques pour le même canal.)

Maintenant, vous pouvez faire une requête très efficace pour les mêmes données que ci-dessus:

C’est un temps plus ou moins constant.

Et vous pouvez le voir en action sur le site. Le chargement et le rendu de la page prennent environ 30 ms si je l’exécute sur le serveur:

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

Bien sûr, l’envoi d’une demande dans votre navigateur prendra plus de temps en raison de la surcharge de connexion et des actifs, mais généralement le but était que ce soit très rapide. Le vieux ircbrowse.com (par un autre individu, qui m’a gentiment laissé le nom) était très lent en effet. Vous verriez la page charger les données de manière incrémentielle à partir de la base de données.

De toute façon, j’ai pensé que c’était une optimisation correcte et pratique spécifique à PostgreSQL en ce qui concerne la pagination. J’espère que ça valait la peine d’écrire.