Les vues matérialisées dans PostgreSQL utilisent le système des règles, tout comme les vues, mais les résultats persistent sous la forme d'une table. Les principales différences entre :
CREATE MATERIALIZED VIEW ma_vue_mat AS SELECT * FROM ma_table;
et :
CREATE TABLE ma_vue_mat AS SELECT * FROM ma_table;
sont que la vue matérialisée ne peut pas être directement mise à jour et que la requête utilisée pour créer la vue matérialisée est enregistrée exactement de la même façon qu'une requête d'une vue standard. Des données fraiches peuvent être générées pour la vue matérialisée avec cette commande :
REFRESH MATERIALIZED VIEW ma_vue_mat;
L'information sur une vue matérialisée est stockée dans les catalogues systèmes de PostgreSQL exactement de la même façon que pour les tables et les vues. Quand une vue matérialisée est référencée dans une requête, les données sont renvoyées directement à partir de la vue matérialisée, tout comme une table ; la règle est seulement utilisée pour peupler la vue matérialisée.
Bien que l'accès aux données d'une vue matérialisée est souvent bien plus rapide qu'accèder aux tables sous-jacentes directement ou par l'intermédiaire d'une vue, les données ne sont pas toujours fraiches. Cependant, quelques fois, des données plus fraiches ne sont pas nécessaires. Considérez une table qui enregistre les ventes :
CREATE TABLE facture ( no_facture integer PRIMARY KEY, no_vendeur integer, -- identifiant du vendeur date_facture date, -- date de la vente mtt_facture numeric(13,2) -- montant de la vente );
Si des personnes souhaitent grapher rapidement les données de vente, elles peuvent vouloir résumer l'information et ne pas avoir besoin des données incomplètes du jour :
CREATE MATERIALIZED VIEW resume_ventes AS SELECT no_vendeur, date_facture, sum(mtt_facture)::numeric(13,2) as mtt_ventes FROM facture WHERE date_facture < CURRENT_DATE GROUP BY no_vendeur, date_facture; CREATE UNIQUE INDEX ventes_resume_vendeur ON resume_ventes (no_vendeur, date_facture);
Cette vue matérialisée peut être utile pour afficher un graphe dans l'affichage créée pour les vendeurs. Une tâche de fond pourrait être planifiée pour mettre à jour les statistiques chaque nuit en utilisant cette requête SQL :
REFRESH MATERIALIZED VIEW resume_ventes;
Une autre utilisation des vues matérialisées est de permettre un accès
rapide aux données provenant d'un système distant, au travers d'un wrapper
de données distantes. Un exemple utilisant file_fdw
est
donné ci-dessous, avec des chronométrages mais comme cela utilise le cache
du système local, les performances comparées à l'accès à un système distant
seront supérieures à celles montrées ici. Notez que nous exploitons aussi
la capacité à placer un index sur la vue matérialisée alors que
file_fdw
n'autorise pas les index ; cet avantage
pourrait ne pas s'appliquer pour d'autres types d'accès à des données
distantes.
Configuration ::
CREATE EXTENSION file_fdw; CREATE SERVER fichier_local FOREIGN DATA WRAPPER file_fdw; CREATE FOREIGN TABLE mots (mot text NOT NULL) SERVER fichier_local OPTIONS (filename '/usr/share/dict/words'); CREATE MATERIALIZED VIEW vmots AS SELECT * FROM mots; CREATE UNIQUE INDEX idx_vmots ON vmots (mot); CREATE EXTENSION pg_trgm; CREATE INDEX vmots_trgm ON vmots USING gist (mot gist_trgm_ops); VACUUM ANALYZE vmots;
Maintenant, vérifions un mot. En utilisant file_fdw
directement :
SELECT count(*) FROM mots WHERE mot = 'caterpiler'; count ------- 0 (1 row)
Avec EXPLAIN ANALYZE
, nous voyons :
Aggregate (cost=21763.99..21764.00 rows=1 width=0) (actual time=188.180..188.181 rows=1 loops=1) -> Foreign Scan on words (cost=0.00..21761.41 rows=1032 width=0) (actual time=188.177..188.177 rows=0 loops=1) Filter: (word = 'caterpiler'::text) Rows Removed by Filter: 479829 Foreign File: /usr/share/dict/words Foreign File Size: 4953699 Planning time: 0.118 ms Execution time: 188.273 ms
Si la vue matérialisée est utilisée à la place, la requête est bien plus rapide :
Aggregate (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1) -> Index Only Scan using wrd_word on wrd (cost=0.42..4.44 rows=1 width=0) (actual time=0.039..0.039 rows=0 loops=1) Index Cond: (word = 'caterpiler'::text) Heap Fetches: 0 Planning time: 0.164 ms Execution time: 0.117 ms
Dans les deux cas, le mot est mal orthographié. Donc cherchons le bon mot.
Toujours en utilisant file_fdw
:
SELECT mot FROM mots ORDER BY mot <-> 'caterpiler' LIMIT 10; mot --------------- cater caterpillar Caterpillar caterpillars caterpillar's Caterpillar's caterer caterer's caters catered (10 rows)
Limit (cost=11583.61..11583.64 rows=10 width=32) (actual time=1431.591..1431.594 rows=10 loops=1) -> Sort (cost=11583.61..11804.76 rows=88459 width=32) (actual time=1431.589..1431.591 rows=10 loops=1) Sort Key: ((word <-> 'caterpiler'::text)) Sort Method: top-N heapsort Memory: 25kB -> Foreign Scan on words (cost=0.00..9672.05 rows=88459 width=32) (actual time=0.057..1286.455 rows=479829 loops=1) Foreign File: /usr/share/dict/words Foreign File Size: 4953699 Planning time: 0.128 ms Execution time: 1431.679 ms
Et en utilisant la vue matérialisée :
Limit (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10 loops=1) -> Index Scan using wrd_trgm on wrd (cost=0.29..37020.87 rows=479829 width=10) (actual time=187.219..188.252 rows=10 loops=1) Order By: (word <-> 'caterpiler'::text) Planning time: 0.196 ms Execution time: 198.640 ms
Si vous pouvez tolérer des mises à jour périodiques sur les données distantes pour votre base locale, les bénéfices en performance seront importants.