Comment puis-je générer efficacement des colonnes calculées dans PostgreSQL pour des requêtes plus rapides ?

Optimisation des requêtes PostgreSQL avec des colonnes calculées

Pour les requêtes accédant fréquemment aux valeurs dérivées de plusieurs tables, la création de colonnes calculées dans la table cible améliore considérablement les performances. PostgreSQL propose plusieurs méthodes pour y parvenir.

Les vues, tout en offrant une représentation logique d'une requête, ne sont pas idéales pour les scénarios critiques en termes de performances, car elles ne stockent pas physiquement les données.

Une solution supérieure est les "colonnes générées" de PostgreSQL (disponibles à partir de la version 11). STORED les colonnes générées stockent les valeurs calculées directement dans la table, offrant les mêmes performances que les colonnes traditionnelles.

Pour les anciennes versions de PostgreSQL ou les situations où les colonnes générées virtuellement ne conviennent pas, vous pouvez simuler des colonnes calculées à l'aide de fonctions. Ces fonctions acceptent le type de table en entrée et renvoient la valeur calculée, agissant effectivement comme des pseudo-colonnes.

Illustrons avec un exemple :

<code class="language-sql">CREATE TABLE tbl_a (a_id int, col1 int, col2 int);
INSERT INTO tbl_a VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4);

CREATE TABLE tbl_b (b_id int, a_id int, colx int);
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);</code>

Pour créer une pseudo-colonne col3 à l'aide d'une fonction :

<code class="language-sql">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$;</code>

Accédez à la valeur calculée en utilisant soit la notation attributaire (tbl_a.col3), soit la notation fonctionnelle (col3(tbl_a)), offrant une flexibilité de requête. N'oubliez pas d'utiliser des alias de table pour éviter les conflits de noms.

L'utilisation de colonnes calculées ou de fonctions générées minimise les sous-requêtes, rationalise la récupération des données et augmente considérablement la vitesse des requêtes. La meilleure approche dépend de vos besoins spécifiques et de la version de PostgreSQL. Un examen attentif garantit une conception efficace de la base de données.

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!