Forums PostgreSQL.fr

postgresqlforumuser

Membre

Meilleure implémentation d'une CTE récursive en while loop

Bonjour,

J'ai deux requêtes qui calculent une cloture transitive sur un graphe. La première utilise une CTE recursive et la deuxième utilise une boucle WHILE. La première est beaucoup plus rapide que la deuxième. Je voudrais savoir s'il est possible d'améliorer la boucle WHILE pour avoir les mêmes performances.

Voici la première requête:

CREATE TEMPORARY VIEW const_subquery2 AS
  (SELECT src AS x, trg AS col1 FROM graph AS t WHERE predicate = '<p>');
CREATE TEMPORARY RECURSIVE VIEW fixpoint_relation1_X3 (x) AS
    SELECT src AS x FROM graph WHERE trg = '<T>' AND predicate = '<p>'
  UNION 
    SELECT x FROM (SELECT x AS col1 FROM fixpoint_relation1_X3) AS t NATURAL JOIN const_subquery2;
SELECT COUNT(*) FROM (SELECT DISTINCT * FROM (SELECT * FROM fixpoint_relation1_X3) AS t) as t3;

Voici la deuxième requête:

CREATE TEMPORARY VIEW const_subquery2 AS
  (SELECT src AS x, trg AS col1 FROM graph WHERE predicate = '<p>');
DO $$BEGIN
CREATE TEMPORARY TABLE fixpoint_tmp AS
  (SELECT src AS x FROM graph WHERE trg = '<T>' AND predicate = '<p>');
CREATE TEMPORARY TABLE fixpoint_relation1_X3 AS (SELECT * FROM fixpoint_tmp);
WHILE EXISTS (SELECT 1 FROM fixpoint_relation1_X3) LOOP
  CREATE TEMPORARY TABLE nouvelles AS
    (SELECT x FROM (SELECT x AS col1 FROM fixpoint_relation1_X3) AS t NATURAL JOIN const_subquery2 EXCEPT SELECT * FROM fixpoint_tmp);
  INSERT INTO fixpoint_tmp (SELECT * FROM nouvelles);
  DROP TABLE fixpoint_relation1_X3;
  ALTER TABLE nouvelles RENAME TO fixpoint_relation1_X3;
END LOOP;
DROP TABLE fixpoint_relation1_X3;
ALTER TABLE fixpoint_tmp RENAME TO fixpoint_relation1_X3;
END;$$;
SELECT COUNT(*) FROM (SELECT DISTINCT * FROM (SELECT * FROM fixpoint_relation1_X3) AS t) as t3;

rjuju

Administrateur

Re : Meilleure implémentation d'une CTE récursive en while loop

Bonjour,

Quel est l'écart entre les 2 approches ?

Le problème ne vient pas forcément du fait que vous utilisez une boucle while, même si le langage plpgsql n'a jamais été écrit pour être très rapide, mais plutôt du fait que chaque itération à un surcout assez impressionnant du fait que vous créez, supprimez et renommez des tables à chaque itération.  Même si vous pouviez rendre cette fonction plus rapide, je vous déconseillerai de l'utiliser car elle aurait d'autres problèmes, notamment une fragmentation excessive des catalogues systèmes.

---

Julien.
https://rjuju.github.io/

postgresqlforumuser

Membre

Re : Meilleure implémentation d'une CTE récursive en while loop

Merci pour la réponse. Il y a des instances où la première prend autour 20s et la deuxième autour de 100s. La raison d'utiliser plpgsql est que c'est plus expressif que les CTE récursifs où on ne peut pas mettre la variable récursive dans une sous requête.