J’ai trouvé une question intéressante sur Twitter, récemment. L’utilisation de FILTER en SQL (PostgreSQL, en particulier), ou est-ce juste du sucre de syntaxe pour un CASE expression dans une fonction d’agrégation ?
Pour rappel rapide, FILTER est une extension SQL standard géniale pour filtrer les valeurs avant de les agréger en SQL. Ceci est très utile lors de l’agrégation de plusieurs choses dans une seule requête.
Ces deux sont les mêmes :
SELECT
fa.actor_id,
-- These:
SUM(length) FILTER (WHERE rating = 'R'),
SUM(length) FILTER (WHERE rating = 'PG'),
-- Are the same as these:
SUM(CASE WHEN rating = 'R' THEN length END),
SUM(CASE WHEN rating = 'PG' THEN length END)
FROM film_actor AS fa
LEFT JOIN film AS f
ON f.film_id = fa.film_id
GROUP BY fa.actor_id
Depuis jOOQ 3.17, ces dialectes SQL sont connus pour prendre en charge FILTER nativement :
- CafardDB
- Oiseau de feu
- H2
- HSQLDB
- PostgreSQLName
- SQLiteName
- YugabyteDB
Cela devrait-il avoir de l’importance ?
Mais revenons à la question. Est-ce vraiment important en termes de performances ? Devrait-il? De toute évidence, cela ne devrait pas avoir d’importance. On peut prouver que les deux types d’expressions de fonction d’agrégat signifient exactement la même chose. Et en fait, c’est ce que fait jOOQ si vous utilisez FILTER sur tout autre dialecte SQL. Mettez la requête ci-dessus dans notre outil de traduction SQL, traduisez vers Oracle, par exemple, et vous obtiendrez :
SELECT
fa.actor_id,
sum(CASE WHEN rating = 'R' THEN length END),
sum(CASE WHEN rating = 'PG' THEN length END),
sum(CASE WHEN rating = 'R' THEN length END),
sum(CASE WHEN rating = 'PG' THEN length END)
FROM film_actor fa
LEFT JOIN film f
ON f.film_id = fa.film_id
GROUP BY fa.actor_id
L’autre manière devrait également être possible dans un optimiseur.
Est-ce que ça importe?
Mais est-ce que cela se fait ? Essayons de comparer les 2 requêtes suivantes sur PostgreSQL avec la base de données sakila :
Requête 1:
SELECT
fa.actor_id,
SUM(length) FILTER (WHERE rating = 'R'),
SUM(length) FILTER (WHERE rating = 'PG')
FROM film_actor AS fa
LEFT JOIN film AS f
ON f.film_id = fa.film_id
GROUP BY fa.actor_id
Requête 2:
SELECT
fa.actor_id,
SUM(CASE WHEN rating = 'R' THEN length END),
SUM(CASE WHEN rating = 'PG' THEN length END)
FROM film_actor AS fa
LEFT JOIN film AS f
ON f.film_id = fa.film_id
GROUP BY fa.actor_id
J’utiliserai cette technique de référence et publierai le code de référence à la fin de cet article de blog. Les résultats de l’exécution de chaque requête 500x sont clairs (moins de temps, c’est mieux) :
Run 1, Statement 1: 00:00:00.786621
Run 1, Statement 2: 00:00:00.839966
Run 2, Statement 1: 00:00:00.775477
Run 2, Statement 2: 00:00:00.829746
Run 3, Statement 1: 00:00:00.774942
Run 3, Statement 2: 00:00:00.834745
Run 4, Statement 1: 00:00:00.776973
Run 4, Statement 2: 00:00:00.836655
Run 5, Statement 1: 00:00:00.775871
Run 5, Statement 2: 00:00:00.845209
Il y a une pénalité de performance constante de 8 % pour l’utilisation du CASE syntaxe, par rapport à la FILTER syntaxe sur ma machine, exécutant PostgreSQL 15 dans docker. La différence réelle dans une requête non comparative peut ne pas être aussi impressionnante, voire plus impressionnante, selon le matériel et les ensembles de données. Mais clairement, une chose semble être un peu mieux dans ce cas que l’autre.
Étant donné que ces types de syntaxes sont généralement utilisés dans un contexte de création de rapports, les différences peuvent certainement avoir de l’importance.
Ajout d’un prédicat auxiliaire
Vous pourriez penser qu’il y a un potentiel d’optimisation supplémentaire, si nous faisons les prédicats sur le RATING colonne redondante, comme ceci :
Requête 1 :
SELECT
fa.actor_id,
SUM(length) FILTER (WHERE rating = 'R'),
SUM(length) FILTER (WHERE rating = 'PG')
FROM film_actor AS fa
LEFT JOIN film AS f
ON f.film_id = fa.film_id
AND rating IN ('R', 'PG') -- Redundant predicate here
GROUP BY fa.actor_id
Requête 2 :
SELECT
fa.actor_id,
SUM(CASE WHEN rating = 'R' THEN length END),
SUM(CASE WHEN rating = 'PG' THEN length END)
FROM film_actor AS fa
LEFT JOIN film AS f
ON f.film_id = fa.film_id
AND rating IN ('R', 'PG')
GROUP BY fa.actor_id
Notez qu’il doit être placé dans le LEFT JOINc’est ON clause, afin de ne pas altérer les résultats. Il ne peut pas être placé dans la requête WHERE clause. Une explication de cette différence est ici.
Quel sera le rendement de l’indice de référence maintenant ?
Run 1, Statement 1: 00:00:00.701943
Run 1, Statement 2: 00:00:00.747103
Run 2, Statement 1: 00:00:00.69377
Run 2, Statement 2: 00:00:00.746252
Run 3, Statement 1: 00:00:00.684777
Run 3, Statement 2: 00:00:00.745419
Run 4, Statement 1: 00:00:00.688584
Run 4, Statement 2: 00:00:00.740979
Run 5, Statement 1: 00:00:00.688878
Run 5, Statement 2: 00:00:00.742864
Donc, en effet, le prédicat redondant a amélioré les choses (dans un monde parfait, il ne devrait pas, mais nous y sommes. L’optimiseur n’optimise pas cela aussi bien qu’il le pourrait). Mais encore, le FILTER la clause surpasse CASE utilisation des clauses.
Conclusion
Dans un monde parfait, deux syntaxes SQL dont l’équivalence est prouvée fonctionnent également de la même manière. Mais ce n’est pas toujours le cas dans le monde réel, où les optimiseurs font des compromis entre :
- Temps passé à optimiser les syntaxes rares
- Temps passé à exécuter des requêtes
Dans un article de blog précédent (qui est probablement obsolète maintenant), j’ai montré beaucoup de ces cas, où la décision d’optimisation ne dépend d’aucun modèle de coût ni d’ensembles de données et devrait toujours être prise, idéalement. Il y avait une tendance à ce que de telles optimisations soient favorisées par les SGBDR qui ont un cache de plan d’exécution (par exemple, Db2, Oracle, SQL Server), dans le cas où l’optimisation ne doit être effectuée qu’une seule fois par plan mis en cache, puis le plan peut être réutilisé . Dans les SGBDR qui ne disposent pas d’un tel cache, le temps d’optimisation est plus coûteux par requête, donc on peut s’attendre à moins.
Je pense que c’est un cas où il vaut la peine d’examiner des modèles simples d’expressions dans des fonctions d’agrégation. AGG(CASE ..) est un idiome si populaire, et 8% est une amélioration assez significative, que je pense que PostgreSQL devrait résoudre ce problème. Nous verrons. En tout cas, depuis FILTER est déjà:
- Plus performant
- Plus beau
Vous pouvez dès maintenant passer en toute sécurité à cette belle syntaxe SQL standard.
Analyse comparative
Bien que dans ce cas, l’amélioration en vaille la peine, quelles que soient les mesures réelles (car les performances peuvent difficilement être pires et la lisibilité s’améliore réellement), soyez toujours prudent avec de tels résultats de référence. Mesurez les choses vous-même, et si vous ne pouvez pas reproduire un problème de performances, ne touchez pas nécessairement à une logique parfaitement saine simplement parce qu’un article de blog vous l’a dit.
Code de référence
Comme promis, voici le code de référence utilisé pour cet article de blog :
DO $$
DECLARE
v_ts TIMESTAMP;
v_repeat CONSTANT INT := 500;
rec RECORD;
BEGIN
-- Repeat the whole benchmark several times to avoid warmup penalty
FOR r IN 1..5 LOOP
v_ts := clock_timestamp();
FOR i IN 1..v_repeat LOOP
FOR rec IN (
SELECT
fa.actor_id,
SUM(length) FILTER (WHERE rating = 'R'),
SUM(length) FILTER (WHERE rating = 'PG')
FROM film_actor AS fa
LEFT JOIN film AS f
ON f.film_id = fa.film_id
AND rating IN ('R', 'PG')
GROUP BY fa.actor_id
) LOOP
NULL;
END LOOP;
END LOOP;
RAISE INFO 'Run %, Statement 1: %', r, (clock_timestamp() - v_ts);
v_ts := clock_timestamp();
FOR i IN 1..v_repeat LOOP
FOR rec IN (
SELECT
fa.actor_id,
SUM(CASE WHEN rating = 'R' THEN length END),
SUM(CASE WHEN rating = 'PG' THEN length END)
FROM film_actor AS fa
LEFT JOIN film AS f
ON f.film_id = fa.film_id
AND rating IN ('R', 'PG')
GROUP BY fa.actor_id
) LOOP
NULL;
END LOOP;
END LOOP;
RAISE INFO 'Run %, Statement 2: %', r, (clock_timestamp() - v_ts);
RAISE INFO '';
END LOOP;
END$$;
La technique de référence est décrite ici.