Récemment, en discutant avec un collègue, - un collègue expérimenté qui a l’habitude de manipuler des bases de données géographiques -, je me suis rendu compte que certains des modes d'optimisation de requêtes que je jugeais assez simples ne l'étaient pas et n'étaient pas toujours connus.

Il existe des façons de penser à vos requêtes qui peuvent vous éviter de gros problèmes de performance, et qui peuvent optimiser une requête de façon fulgurante, tout en étant très simples à mettre en œuvre. Le tout étant de l'avoir vu au moins une fois en action.

Nous allons donc étudier en direct une optimisation SQL utile: il s'agit de regarder si vos fonctions appliquées dans les filtrages de la requête (là où il y a des `WHERE`/`AND`/`OR`) sont appliquées du bon côté de l'équation.

Notre exemple

Pour étudier cette optimisation, nous allons prendre comme exemple une requête plutôt simple, ne travaillant que sur une seule table.

Nous prenons une table d'utilisateurs, avec des identifiants, noms, prénoms, et une date de naissance.

La requête que nous avons besoin d'écrire doit résoudre ce problème:

Trouver les utilisateurs qui ont plus de 40 ans.

Vous avez la date de naissance, vous avez donc indirectement l'âge de la personne, mais à chaque seconde cet âge change, cette requête ne sera donc pas ultra simple, un simple `where age >= 40` n'est pas possible puisque nous n'avons pas de colonne `"age"`.

Démo SqlFiddle

