Postgresql - Optimisation d'une requête

Schématiquement, j'ai une table des opérations, qui maintient pour chaque opération un identifiant unique (incrémental), et l'utilisateur qui a amorcé l'opération. Vu que chaque opération maintient l'état final du compte de l'utilisateur, je n'ai besoin de charger au démarrage que la dernière opération pour chaque utilisateur.

Je fais tourner le code suivant dans pgAdmin3.

Tout d'abord, je créé la table des opérations, et je la remplis avec des données aléatoires. J'ai donc 10 millions d'opérations, réparties sur 10 000 utilisateurs.

create table data(id int not null, utilisateur int not null);
insert into data select generate_series(1, 10000000), (random()*10000)::int;

La requête qui m'intéresse est simple:

select max(id), utilisateur from data group by utilisateur;

Arg, 6.3 secondes! Il y a forcément moyen de faire mieux. Un petit tour auprès du plan (F7) nous indique qu'effectivement, la requête balaye toute la table. Mais bien sûr, rajoutons un index! Si l'index est sur l'utilisateur, puis l'id, le plan devrait être trivial et bien plus efficace.

create index data_idx on data(utilisateur, id);
vacuum full analyze;

On relance la requête... Et aucune différence. Pourtant, on sent bien que l'index devrait aider! Mais Postgresql est historiquement un peu faible au niveau des performances des agrégats. Il va donc falloir forcer un peu le plan. Par exemple, en forçant la base de données à commencer à lire la liste des utilisateurs, puis en allant chercher l'identifiant le plus haut. Cela tombe bien, dans mon modèle, il y aura une liste séparée des utilisateurs. Essayons:

create table utilisateurs(utilisateur int primary key);
insert into utilisateurs select distinct utilisateur from data;

Et maintenant, la requête modifiée:

select utilisateur, (select max(id) from data d where d.utilisateur = u.utilisateur)
from utilisateurs u;

Yeah, 370 ms! Bien mieux, et parfaitement en phase avec mes besoins.



En regardant le plan, l'on se rend compte qu'il se passe exactement ce que nous voulions: pour chaque utilisateur, notre index est utilisé pour trouver le maximum. L'on notera que Postgresql a de fait transformé notre requête en ceci:

select 
   utilisateur, 
   (select id from data d where d.utilisateur = u.utilisateur order by id desc limit 1)
from utilisateurs u;

Dans les versions précédentes, le planificateur ne savait pas optimiser les max et min, et la meilleure manière d'être efficace était alors d'ordonner les données et de limiter la sortie. Dans 8.3 (ou peut-être 8.2?), le planificateur le fait de lui-même.

Voilà notre requête environ 20 fois plus rapide, cela valait le coup de s'y accrocher, non?