Filtrage avant application d’une fonction d’agrégat

(sujet mis à jour avec la version 11)

      Dans cet article général, je présente le principe selon lequel le moteur des SGBD travaille requête par requête. Certes, les exécutions successives travaillant sur les mêmes données bénéficient de la mise en cache. Mais il est malgré tout souvent préférable de ne faire qu’un seul passage sur les données.
      Ici je reprends le contexte du clan des géants. Différentes statistiques sont régulièrement demandées au scribe concernant la population jeune :


      Pour cela le scribe Margiono exécute 6 requêtes, démonstration avec PostgreSQL 9.6 :

select version(); version --------------------------------------------------------------------------------------------------- PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-10) 6.3.0 20170321, 64-bit (1 ligne) create table geants( idu uuid, dtn date, genre char(1), taille smallint, masse smallint, actif boolean, devise varchar(128), pw smallint, heureux boolean, couleur varchar(8), veteran boolean, clan smallint, gabarit varchar(8), revenu integer, pm smallint, berserk boolean, tutelaire smallint, ere varchar(10), cyclope boolean); CREATE TABLE CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; NOTICE: l'extension " uuid-ossp " existe déjà, poursuite du traitement CREATE EXTENSION WITH serie(i) AS (SELECT generate_series(5500000,100001,-1)) insert into geants select uuid_generate_v4(), current_date - (ceil(i/3) || ' minutes')::interval + (trunc(random() * 100 + 1) || ' days')::interval, case when random() < 0.45 then 'M' else 'F' end, 200 + (trunc(random() * 200 + 1)), 300 + (trunc(random() * 200 + 1)), case when random() < 0.7 then false else true end, upper(md5(random()::text)), (trunc(random()*100 + 1)), case when random() < 0.1 then false else true end, case when random() < 0.7 then 'GRIS' when random() < 0.8 then 'NOIR' else 'BLEU' end, case when random() < 0.9 then false else true end, (trunc(random()*1000 + 1)), case when random() < 0.1 then 'PETIT' when random() < 0.9 then 'MOYEN' else 'GRAND' end, (trunc(random()*1000000 + 1)), (trunc(random()*10 + 1)), case when random() < 0.01 then true else false end, (trunc(random()*10 + 1)), case when i < 18000000 then 'TAUREAU' when i < 40000000 then 'LICORNE' else 'DRAGON' end, case when random() < 0.001 then true else false end from serie; INSERT 0 5400000 select avg(taille) from geants where dtn >= (current_date - interval '500 days') and genre = 'M'; avg ---------------------- 300.5259089808952008 (1 ligne) Temps : 1704,752 ms Temps : 1687,436 ms Temps : 1699,131 ms select avg(taille) from geants where dtn >= (current_date - interval '500 days') and genre = 'F'; avg ---------------------- 300.5224571954215197 (1 ligne) Temps : 1947,302 ms Temps : 1946,471 ms Temps : 1923,097 ms select avg(masse) from geants where dtn >= (current_date - interval '500 days') and actif; avg ---------------------- 400.5495511178533237 (1 ligne) Temps : 1247,134 ms Temps : 1261,909 ms Temps : 1244,002 ms select avg(masse) from geants where dtn >= (current_date - interval '500 days') and not actif; avg ---------------------- 400.4168965184201251 (1 ligne) Temps : 2129,949 ms Temps : 2112,982 ms Temps : 2119,222 ms select count(*) from geants where dtn >= (current_date - interval '500 days') and revenu > 900000; count -------- 228287 (1 ligne) Temps : 1137,624 ms Temps : 1102,817 ms Temps : 1109,720 ms select count(*) from geants where dtn >= (current_date - interval '500 days') and berserk and veteran; count ------- 2256 (1 ligne) Temps : 781,292 ms Temps : 798,236 ms Temps : 772,947 ms

      Le temps cumulé est autour de 9s. Ce n’est pas gigantesque mais il est à prévoir que le Grand Chef demande de plus en plus d’informations. Margiono aimerait que le temps de traitement n’explose pas. Le Grand Chef a une lourde massue et il n’est pas spécialement patient !
      Margiono décide de ne plus utiliser qu’une requête. Il pourrait le faire avec CASE mais décide d’utiliser FILTER. Cette clause a été introduite dans la norme SQL:2003. Elle est utilisable depuis PostgreSQL 9.4 et permet de filtrer avant d’appliquer une fonction d’agrégat. Il va donc appliquer 6 filtres différents correspondant aux 6 cas demandés. Démonstration, toujours avec PostgreSQL 9.6 :

explain select avg(taille) filter (where genre = 'M') taille_moyenne_geants, avg(taille) filter (where genre = 'F') taille_moyenne_geantes, avg(masse) filter (where actif) masse_moyenne_actifs, avg(masse) filter (where not actif) masse_moyenne_blesses, count(*) filter (where revenu > 900000) nombre_riches, count(*) filter (where berserk and veteran) nombre_guerriers_fous from geants where dtn >= (current_date - interval '500 days'); QUERY PLAN -------------------------------------------------------------------------- Aggregate (cost=129910.46..129910.46 rows=1 width=144) -> Seq Scan on geants (cost=0.00..118623.50 rows=501642 width=19) Filter: (dtn >= (('now'::cstring)::date - '500 days'::interval)) (3 lignes) select avg(taille) filter (where genre = 'M') taille_moyenne_geants, avg(taille) filter (where genre = 'F') taille_moyenne_geantes, avg(masse) filter (where actif) masse_moyenne_actifs, avg(masse) filter (where not actif) masse_moyenne_blesses, count(*) filter (where revenu > 900000) nombre_riches, count(*) filter (where berserk and veteran) nombre_guerriers_fous from geants where dtn >= (current_date - interval '500 days'); taille_moyenne_geants | taille_moyenne_geantes | masse_moyenne_actifs | masse_moyenne_blesses | nombre_riches | nombre_guerriers_fous -----------------------+------------------------+----------------------+-----------------------+---------------+----------------------- 300.5259089808952008 | 300.5224571954215197 | 400.5495511178533237 | 400.4168965184201251 | 228287 | 2256 (1 ligne) Temps : 3155,818 ms Temps : 3167,542 ms Temps : 3141,067 ms

      Un seul balayage de la table GEANTS est à présent nécessaire pour obtenir les informations demandées par le Grand Chef. Le temps de traitement cumulé est passée de 9s environ à 3s2. Pari réussi pour Margiono !

Mise à jour : 15/04/2017