Pour tester une optimisation de requête il est toujours utile de pouvoir créer rapidement une table, de pouvoir y mettre un gros volume de données (parce qu'avec 50 lignes dans une table vous ne verrez jamais la différence entre une mauvaise et une bonne requête), et de pouvoir y faire tourner nos requêtes.

Nous allons utiliser SQL Fiddle pour obtenir ces conditions et pouvoir les partager en ligne. Créer la table, y ajouter 100 000 utilisateurs, avec des noms aléatoires, et aussi surtout des dates de naissance aléatoires (et pour cet aspect d'aléatoire nous allons utiliser la géniale fonction `generate_series` de PostgreSQL).

Sur ce premier SQL Fiddle on voit une définition de table très simple :

CREATE TABLE "user"
(
  "id" bigserial PRIMARY KEY,
  "firstname" text NOT NULL,
  "lastname" text NOT NULL,
  "birthdate" timestamp with time zone
);

… suivi d'une requête de génération de contenu aléatoire beaucoup moins simple, mais ce n'est pas le sujet du jour. Cette requête de génération de contenus à permis de créer 100 000 lignes dans la table "user", qui partagent 5000 dates de naissances différentes, situées entre 1950 et juin 2018.

Cette requête est très complexe, je la rapelle ici pour information, et pour ceux qui veulent explorer la génération de contenus. Ne cherchez pas forcément à la comprendre, elle n'est pas liée à la problématique étudiée.

WITH serie5000dates as (
 SELECT
     row_number() OVER () as rownum,
     generator2 as birthdate
    FROM generate_series('1950-01-01 00:00+00'::timestamp,
                         CURRENT_DATE,
                         '5 days') as generator2
    LIMIT 5000
)
INSERT INTO "user" (
 "id",
 "firstname",
 "lastname",
 "birthdate"
)
SELECT
  generator as id,
  'first' || generator as firstname,
  'last'|| generator as lastname,
  serie5000dates.birthdate
  FROM (
    SELECT
      generator,
      (
        select  (random() * 5000)::int + (generator*0)::int as joinid
      )
      FROM generate_series(1,100000) as generator
 ) mainsubquery
 INNER JOIN serie5000dates ON serie5000dates.rownum = mainsubquery.joinid
;

• http://sqlfiddle.com/#!17/e9c4a/1 : select count(*) from "user" => 99990 environ, cela varie un peu parce que le générateur n'est pas très précis
• http://sqlfiddle.com/#!17/e9c4a/2 : comptage d'utilisateurs par date de naissance

Notre requête à optimiser

Nous voulons extraire, ou compter, les users qui ont plus de 40 ans. Nous allons plutôt compter qu'extraire, parce que le rapatriement de 100 000 lignes serait sans aucun doute trop long.

De base, devant cette problématique d'âge, une première recherche dans les fonctions PostgreSQL renvoie une fonction qui semble très utile et qui se nomme `age()`. On peut la tester avec

http://sqlfiddle.com/#!17/e9c4a/3

SELECT age("birthdate")
FROM "user"
LIMIT 10;

Le résultat est un intervalle de temps. On obtient donc rapidement notre résultat escompté avec :

http://sqlfiddle.com/#!17/e9c4a/4

-- version 1.1
SELECT count(*)
FROM "user"
WHERE extract(year from age(current_date,"birthdate")) >= 40;

Avec un résultat de `43112` utilisateurs, trouvés en 95ms (environ, plus ou moins 10ms suivant la charge du serveur). Si vous regardez le plan d’exécution (vous l'avez en lien dans SQL Fiddle dans le bandeau vert qui donne le temps d'exécution) vous pouvez y voir un `Seq Scan` qui signifie que toute la table a été parcourue séquentiellement.

On peut imaginer que PostgreSQL, avec ses petits bras et ses petites papattes, est allé regarder chaque ligne de la table, et a lancé la fonction `(date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, birthdate))` sur chacune des lignes.

Première Optimisation

On peut tout d'abord essayer d'appliquer moins d'appels de fonctions pour chaque ligne, première optimisation:

http://sqlfiddle.com/#!17/e9c4a/7

-- version 1.2
SELECT count(*)
FROM "user"
WHERE age("birthdate") >= '40 years'::interval

On obtient les lignes un peu plus rapidement (environ 80ms toujours plus ou moins 10ms). Dans le plan d’exécution on voit que les fonctions à appliquer à chaque ligne sont un peu plus simples: `(age((('now'::cstring)::date)::timestamp with time zone, birthdate)`

Meilleure Optimisation ?

http://sqlfiddle.com/#!17/e9c4a/8

-- version 2.0
SELECT count(*)
FROM "user"
WHERE "birthdate" < current_date-INTERVAL '40 years';

Ici la fonction appliquée à chaque ligne est `(birthdate < (('now'::cstring)::date - '40 years'::interval))`.

Il y a une très très grosse différence par rapport aux requêtes précédents. On compare le champ `"birthdate"` à une constante. PostgreSQL peut calculer cette constante (('now'::cstring)::date - '40 years'::interval).

Si je lance `SELECT (('now')::date - '40 years'::interval)` aujourd'hui j’obtiens `1979-08-29T00:00:00Z`, et maintenant PostgreSQL, toujours avec ses petites jambes et ses petits bras, il peut parcourir toute la table à la recherche de dates situées avant celle-ci.

Sur le SqlFiddle, on observe un temps moyen 65 ms (plus ou moins 10ms). Le temps de réponse dépend fortement de l'utilisation du service, mais comparez les différentes requêtes en les faisant tourner au même moment, vous verrez que cette version est déjà plus rapide en l'état.

• La première différence est qu'il n'a pas besoin d'appliquer de fonctions de transformations au dates stockées en table, dans la version précédente, il fallait appliquer `age()` à chaque date rencontrée. Ici l'optimisation est réelle, on gagne du temps à ne pas appliquer les fonctions à chaque ligne rencontrée. Il n'y a que 100 000 lignes, et qu'une seule fonction à appliquer si vous utilisez la version 1.2 de la requête. Donc l'erreur conceptuelle reste masquée par les capacités de calcul du serveur. Mais cet effet va s'aggraver quand le nombre de lignes et de traitements appliqués augmentera.
• La deuxième différence est qu'il est maintenant possible d'indexer la table pour avoir des améliorations de performance. Prenez un dictionnaire français, c'est un index, si je vous demande de m'extraire tous les mots situés après le mot "Polonium" c'est assez facile à faire. De même si un index existe en base sur cette colonne de date de naissance, retrouver les gens qui sont nés avant cette date devrait être très facile pour la base de données.

http://sqlfiddle.com/#!17/2a7d8a

CREATE INDEX birthdate_idx ON "user"("birthdate");

On redemande maintenant à la base de nous compter les gens de plus de 40 ans, tout d'abord avec la requête en version 1.2 :

• http://sqlfiddle.com/#!17/2a7d8a/1 : toujours entre 95 ms avec un `Seq Scan`, l'index n'est pas utilisé.

On obtient en revanche une énorme différence avec la requête optimisée qui utilise les fonctions uniquement au départ pour calculer la constante. Les fonctions ne sont pas appliquées à chaque ligne de la table:

• http://sqlfiddle.com/#!17/2a7d8a/2: 20ms avec dans le plan d'exécution `Index Only Scan using birthdate_idx on "user"`

Avec les version 1.x on empêche toute optimisation, il faudra donc toujours aller regarder chaque ligne et y faire le travail (appliquer les fonctions de transformation). La même logique s'appliquerait si, au lieu de vous demander de rechercher les mots situés après le mot "Polonium", je vous avait demandé de m'extraire du dictionnaire tous les mots qui possèdent la sous chaîne de caractères 'oloni'. Vous êtes obligé de faire ce travail pour chaque mot et de parcourir l'ensemble des mots.

Pour effectuer cette tâche le fait que les mots soient triés par ordre alphabétique dans le dictionnaire ne vous est d'aucune utilité. Si je vous donne une liste non ordonnée de l'ensemble des mots du dictionnaire (l'équivalent des données brutes dans la base, sans index), vous irez aussi vite qu'avec la version ordonnée, vous devrez de toute façon examiner l'ensemble des mots pour y rechercher cette chaîne de lettres.

Quand vous demandez à la base de données d'appliquer des traitements complexes sur les données (dans les conditions, dans les regroupements, dans les tris), réfléchissez bien: Est-ce qu'il y a beaucoup de lignes à examiner ? Est-ce qu'une indexation est possible ? Remarquez que parfois on peut indexer sur le résultat d'une fonction, ou sur un sous ensemble de la table, mais dans cet exemple vous ne pouvez pas indexer sur l'âge d'une personne car c'est une donnée qui évolue au fur et à mesure que le temps passe. Est-ce que je ne suis pas en train de forcer le moteur de base de données à lire chaque ligne et à lancer des calculs pour chacune de ces lignes?

Oui, mais on ne fait que compter

En utilisant uniquement le `count(*)` dans nos requêtes d'exemples on augmente l'effet de l'optimisation car ce comptage peut se contenter d'utiliser l'index, il n'y a même pas besoin de lire les vraies lignes. On augmente donc la différence de performance entre les requêtes qui peuvent se contenter de l'index et celles qui en sont incapables (en un sens tant mieux pour celles qui peuvent utiliser l'index).

Regardons ce qui se passerait si j'avais besoin de remonter les lignes de résultats.

Premier élément, les volumes sont importants, donc une application qui remonte les résultats le ferait sans doute en paginant la remontée d'information. Et quand on pagine (avec des LIMIT et OFFSET par exemple --même si d'autres techniques existent) il faut toujours penser à … ordonner. Nos requêtes doivent donc être modifiées pour prendre en compte un ordre de retour. On pourrait par exemple trier par identifiant. Si les extractions de données liées à l'âge sont courantes, on peut imaginer que l'index sur les dates de naissance existe bien dans notre application. Dès lors on peut directement se servir de cet index pour trier par date de naissance.

http://sqlfiddle.com/#!17/2a7d8a/7

-- version 1.3
SELECT *
FROM "user"
WHERE age("birthdate") >= '40 years'::interval
ORDER BY "birthdate" ASC
LIMIT 100;

http://sqlfiddle.com/#!17/2a7d8a/6

-- version 2.1
SELECT *
FROM "user"
WHERE "birthdate" < current_date-INTERVAL '40 years'
ORDER BY "birthdate" ASC
LIMIT 100;

Le résultat est normalement quasi identique sur les deux requêtes, et beaucoup plus rapide que le comptage complet (moins de 10ms) car le planificateur de requêtes choisit dans les deux cas d'utiliser l'index sur la date de naissance. La différence repose uniquement sur le coût de notre fonction `age()`, qui en l’occurrence n'est pas très élevé (certaines fonctions ou enchaînements de fonctions pourraient l'être beaucoup plus). De plus le fait de limiter nos résultats à 100 lignes limite mécaniquement le nombre d'appels de fonctions effectué (moins de volume).

