F.17. intagg

L'agrégateur est une fonction d'agrégat int_array_aggregate(integer) qui produit un tableau d'entiers contenant exactement les entiers fournis en argument. Cette fonction appelle array_agg pour des raisons de compatibilité.

L'énumérateur est une fonction int_array_enum(integer[]) qui renvoie setof integer. C'est essentiellement une opération reverse de l'agrégateur : elle étend un tableau d'entiers en un ensemble de lignes. Cette fonction appelle unnest, pour des raisons de compatibilité.

Un grand nombre de bases de données utilisent la notion de table « une vers plusieurs » (one to many). Ce type de table se trouve habituellement entre deux tables indexés, par exemple :

CREATE TABLE left (id INT PRIMARY KEY, ...);
CREATE TABLE right (id INT PRIMARY KEY, ...);
CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);
 

Il est typiquement utilisé de cette façon :

  SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)
    WHERE one_to_many.left = item;
 

Cela renvoie tous les éléments de la table de droite pour un enregistrement de la table de gauche donné. Il s'agit d'une construction assez commune en SQL.

Cette méthode devient complexe lorsqu'il existe de nombreuses entrées dans la table one_to_many. Souvent, une jointure de ce type résulte en un parcours d'index et une récupération de chaque enregistrement de la table de droite pour une entrée particulière de la table de gauche. Sur un système dynamique, il n'y a pas grand chose à faire. Au contraire, lorsqu'une partie des données est statique, une table de résumé peut être créée par agrégation.

CREATE TABLE summary AS
  SELECT left, int_array_aggregate(right) AS right
  FROM one_to_many
  GROUP BY left;
 

Ceci crée une table avec une ligne par élément gauche et un tableau d'éléments droits. En l'absence de méthode d'utilisation de tableau, c'est réellement inutilisable, d'où l'énumérateur.

Exemple :

SELECT left, int_array_enum(right) FROM summary WHERE left = item;
 

La requête ci-dessus, qui utilise int_array_enum, produit les mêmes résultats que celle-ci :

SELECT left, right FROM one_to_many WHERE left = item;
 

La différence tient dans le fait que la requête qui utilise la table de résumé ne récupère qu'une ligne de la table alors que la requête directe à one_to_many doit faire un parcours d'index et récupérer une ligne par enregistrement.

Sur un système particulier, un EXPLAIN a montré qu'une requête avec un coût de 8488 a été réduite à une requête d'un coût de 329. La requête originale était une jointure impliquant la table one_to_many, remplacée par :

SELECT right, count(right) FROM
  ( SELECT left, int_array_enum(right) AS right
    FROM summary JOIN (SELECT left FROM left_table WHERE left = item) AS lefts
         ON (summary.left = lefts.left)
  ) AS list
  GROUP BY right
  ORDER BY count DESC;