Comment puis-je stocker et accéder efficacement aux sous-sélections fréquemment utilisées dans PostgreSQL ?

Optimisation des sous-requêtes fréquentes dans PostgreSQL

Les utilisateurs de PostgreSQL rencontrent fréquemment des requêtes reposant sur des sous-requêtes répétées, agrégeant souvent des données de tables associées. Cet article explore des méthodes efficaces pour gérer ces modèles de sous-requêtes courants, améliorant ainsi les performances et la lisibilité du code. Un scénario typique consiste à récupérer des données agrégées à partir d'une table secondaire basée sur une relation de clé étrangère.

L'approche View : une solution robuste

Bien qu'il ne soit pas possible d'intégrer directement des sous-requêtes sous forme de pseudo-colonnes, la fonctionnalité VIEW de PostgreSQL offre une excellente alternative. Un VIEW définit une table virtuelle basée sur les tables et requêtes existantes, créant ainsi une vue personnalisée des données. Cela évite de modifier la structure de la table de base tout en obtenant le résultat souhaité.

Colonnes générées (PostgreSQL 11) : performances améliorées

PostgreSQL 11 et versions ultérieures proposent des colonnes générées (également appelées colonnes virtuelles). Ceux-ci permettent de définir une colonne dont la valeur est calculée à l'aide d'une expression, reflétant l'objectif d'intégration de sous-requêtes. Cette approche donne souvent de meilleures performances que les vues.

Émulation des colonnes générées dans les anciennes versions

Pour les versions antérieures à PostgreSQL 11, ou lorsqu'une approche de colonne virtuelle est préférée, une fonction définie par l'utilisateur peut simuler les colonnes générées. Cette fonction prend le type de ligne du tableau en entrée et calcule la valeur souhaitée. La fonction est ensuite liée à une pseudo-colonne, obtenant une fonctionnalité similaire aux colonnes générées.

Mise en œuvre pratique

Illustrons avec un exemple :

-- Create tables
CREATE TABLE tbl_a (a_id int, col1 int, col2 int);
CREATE TABLE tbl_b (b_id int, a_id int, colx int);

-- Sample data
INSERT INTO tbl_a VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4);
INSERT INTO tbl_b VALUES
(1,1,5),  (2,1,5),  (3,1,1)
, (4,2,8),  (5,2,8),  (6,2,6)
, (7,3,11), (8,3,11), (9,3,11);

-- Function to emulate the generated column
CREATE FUNCTION col3(tbl_a)
RETURNS int8
LANGUAGE sql STABLE AS
$func$
SELECT sum(colx)
FROM   tbl_b b
WHERE  b.a_id = .a_id
$func$;

Maintenant, accédez à la col3 pseudo-colonne :

SELECT a_id, col1, col2, tbl_a.col3
FROM   tbl_a;

SELECT *, a.col3 FROM tbl_a a;

Avantages des colonnes générées

Par rapport aux vues, les colonnes générées offrent :

• Intégration transparente : Inclus dans les SELECT * requêtes sans mention explicite.
• Mises à jour dynamiques : Reflète automatiquement les modifications dans la table de base.
• Performances optimisées : Tire parti de l'optimiseur de requêtes de PostgreSQL pour plus d'efficacité.

Conclusion

Bien que l'intégration directe de sous-requêtes ne soit pas prise en charge, PostgreSQL propose des alternatives efficaces (vues et colonnes générées) pour gérer les sous-requêtes fréquemment utilisées. Ces méthodes améliorent les performances des requêtes et la clarté du code, conduisant à une gestion de base de données plus efficace. Choisissez l'approche la mieux adaptée à votre version de PostgreSQL et à vos exigences de performances.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!