Si on y réfléchit, en partant d'un index basé sur la date de naissance, qui est par défaut `ASC`, les premiers enregistrements seront toujours des enregistrements qui valident la condition (on démarre par les plus vieux). Essayons avec un ordre de tri inversé, PostgreSQL utilisera un `Index Scan Backward using birthdate_idx`. L'index reste donc utilisé dans les deux cas. Toutefois on peut tout de suite prévoir que toutes les premières dates testées par la version 1.3 de la requête seront fausses (il tombera par d"éfaut sur les plusjeunes), et il faudra avancer très loin dans l'index pour finir par tomber sur des conditions vérifiant la condition d'âge:

Voici donc les deux mêmes requêtes mais avec `ORDER BY "birthdate" DESC` :

• http://sqlfiddle.com/#!17/2a7d8a/16 : v1.3 : 78ms
• http://sqlfiddle.com/#!17/2a7d8a/17 : v2.1 : 8ms

Il y a effondrement de performance pour la requête qui doit effectuer le travail à chaque ligne.

Au final on voit que l'effet de ce "conseil général" sur les invariants n'est pas toujours facile à identifier, mais vous évitera toujours des déconvenues difficiles à prévoir.

Une autre variation sur le même thème

Imaginez maintenant que la problématique ne soit plus de retrouver les utilisateurs de plus de 40 ans mais tous les utilisateurs ayant 10, 20, 30, 40, 50, 60, 70, 80, 90 ans cette année (des anniversaires tous ronds).

Si vous vous sentez en forme pour travailler un peu avec les fonctions de dates vous pouvez essayer sans regarder la soluce pendant au moins une minute (par exemple en tapant vos requêtes directement dans SqlFiddle).

http://sqlfiddle.com/#!17/2a7d8a/3

-- version 1.0
SELECT count(*)
FROM "user"
WHERE extract(
        year from
        -- age at last day of this year
        age(
            -- last day of current year
            date_trunc('YEAR', current_date)+ INTERVAL '1 YEAR - 1 day',
            "birthdate"
        )
    ) IN (10,20,30,40,50,60,70,80,90);

8658 utilisateurs retrouvés en 167 ms.

La requête n'était pas hyper simple, il faut bien penser à calculer l'âge de l'utilisateur à la fin de l'année et pas à la date du jour (dans l'énoncé c'est bien marqué cette année).

Essayons maintenant de repenser la requête en essayant de trouver des constantes, des dates pivot. Je ne fais pas d'effort pour simplifier mes fonctions, il est fort possible qu'il existe de meilleures façons de l'écrire, l'important ici est que je fais mes calculs de constantes du bon côté: je n'applique pas mes fonctions sur la date de naissance mais sur le calcul des dates pivots (`date_trunc('YEAR', current_date)` donne le premier jour de l'année courante et `date_trunc('YEAR', current_date) + INTERVAL '1 YEAR - 1 day'` le dernier jour de l'année courante):

http://sqlfiddle.com/#!17/2a7d8a/4

-- version 2.0
-- on calcule les dates pivots et on recherche les gens qui sont dans les plages
SELECT count(*)
FROM "user"
WHERE "birthdate" BETWEEN
    (date_trunc('YEAR', current_date) - INTERVAL '10 years')
  AND
    (date_trunc('YEAR', current_date) + INTERVAL '1 YEAR - 1 day' - INTERVAL '10 years')
OR "birthdate" BETWEEN
    (date_trunc('YEAR', current_date) - INTERVAL '20 years')
  AND
    (date_trunc('YEAR', current_date) + INTERVAL '1 YEAR - 1 day' - INTERVAL '20 years')
OR "birthdate" BETWEEN
    (date_trunc('YEAR', current_date) - INTERVAL '30 years')
  AND
    (date_trunc('YEAR', current_date) + INTERVAL '1 YEAR - 1 day' - INTERVAL '30 years')
OR "birthdate" BETWEEN
    (date_trunc('YEAR', current_date) - INTERVAL '40 years')
  AND
    (date_trunc('YEAR', current_date) + INTERVAL '1 YEAR - 1 day' - INTERVAL '40 years')
OR "birthdate" BETWEEN
    (date_trunc('YEAR', current_date) - INTERVAL '50 years')
  AND
    (date_trunc('YEAR', current_date) + INTERVAL '1 YEAR - 1 day' - INTERVAL '50 years')
OR "birthdate" BETWEEN
    (date_trunc('YEAR', current_date) - INTERVAL '60 years')
  AND
    (date_trunc('YEAR', current_date) + INTERVAL '1 YEAR - 1 day' - INTERVAL '60 years')
OR "birthdate" BETWEEN
    (date_trunc('YEAR', current_date) - INTERVAL '70 years')
  AND
    (date_trunc('YEAR', current_date) + INTERVAL '1 YEAR - 1 day' - INTERVAL '70 years')
OR "birthdate" BETWEEN
    (date_trunc('YEAR', current_date) - INTERVAL '80 years')
  AND
    (date_trunc('YEAR', current_date) + INTERVAL '1 YEAR - 1 day' - INTERVAL '80 years')
OR "birthdate" BETWEEN
    (date_trunc('YEAR', current_date) - INTERVAL '90 years')
  AND
    (date_trunc('YEAR', current_date) + INTERVAL '1 YEAR - 1 day' - INTERVAL '90 years')

8658 utilisateurs retrouvés en 25 ms. C'est moins joli mais ça tourne vraiment mieux.

La chose à retenir est qu'il faut préférer appliquer les calculs complexes pour trouver des constantes plutôt que de les appliquer à chacune des lignes.

Principe que vous pouvez retrouver aussi dans l'application des mots clefs `VOLATILE`/`STABLE`/`IMMUTABLE` sur les éventuelles fonctions que vous créez vous même en base, mais cela aussi est un autre sujet.

PS: N'optimisez pas prématurément. Si vous n'avez pas de gros volumes, pas de requêtes complexes, attendez un peu avant de perdre quelques heures à optimiser une requête.

PPS: Dans la vraie vie vous rencontrerez des gens qui rapatrient l'intégralité de la base dans leur application Python ou PHP, puis qui font le filtrage dans l'application, explosion de mémoire consommée, temps de calcul très très lent, quand je dis de ne pas optimiser tout de suite je ne parle pas de coder comme un cochon en oubliant d'utiliser le SQL.

PPPS: En revanche il est possible d'utiliser votre langage applicatif pour précalculer les constantes et les insérer dans la requête (par exemple premier et dernier jour de l'année), c'est d'ailleurs une extension du principe général de ne pas faire de calcul à chaque ligne en scan séquentiel, mais de faire des requêtes qui comparent des champs potentiellement indexés à des constantes.