Fonctions de fenêtrage

(sujet mis à jour avec la version 11)

Le 20ème siècle est si loin...

      Dans cette page présentant le parallélisme nous avons vu que découper la lecture d’un grand ensemble de données était parfois intéressant. Mais le plan initial n’était pas si mauvais dans une optique décisionnelle. Sur les systèmes modernes, balayer une table de 6Go ne prend pas un temps "infini". Ce qui est ennuyeux c’est de lire les mêmes données N fois dans une requête ou un traitement.
      Reprenons la table VENTES_AGG contenant les ventes par seconde de chacun de nos 200 produits sur un an. Pour le produit ID_PROD, le montant de vente MTV a été réalisé dans la seconde suivant le temps stocké dans la colonne DTV.
      M.Gnok demande cette fois la moyenne glissante à 7 jours des montants journaliers cumulés de vente pour chacun de nos produits.
      Alex Mou, stagiaire, tente de l’écrire en partant d’un vieux bouquin SQL puis d’un livre plus récent :

postgres=# select version(); version ----------------------------------------------------------------------------------------------- PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit (1 ligne) Temps : 6,071 ms postgres=# \d+ Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+------------+-------+--------------+---------+------------- public | ventes_agg | table | postgres | 6000 MB | (1 row) postgres=# \di+ Aucune relation trouvée. postgres=# explain create table mg7_1992 as postgres-# select postgres-# W.id_prod, postgres-# W.jour, postgres-# avg(W.mtv_cumul) moyenne_mobile_mtv_cumul_7 postgres-# from postgres-# ( postgres(# select A.id_prod, A.jour, B.mtv_cumul postgres(# from postgres(# ( postgres(# select id_prod, date_trunc('day', dtv) jour postgres(# from ventes_agg postgres(# group by id_prod, date_trunc('day', dtv) postgres(# ) AS A postgres(# JOIN postgres(# ( postgres(# select id_prod, date_trunc('day', dtv) jour, sum(mtv) mtv_cumul postgres(# from ventes_agg postgres(# group by id_prod, date_trunc('day', dtv) postgres(# ) AS B postgres(# ON (A.id_prod = B.id_prod AND B.jour between A.jour - interval '6 days' and A.jour) postgres(# ) AS W postgres-# group by id_prod, jour postgres-# order by 1, 2 asc; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=58098762.04..23583914625.82 rows=40000 width=20) Group Key: ventes_agg.id_prod, (date_trunc('day'::text, ventes_agg.dtv)) -> Merge Join (cost=58098762.04..22743814197.83 rows=112013323732 width=20) Merge Cond: (ventes_agg.id_prod = ventes_agg_1.id_prod) Join Filter: (((date_trunc('day'::text, ventes_agg_1.dtv)) <= (date_trunc('day'::text, ventes_agg.dtv))) AND ((date_trunc('day'::text, ventes_agg_1.dtv)) >= ((date_trunc('day'::text, ventes_agg.dtv)) - '6 days'::interval))) -> Group (cost=29049381.02..30149837.25 rows=14199436 width=12) Group Key: ventes_agg.id_prod, (date_trunc('day'::text, ventes_agg.dtv)) -> Sort (cost=29049381.02..29404366.90 rows=141994352 width=12) Sort Key: ventes_agg.id_prod, (date_trunc('day'::text, ventes_agg.dtv)) -> Seq Scan on ventes_agg (cost=0.00..2542466.40 rows=141994352 width=12) -> Materialize (cost=29049381.02..30824310.44 rows=14199436 width=20) -> GroupAggregate (cost=29049381.02..30646817.49 rows=14199436 width=16) Group Key: ventes_agg_1.id_prod, (date_trunc('day'::text, ventes_agg_1.dtv)) -> Sort (cost=29049381.02..29404366.90 rows=141994352 width=16) Sort Key: ventes_agg_1.id_prod, (date_trunc('day'::text, ventes_agg_1.dtv)) -> Seq Scan on ventes_agg ventes_agg_1 (cost=0.00..2542466.40 rows=141994352 width=16) (16 lignes) Temps : 6,366 ms postgres=# create table mg7_1992 as postgres-# select postgres-# W.id_prod, postgres-# W.jour, postgres-# avg(W.mtv_cumul) moyenne_mobile_mtv_cumul_7 postgres-# from postgres-# ( postgres-# select A.id_prod, A.jour, B.mtv_cumul postgres-# from postgres-# ( postgres-# select id_prod, date_trunc('day', dtv) jour postgres-# from ventes_agg postgres-# group by id_prod, date_trunc('day', dtv) postgres-# ) AS A postgres-# JOIN postgres-# ( postgres-# select id_prod, date_trunc('day', dtv) jour, sum(mtv) mtv_cumul postgres-# from ventes_agg postgres-# group by id_prod, date_trunc('day', dtv) postgres-# ) AS B postgres-# ON (A.id_prod = B.id_prod AND B.jour between A.jour - interval '6 days' and A.jour) postgres-# ) AS W postgres-# group by id_prod, jour postgres-# order by 1, 2 asc; SELECT 73200 Temps : 812469,215 ms postgres=# explain create table mg7_2003 as postgres-# select id_prod, postgres-# date_trunc('day', dtv) jour, postgres-# avg(sum(mtv)) over(partition by id_prod order by date_trunc('day', dtv) rows 6 preceding) moyenne_mobile_mtv_cumul_7 postgres-# from ventes_agg postgres-# group by id_prod, date_trunc('day', dtv) postgres-# order by 1,2 asc; QUERY PLAN ------------------------------------------------------------------------------------------- WindowAgg (cost=29049381.02..30930806.21 rows=14199436 width=16) -> GroupAggregate (cost=29049381.02..30646817.49 rows=14199436 width=16) Group Key: id_prod, (date_trunc('day'::text, dtv)) -> Sort (cost=29049381.02..29404366.90 rows=141994352 width=16) Sort Key: id_prod, (date_trunc('day'::text, dtv)) -> Seq Scan on ventes_agg (cost=0.00..2542466.40 rows=141994352 width=16) (6 lignes) postgres=# create table mg7_2003 as postgres-# select id_prod, postgres-# date_trunc('day', dtv) jour, postgres-# avg(sum(mtv)) over(partition by id_prod order by date_trunc('day', dtv) rows 6 preceding) moyenne_mobile_mtv_cumul_7 postgres-# from ventes_agg postgres-# group by id_prod, date_trunc('day', dtv) postgres-# order by 1,2 asc; SELECT 73200 Temps : 438293,048 ms

      La première requête globalement "SQL:1992" utilise une auto-jointure et donne lieu à 2 balayages complets de la table VENTES_AGG. La deuxième requête tire parti des possibilités offertes par la norme SQL:2003, ici les fonctions de fenêtrage, et n’effectue qu’un seul balayage. La première requête a pris 13min32s alors que la deuxième n’a pris "que" 7min18s.
      Si vous êtes (comme moi) un vieil habitué d’Oracle vous ne connaissez peut-être pas la norme SQL:1992 et même la première requête et son "JOIN" peut vous sembler peu familière. Si vous utilisez des (+) quand vous voulez réaliser une jointure externe ou encore des start with...connect by quand vous voulez réaliser une requête hiérarchique alors vous êtes hors standard SQL. Je ne vous jette pas la pierre, changer les habitudes et donc prendre des risques n’est pas facile lorsqu’on doit coder de nouvelles fonctionnalités avec de la pression au niveau des délais.
      Je ne jette pas non plus la pierre à Oracle. Ce SGBD respecte les nouvelles normes mais conserve les syntaxes propriétaires pour compatiblité ascendante. Leurs requêtes hiérarchiques étaient par exemple disponibles depuis Oracle 2 au début des années 1980. Elles ont rendu bien des services mais écrire en 2016 du pseudo SQL des années 1980 c’est dommage. Certaines versions propriétaires de PostgreSQL, comme celle d’EDB, vous permettraient de garder ces (mauvaises) habitudes mais quitte à changer de SGBD autant les abandonner.
      Esnuite pourquoi se limiter à 1992 ? Comme le rappelle "Use the index Luke", vous n’avez plus une station de travail sous Windows 3.1 et se cantonner à SQL:1992 c’est un peu utiliser ce vieux système d’exploitation plutôt que Windows 10 (ou Ubuntu 15.10 pour les ultras de Linux même côté client). Le passage à PostgreSQL peut être une bonne occasion de faire un bond comparable à celui de Windows 3.1 vers Windows 10, mais au niveau du SGBD. Vous pourrez découvrir la richesse des dernières évolutions de la norme SQL.
      PostgreSQL est particulièrement respectueux de la norme, la documentation indique clairement pour chaque syntaxe si vous utilisez quelque chose de "standard" ou une "extension de langage". En adoptant les nouveautés vous rendrez votre code plus lisible et vous aurez peut-être en plus d’excellentes surprises au niveau des performances, comme dans le petit exemple présenté.

Mise à jour : 07/02/2016