Il existe plusieurs manières d’accéder aux données d’une base de données. Je vais évoquer ici les principales méthodes d’accès possibles avec PostgreSQL : les requêtes directes sur les tables, les requêtes sur des vues, les requêtes sur des vues matérialisées.
Différence entre vue et requête
La vue est une requête préparée permettant de simplifier drastiquement le code de développement en reportant la complexité du code SQL à l’intérieur de la base de données. La vue permet donc un allègement du code applicatif. Le plan d’exécution entre une vue et la requête directe sans vue n’a aucune raison de ne pas être identique.
Techniquement, la vue est une règle (rule) associée à une structure de table qui permet la réécriture de requête. Lors de l’exécution d’une requête sur une vue, l’optimiseur va réécrire la requête avec le contenu de la vue.
Créer une vue revient à créer une règle avec une structure de table correspondant aux colonnes de la vue.
Différence entre vue et vue matérialisée
La vue matérialisée est la combinaison d’une requête et d’une table physique sous-jacente.
Contrairement à la vue, la vue matérialisée contient le résultat de la requête lors de sa dernière exécution.
La vue matérialisée peut donc ne pas contenir un résultat cohérent avec le résultat produit par la même requête si le contenu des tables de la requête de vue matérialisée ont entretemps évolué en terme de contenu. Dans tel cas, il devient nécessaire de rafraîchir la vue matérialisée afin d’en rendre le résultat cohérent.
!!! ATTENTION !!! Contrairement à Oracle, les vues matérialisées n’ont pas vocation à servir à la réécriture de requête telle qu’elle est réalisée par l’optimiseur Oracle. Les vues matérialisées doivent être explicitement appelées au niveau du code applicatif SQL pour être sollicitées.
Avantage de la vue matérialisée
La vue matérialisée est un outil très puissant, généralement utilisé dans le cadre des datawarehouse et datamart afin de remonter les résultats d’agrégats rapidement et sans calcul lourd au niveau du serveur de données.
Dans les environnements de datawarehouse, les données sont en général chargées quotidiennement (souvent en nuit) et ensuite ne bougent plus jusqu’au chargement du jour suivant. De ce fait, le vues matérialisées peuvent permettre d’accélérer l’accès aux agrégats les plus souvent demandés par les outils décisionnels.
Les vues matérialisées sont dans ce cas recalculées en fin de chargement du datawarehouse.
Différents exemples de requête directe, vue et vue matérialisée
Création de la vue
create view v_agg_csp_par_reg_tra_11 as
select
s.an_recens,
c.csp,
c.csp_desc,
r.reg,
r.reg_desc,
sum(s.pond) as compte
from
statistiques s
join csp c on (c.csp = s.csp)
join reg r on (r.reg = s.reg_tra_11)
group by
s.an_recens,
c.csp,
c.csp_desc,
r.reg,
r.reg_desc
order by
s.an_recens,
c.csp_desc,
r.reg_desc;
Création de la vue matérialisée
create materialized view mv_agg_csp_par_reg_tra_11 as
select
s.an_recens,
c.csp,
c.csp_desc,
r.reg,
r.reg_desc,
sum(s.pond) as compte
from
statistiques s
join csp c on (c.csp = s.csp)
join reg r on (r.reg = s.reg_tra_11)
group by
s.an_recens,
c.csp,
c.csp_desc,
r.reg,
r.reg_desc
order by
s.an_recens,
c.csp_desc,
r.reg_desc;
Requête directe
explain select s.an_recens, c.csp, c.csp_desc, r.reg, r.reg_desc, sum(s.pond) as compte from statistiques s join csp c on (c.csp = s.csp) join reg r on (r.reg = s.reg_tra_11) where an_recens = '1968' group by s.an_recens, c.csp, c.csp_desc, r.reg, r.reg_desc order by s.an_recens, c.csp_desc, r.reg_desc; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Sort (cost=246932.26..246936.82 rows=1824 width=466) Sort Key: c.csp_desc, r.reg_desc -> HashAggregate (cost=246810.66..246833.46 rows=1824 width=466) Group Key: s.an_recens, c.csp_desc, r.reg_desc, c.csp, r.reg -> Hash Join (cost=3.04..190124.94 rows=3779048 width=466) Hash Cond: ((s.reg_tra_11)::text = (r.reg)::text) -> Hash Join (cost=1.18..138161.18 rows=3779048 width=239) Hash Cond: ((s.csp)::text = (c.csp)::text) -> Append (cost=0.00..86198.09 rows=3779048 width=15) -> Seq Scan on statistiques s (cost=0.00..0.00 rows=1 width=68) Filter: ((an_recens)::text = '1968'::text) -> Seq Scan on statistiques_1968 s_1 (cost=0.00..86198.09 rows=3779047 width=15) Filter: ((an_recens)::text = '1968'::text) -> Hash (cost=1.08..1.08 rows=8 width=226) -> Seq Scan on csp c (cost=0.00..1.08 rows=8 width=226) -> Hash (cost=1.38..1.38 rows=38 width=230) -> Seq Scan on reg r (cost=0.00..1.38 rows=38 width=230) (17 lignes)
Vue
explain select * from v_agg_csp_par_reg_tra_11 where an_recens = '1968'; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Sort (cost=246932.26..246936.82 rows=1824 width=466) Sort Key: c.csp_desc, r.reg_desc -> HashAggregate (cost=246810.66..246833.46 rows=1824 width=466) Group Key: s.an_recens, c.csp_desc, r.reg_desc, c.csp, r.reg -> Hash Join (cost=3.04..190124.94 rows=3779048 width=466) Hash Cond: ((s.reg_tra_11)::text = (r.reg)::text) -> Hash Join (cost=1.18..138161.18 rows=3779048 width=239) Hash Cond: ((s.csp)::text = (c.csp)::text) -> Append (cost=0.00..86198.09 rows=3779048 width=15) -> Seq Scan on statistiques s (cost=0.00..0.00 rows=1 width=68) Filter: ((an_recens)::text = '1968'::text) -> Seq Scan on statistiques_1968 s_1 (cost=0.00..86198.09 rows=3779047 width=15) Filter: ((an_recens)::text = '1968'::text) -> Hash (cost=1.08..1.08 rows=8 width=226) -> Seq Scan on csp c (cost=0.00..1.08 rows=8 width=226) -> Hash (cost=1.38..1.38 rows=38 width=230) -> Seq Scan on reg r (cost=0.00..1.38 rows=38 width=230) (17 lignes)
Vue matérialisée
explain select * from mv_agg_csp_par_reg_tra_11 where an_recens = '1968'; QUERY PLAN ----------------------------------------------------------------------------- Seq Scan on mv_agg_csp_par_reg_tra_11 (cost=0.00..27.73 rows=175 width=54) Filter: ((an_recens)::text = '1968'::text) (2 lignes)
Analyse
Comme nous pouvons le voir, pour la requête directe et la requête au travers de la vue, le plan d’exécution est strictement le même.
Le plan d’exécution commun à ces deux modes d’accès, requête directe et vue, le coût global d’exécution est de 247000 unités.
En passant par la vue matérialisée, le coût d’exécution est estimé entre 0 et 28 unités, soit 10000 fois plus rapide que le passage par vue ou requête directe.
Il faut imaginer que sur un datawarehouse, les agrégats peuvent être réclamés de multiples fois par le applications de décision. Ce gain de performance devient vite incontournable.
Je ne rentrerai pas dans le détail du plan d’exécution de la vue ou de la requête directe, car cela nécessiterait d’expliquer le schéma de données sous-jacent, ce qui dépasse largement le cadre de cet article. Toutefois, le schéma de données parcouru est celui constitué avec les tables partitionnées évoquées dans un article précédent.