Décaler les valeurs d'une colonne, SQL-92 vs PL/pgSQL vs SQL:2011

Choisir la meilleure méthode

      Tout ce qui suit est inspiré d'un cas réel rencontré sur une base de production.
      C'est la panique chez les géants. Une erreur sur les dates de mise à jour (colonne datmaj) a été commise dans la table historique permettant de stocker leurs changements de devise. Pour rectifier cette erreur, il s'agit de décaler la valeur d'une colonne d'une ligne pour chaque géant.
      Par exemple :

idpo idg devise datmaj 1 1 abc 02/03/2015 5 1 def 07/03/2015 27 1 ghi 09/03/2015 9 2 jkl 03/03/2015 11 2 mno 06/06/2015 12 2 pqr 07/07/2015 ...
doit donner après correction :
idpo idg devise datmaj 1 1 abc 5 1 def 02/03/2015 27 1 ghi 07/03/2015 9 2 jkl 11 2 mno 03/03/2015 12 2 pqr 06/06/2015 ...

      La colonne datmaj est à présent nulle pour chaque première opération concernant un géant donné. La datmaj qui était affectée à la première opération est affectée à la deuxième, la deuxième à la troisième etc.
      Environnement commun de test :

select version(); version ---------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 10.3 (Debian 10.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit (1 ligne) create table geants_hist(idpo integer primary key, idg integer, devise text, datmaj timestamp); CREATE TABLE with recursive serie(idpo, idg, devise, datmaj) as (select 1,trunc(random()*5000000+1),md5(random()::text),current_timestamp - interval '50000000 seconds' + (1 || ' seconds')::interval UNION ALL select idpo + 1,trunc(random()*5000000+1),md5(random()::text),current_timestamp - interval '50000000 seconds' + (idpo || ' seconds')::interval from serie where idpo < 50000000) insert into geants_hist select idpo, idg, devise, datmaj from serie; INSERT 0 50000000 analyze geants_hist; ANALYZE \d+ Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description ----------+-------------------+-------+--------------+------------+------------- postgres | geants_hist | table | postgres | 4028 MB |


      Le développeur tente d'abord d'effectuer le traitement avec une requête SQL traditionnelle. Son idée est globalement de déterminer, pour une ligne donnée, la date de l'opération précédente concernant le même géant en se servant d'un panel de possibilités : jointure externe, sous-requête avec max et autojointure avec un test d'infériorité stricte :

explain select h.idpo, h.idg, h.devise, t2.datmaj from geants_hist h left join geants_hist t2 on (t2.idpo = (select max(t.idpo) from geants_hist t where t.idg = h.idg and t.idpo < h.idpo)); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Hash Left Join (cost=1884605.18..456019404725.81 rows=50000008 width=49) Hash Cond: ((SubPlan 2) = t2.idpo) -> Seq Scan on geants_hist h (cost=0.00..1015464.08 rows=50000008 width=41) -> Hash (cost=1015464.08..1015464.08 rows=50000008 width=12) -> Seq Scan on geants_hist t2 (cost=0.00..1015464.08 rows=50000008 width=12) SubPlan 2 -> Result (cost=182405.83..182405.84 rows=1 width=4) InitPlan 1 (returns $2) -> Limit (cost=0.56..182405.83 rows=1 width=4) -> Index Scan Backward using geants_hist_pkey on geants_hist t (cost=0.56..729621.62 rows=4 width=4) Index Cond: ((idpo IS NOT NULL) AND (idpo < h.idpo)) Filter: (idg = h.idg) create table geants_hist_bis_1 as select h.idpo, h.idg, h.devise, t2.datmaj from geants_hist h left join geants_hist t2 on (t2.idpo = (select max(t.idpo) from geants_hist t where t.idg = h.idg and t.idpo < h.idpo)); -- pas de réponse...


      Cela n'aboutit pas. Le réflexe peut alors être de passer directement à un langage externe comme PL/pgSQL pour traiter le cas :

create table geants_hist_bis_2 as select * from geants_hist fetch first 0 row only; SELECT 0 Temps : 27,912 ms DO $$ declare rec record; previous_idg integer; previous_datmaj timestamp; begin FOR rec IN SELECT idpo, idg, devise, datmaj FROM geants_hist g ORDER BY idg asc, idpo asc LOOP IF rec.idg <> previous_idg THEN insert into geants_hist_bis_2(idpo, idg, devise, datmaj) values (rec.idpo, rec.idg, rec.devise, null); else insert into geants_hist_bis_2(idpo, idg, devise, datmaj) values (rec.idpo, rec.idg, rec.devise, previous_datmaj); END IF; previous_idg := rec.idg; previous_datmaj := rec.datmaj; END LOOP; end$$; DO Durée : 1620257,159 ms (27:00,257) ... Durée : 1632276,200 ms (27:12,276) ... Durée : 1575203,400 ms (26:15,203)


      C'est OK mais n'est-il pas possible de réaliser ce traitement en SQL ? Une solution tirant parti des évolutions de la norme peut être envisagée. Les fonctions de fenêtrage ont été introduites par SQL:2003, la possibilité d'accéder directement à plusieurs lignes de la même fenêtre via des fonctions comme lag ou lead a été introduite par SQL:2011.
      L'idée est globalement de définir une fenêtre avec partitionnement par géant (idg) et tri ascendant sur les opérations (idpo). La fonction lag peut ensuite être utilisée, elle permet en effet d'obtenir la valeur d'une ligne précédente dans la partition pour la colonne de notre choix (ici datmaj).
      L'écriture est alors directe :

explain (select idpo, idg, devise, lag(datmaj, 1 , null) over(partition by idg order by idpo asc) as datmaj from geants_hist); QUERY PLAN ------------------------------------------------------------------------------------- WindowAgg (cost=12536282.35..13536282.51 rows=50000008 width=49) -> Sort (cost=12536282.35..12661282.37 rows=50000008 width=49) Sort Key: idg, idpo -> Seq Scan on geants_hist (cost=0.00..1015464.08 rows=50000008 width=49) create table geants_hist_bis_3 as (select idpo, idg, devise, lag(datmaj, 1 , null) over(partition by idg order by idpo asc) as datmaj from geants_hist); SELECT 50000000 Durée : 878375,982 ms (14:38,376) ... Durée : 924743,239 ms (15:24,743) ... Durée : 864000,573 ms (14:24,001)


      Moins de 16 minutes contre 26 minutes : le bloc anonyme PL/pgSQL est largement battu.
      SQL-92 est souvent suffisant pour le travail transactionnel mais il ne faut jamais hésiter à regarder du côté de l'évolution de la norme pour réaliser des opérations plus complexes sur des volumes de données importants.

Mise à jour : 16/03/2018