Normalisation de schéma
Renommer la table en voitures_orig. \ Ne pas la
supprimer (nous en aurons besoin plus tard).
ALTER TABLE voitures rename TO voitures_orig;
Écrire des requêtes permettant d’éclater cette table en trois
tables : \ voitures, caracteristiques et
caracteristiques_voitures. \ (La fonction
regexp_split_to_table permettra de séparer les champs de
caractéristiques.)
CREATE TABLE voitures AS SELECT DISTINCT ON (immatriculation) immatriculation, modeleFROM voitures_orig ;ALTER TABLE voitures ADD PRIMARY KEY (immatriculation);CREATE TABLE caracteristiquesAS SELECT * FROM (SELECT DISTINCT ', ' ) caracteristiqueFROM voitures_orig)AS tmpWHERE caracteristique <> ' ' ;ALTER TABLE caracteristiques ADD PRIMARY KEY (caracteristique);CREATE TABLE caracteristiques_voituresAS SELECT DISTINCT * FROM (SELECT ', ' ) caracteristiqueFROM voitures_origAS tmpWHERE caracteristique <> ' ' ;VACUUM ANALYZE ;
Mettre en place les contraintes d’intégrité : clé primaire sur chaque
table, et clés étrangères. \ Ne pas prévoir encore d’index
supplémentaire. \ Attention : la table de départ
contient des immatriculations en doublon !
Sur caracteristiques_voitures, la clé primaire comprend
les deux colonnes, et donc interdit qu’une même caractéristique soit
présente deux fois sur la même voiture :
ALTER TABLE caracteristiques_voituresADD PRIMARY KEY (immatriculation,caracteristique);Clé étrangère de cette table vers les deux autres tables :
ALTER TABLE caracteristiques_voituresADD FOREIGN KEY (immatriculation)REFERENCES voitures(immatriculation);ALTER TABLE caracteristiques_voituresADD FOREIGN KEY (caracteristique)REFERENCES caracteristiques(caracteristique);
Tenter d’insérer une Clio avec les caractéristiques « ABS »
(majusucules) et « phares LED ».
En toute rigueur il faut le faire dans une transaction :
BEGIN ;INSERT INTO voitures VALUES ('AA-007-JB ' ,'clio ' ) ;INSERT INTO caracteristiques_voitures (immatriculation, caracteristique)VALUES ('AA-007-JB ' ,'ABS ' ) ;INSERT INTO caracteristiques_voitures (immatriculation, caracteristique)VALUES ('AA-007-JB ' ,'phares LED ' ) ;COMMIT ;Évidemment, cela échoue :
En cas d’erreur, c’est exactement ce que l’on veut.
Pour que l’insertion fonctionne, il faut corriger la casse de « ABS »
et déclarer la nouvelle propriété :
BEGIN ;INSERT INTO voitures VALUES ('AA-007-JB ' ,'clio ' ) ;INSERT INTO caracteristiques VALUES ('phares LED ' ) ;INSERT INTO caracteristiques_voitures (immatriculation, caracteristique)VALUES ('AA-007-JB ' ,'abs ' ) ;INSERT INTO caracteristiques_voitures (immatriculation, caracteristique)VALUES ('AA-007-JB ' ,'phares LED ' ) ;COMMIT ;
Comparer les performances entre les deux modèles pour une recherche
des voitures ayant un toit ouvrant.
La version la plus simple est :
SELECT * FROM voitures_origWHERE caracteristiques like '%toit ouvrant% ' ;Plus rigoureusement ([[:>:]] et
[[:<:]] indiquent des frontières de mots.), on
préférera :
EXPLAIN ANALYZE SELECT * FROM voitures_origWHERE caracteristiques ~ E'[[:<:]]toit ouvrant[[:>:]]' ;Toute la table a été parcourue, 91 642 lignes ont été rejetées, 8358
retenues (~8 %). Les estimations statistiques sont correctes.
NB : pour la lisibilité, les plans n’utilisent pas l’option
BUFFERS d’EXPLAIN. Si on l’active, on pourra
vérifier que tous les accès se font bien dans le cache de PostgreSQL
(shared hits).
Avec le nouveau schéma on peut écrire la requête simplement avec une
simple jointure :
SELECT * FROM voituresINNER JOIN caracteristiques_voituresON ( caracteristiques_voitures.immatriculation= voitures.immatriculation)WHERE caracteristique = 'toit ouvrant ' ;Il n’y a pas doublement de lignes si une caractéristique est en
double car la clé primaire l’interdit. Sans cette contrainte, une autre
écriture serait nécessaire :
SELECT * FROM voituresWHERE EXISTS (SELECT 1 FROM caracteristiques_voituresWHERE caracteristiques_voitures.immatriculation= voitures.immatriculationAND caracteristique = 'toit ouvrant ' Dans les deux cas, on obtient ce plan :
Le temps d’exécution est ici plus court malgré un parcours complet de
voitures. PostgreSQL prévoit correctement qu’il ramènera
10 % de cette table, ce qui n’est pas si discriminant et justifie
fréquemment un Seq Scan, surtout que voitures
est petite. caracteristiques_voitures est aussi parcourue
entièrement : faute d’index, il n’y a pas d’autre moyen.
Les plans sont-ils les mêmes si la caractéristique recherchée
n’existe pas ?
Si on cherche une option rare ou n’existant pas, le plan change :
EXPLAIN ANALYZE SELECT * FROM voituresINNER JOIN caracteristiques_voituresON ( caracteristiques_voitures.immatriculation= voitures.immatriculation)WHERE caracteristique = 'ordinateur de bord ' ;Avec un seul résultat attendu, ce qui est beaucoup plus discriminant,
l’utilisation de l’index sur voitures devient
pertinente.
Avec l’ancien schéma, on doit toujours lire la table
voitures_orig en entier.
Indexer la colonne de clé étrangère
caracteristiques_voitures.carateristique et voir ce que
devient le plan de la dernière requête.
CREATE INDEX ON caracteristiques_voitures (caracteristique) ;Le plan d’exécution
devient foudroyant, puisque la table
caracteristiques_voitures n’est plus intégralement
lue :
EXPLAIN ANALYZE SELECT * FROM voituresINNER JOIN caracteristiques_voituresON ( caracteristiques_voitures.immatriculation= voitures.immatriculation)WHERE caracteristique = 'ordinateur de bord ' ;Avec voitures_orig, il existerait aussi des méthodes
d’indexation mais elles sont plus lourdes (index GIN…).
Rechercher une voitures possédant les 3 options ABS, toit ouvrant et
4 roues motrices, et voir le plan.
Si on recherche plusieurs options en même temps, l’optimiseur peut
améliorer les choses en prenant en compte la fréquence de chaque option
pour restreindre plus efficacement les recherches. Le plan devient :
EXPLAIN (ANALYZE , COSTS OFF )SELECT * FROM voituresJOIN caracteristiques_voitures AS cr1 USING (immatriculation)JOIN caracteristiques_voitures AS cr2 USING (immatriculation)JOIN caracteristiques_voitures AS cr3 USING (immatriculation)WHERE cr1.caracteristique = 'toit ouvrant ' AND cr2.caracteristique = 'abs ' AND cr3.caracteristique= '4 roues motrices ' ;Ce plan parcoure deux index, joins leurs résultats, fait de même avec
le résultat de l’index pour la 3è caractéristique, puis opère la
jointure finale avec la table principale par l’index sur
immatriculation (un plan complet indiquerait une estimation
de 56 lignes de résultat, même si le résultat final est de 461
lignes).
Mais les problématiques de performances ne sont pas le plus important
dans ce cas. Ce qu’on gagne réellement, c’est la garantie que les
caractéristiques ne seront que celles existant dans la table
caractéristique, ce qui évite d’avoir à réparer la base
plus tard.
Entité-clé-valeur
Afficher toutes les caractéristiques d’une voiture au hasard (par
exemple ZY-745-KT).
SELECT * FROM voitures_ecvWHERE entite = 'ZY-745-KT ' ;
Trouver toutes les caractéristiques de toutes les voitures ayant un
toit ouvrant dans voitures_ecv. Trier par immatriculation.
Quel est le plan d’exécution ?
Autrement dit : on sélectionne toutes les voitures avec un toit
ouvrant, et l’on veut toutes les caractéristiques de ces voitures. Cela
nécessite d’appeler deux fois la table.
Là encore une jointure de la table avec elle-même sur
entite serait possible, mais serait dangereuse dans les cas
où il y a énormément de propriétés. On préférera encore la version avec
EXISTS, et PostgreSQL en fera spontanément une jointure :
EXPLAIN ANALYZE SELECT * FROM voitures_ecvWHERE EXISTS (SELECT 1 FROM voitures_ecv testWHERE test.entite= voitures_ecv.entiteAND cle = 'toit ouvrant ' AND valeur = true ORDER BY entite ;
Installer l’extension hstore. \ Convertir cette table
pour qu’elle utilise une ligne par immatriculation, avec les
caractéristiques dans un champ hstore. \ Une méthode simple
est de récupérer les lignes d’une même immatriculation avec la fonction
array_agg puis de convertir simplement en champ
hstore.
hstore est normalement présente sur toutes les
installations (ou alors l’administrateur a négligé d’installer le paquet
contrib). Il suffit donc d’une déclaration.
CREATE EXTENSION hstore;CREATE TABLE voitures_hstoreAS SELECT entite AS immatriculation,array_agg (cle),array_agg (valeur):: text []) AS caracteristiquesFROM voitures_ecv group by entite;ALTER TABLE voitures_hstore ADD PRIMARY KEY (immatriculation);
Rechercher la voiture précédente.
SELECT * FROM voitures_hstoreWHERE immatriculation = 'ZY-745-KT ' \gxL’accès à une caractéristique se fait ainsi (attention aux
espaces) :
SELECT immatriculation, caracteristiques -> 'climatisation ' FROM voitures_hstoreWHERE immatriculation = 'ZY-745-KT ' ;
Insérer une voiture avec les caractéristiques
couleur=>vert et phares=>LED.
INSERT INTO voitures_hstoreVALUES ('XX-4456-ZT ' , 'couleur=>vert, phares=>LED ' :: hstore) ;
Définir un index de type GiST sur ce champ hstore. \
Retrouver la voiture insérée par ses caractéristiques.
Les index B-tree classiques sont inadaptés aux types complexes, on
préfère donc un index GiST :
CREATE INDEX voitures_hstore_caracteristiquesON voitures_hstoreUSING gist (caracteristiques);L’opérateur @> signifie « contient » :
SELECT * FROM voitures_hstoreWHERE caracteristiques @> 'couleur=>vert ' AND caracteristiques @> 'phares=>LED ' ;Indexation de champs
tableau
Trouver cette fonction dans la documentation de PostgreSQL (chercher
dans les fonctions de découpage de chaîne de caractères).
La fonction est regexp_split_to_array (sa documentation
est sur https://docs.postgresql.fr/15/functions-matching.html ) :
SELECT immatriculation, modele,', ' )FROM voitures_origLIMIT 10 ;La syntaxe {} est la représentation texte d’un
tableau.
Définir un index fonctionnel sur le résultat de cette fonction, de
type GIN.
CREATE INDEX idx_voitures_array ON voitures_origUSING gin (regexp_split_to_array(caracteristiques,', ' ));
Rechercher toutes les voitures avec toit ouvrant et voir le plan.
EXPLAIN ANALYZE SELECT * FROM voitures_origWHERE regexp_split_to_array(caracteristiques,', ' ) @> '{"toit ouvrant"} ' ;Noter que les estimations de statistiques sont plus délicates sur un
résultat de fonction.
Écrire une requête permettant de récupérer les 10 premiers posts de
l’article d’id_article=12, triés dans l’ordre
de id_post. Il n’y a pas d’index, la requête va être très
lente.
EXPLAIN ANALYZE SELECT * FROM postsWHERE id_article = 12 ORDER BY id_postLIMIT 10 ;Le plan est un
parcours complet de la table, rejetant 4 999 000 lignes et en gardant
1000 lignes, suivi d’un tri :
Créer un index permettant d’améliorer cette requête.
Un index sur id_article améliorerait déjà les choses.
Mais comme on trie sur id_post, il est intéressant de
rajouter aussi cette colonne dans l’index :
CREATE INDEX posts_id_article_id_post ON posts (id_article, id_post);Testons cet index :
EXPLAIN ANALYZE SELECT * FROM postsWHERE id_article = 12 ORDER BY id_postLIMIT 10 ;Le plan
devient :
C’est beaucoup plus rapide : l’index trouve tout de suite les lignes
de l’article cherché, et retourne les enregistrements directement triés
par id_post. On évite de parcourir toute la table, et il
n’y a même pas d’étape de tri (qui serait certes très rapide sur 10
lignes).
Utiliser les clauses LIMIT et OFFSET pour
récupérer les 10 posts suivants. Puis du post 901 au 921. Que
constate-t-on sur le plan d’exécution ?
Les posts 11 à 20 se trouvent rapidement :
EXPLAIN ANALYZE SELECT * FROM postsWHERE id_article = 12 ORDER BY id_postLIMIT 10 OFFSET 10 ;Tout va bien. La requête est à peine plus coûteuse. Noter que l’index
a ramené 20 lignes et non 10.
À partir du post 900 :
EXPLAIN ANALYZE SELECT * FROM postsWHERE id_article = 12 ORDER BY id_postLIMIT 10 OFFSET 900 ;Le plan reste
similaire :
Cette requête est 4 fois plus lente. Si une exécution unitaire ne
pose pas encore problème, des demandes très répétées poseraient
problème. Noter que l’index ramène 910 lignes ! Dans notre exemple
idéalisée, les posts sont bien rangés ensemble, et souvent présents dans
les mêmes blocs. C’est très différent dans une table qui beaucoup
vécu.
Trouver une réécriture de la requête pour trouver directement les
posts 901 à 911 une fois connu le post 900 récupéré au travers de la
pagination.
Pour se mettre dans la condition du test, récupérons l’enregistrement
900 :
SELECT id_article, id_postFROM postsWHERE id_article = 12 ORDER BY id_postLIMIT 1 OFFSET 899 ;(La valeur retournée peut différer sur une autre base.)
Il suffit donc de récupérer les 10 articles pour lesquels
id_article = 12 et id_post > 12900 :
EXPLAIN ANALYZE SELECT * FROM postsWHERE id_article = 12 AND id_post> 12900 ORDER BY id_postLIMIT 10 ;Nous sommes de retour à des temps d’exécution très faibles. Ajouter
la condition sur le id_post permet de limiter à la source
le nombre de lignes à récupérer. L’index n’en renvoie bien que 10.
L’avantage de cette technique par rapport à l’offset est que le temps
d’une requête ne variera que l’on chercher la première ou la millième
page.
L’inconvénient est qu’il faut mémoriser l’id_post où
l’on s’est arrêté sur la page précédente.
Clauses WHERE et pièges
Nous allons maintenant manipuler le champ ts (de type
timestamp) de la table posts.
La requêteSELECT * FROM posts WHERE to_char(ts,'YYYYMM')='201302'
EXPLAIN ANALYZE SELECT * FROM postsWHERE to_char (ts,'YYYYMM ' )= '201302 ' ;Le plan est un
parcours complet de la table :
C’est normal : PostgreSQL ne peut pas deviner que
to_char(ts,'YYYYMM')='201302' veut dire « toutes les dates
du mois de février 2013 ». Une fonction est pour lui une boîte noire, et
il ne voit pas le lien entre le résultat attendu et les données qu’il va
lire.
Ceci est une des causes les plus habituelles de
ralentissement de requêtes : une fonction est appliquée à une
colonne, ce qui rend le filtre incompatible avec l’utilisation d’un
index.
Réécrire la clause WHERE avec une inégalité de dates
pour utiliser l’index sur ts.
C’est à nous d’indiquer une clause WHERE au moteur qu’il
puisse directement appliquer sur notre date :
EXPLAIN ANALYZE SELECT * FROM postsWHERE ts >= '2013-02-01 ' AND ts < '2013-03-01 ' ;Le plan montre que
l’index est maintenant utilisable :
Noter la conversion automatique du critère en
timestamp with time zone.
Plus compliqué : retourner tous les posts ayant eu lieu un dimanche,
en 2013, en passant par un index et en une seule requête.generate_series('2013-01-06 00:00:00','2014-01-01 00:00:00', INTERVAL '7 days'))
Construisons cette requête morceau par morceau. Listons tous les
dimanches de 2013 (le premier dimanche est le 6 janvier) :
SELECT generate_series ('2013-01-06 00:00:00 ' ,'2013-12-31 00:00:00 ' ,INTERVAL '7 days ' S’il faut calculer le premier dimanche de l’année, cela peut se faire
ainsi :
WITH premiersjours AS (SELECT '2000-01-01 ' :: timestamp + i * interval '1 year ' AS jan1FROM generate_series (1 , 30 ) iAS (SELECT jan1,+ mod (13 - extract (dow FROM (jan1 - interval '1 day ' )):: int , 7 )+ interval '1 day ' AS dim1FROM premiersjoursSELECT jan1, dim1FROM dimanches ;On n’a encore que des dates à minuit. Il faut calculer les heures de
début et de fin de chaque dimanche :
SELECT i AS debut,+ INTERVAL '1 day ' AS finFROM generate_series ('2013-01-06 00:00:00 ' ,'2013-12-31 00:00:00 ' ,INTERVAL '7 days ' g (i) ;Il ne nous reste plus qu’à joindre ces deux ensembles. Comme clause
de jointure, on teste la présence de la date du post dans un des
intervalles des dimanches :
EXPLAIN ANALYZE WITH dimanches AS (SELECT i AS debut,+ INTERVAL '1 day ' AS finFROM generate_series ('2013-01-06 00:00:00 ' ,'2013-12-31 00:00:00 ' ,INTERVAL '7 days ' g (i)SELECT posts.* FROM postsJOIN dimanchesON (posts.ts >= dimanches.debut AND posts.ts < dimanches.fin) ;Le plan
devient :
PostgreSQL génère les 52 lignes d’intervalles (noter qu’il ne sait
pas estimer le résultat de cette fonction), puis fait 52 appels à
l’index (noter le loops=52). C’est efficace.
Attention : des inéqui-jointures entraînent
forcément des nested loops (pour chaque ligne d’une table, on
va chercher les lignes d’une autre table). Sur de grands volumes, ce ne
peut pas être efficace. Ici, tout va bien parce que la liste des
dimanches est raisonnablement courte.
On cherche un article à peu près au tiers de la liste avec la requête
suivante. Pourquoi est-elle si lente ?
SELECT * FROM postsWHERE id_article = SELECT max (id_article) * 0.333 FROM postsLe plan est :
Ce plan indique une recherche du numéro d’article maximal (il est
dans l’index ; noter que PostgreSQL restreint à une valeur non vide),
puis il calcule la valeur correspondant au tiers et la met dans
$1. Tout ceci est rapide. La partie lente est le
Seq Scan pour retrouver cette valeur, avec un filtre et non
par l’index.
Le problème est visible sur le filtre même :
(id_article)::numeric signifie que tous les
id_article (des entiers) sont convertis en
numeric pour ensuite être comparés au résultat
(InitPlan 2).col1. Or une conversion est une fonction, ce
qui rend l’index inutilisable. En fait, notre problème est que
(InitPlan 2).col1 n’est pas un entier !
SELECT max (id_article) * 0.333 FROM postsLa conversion du critère en int peut se faire à
plusieurs endroits. Par exemple :
SELECT * FROM postsWHERE id_article = SELECT max (id_article) * 0.333 FROM posts:: int ;Et l’index est donc utilisable immédiatement :
Si l’on avait fait le calcul avec / 3 au lieu de
* 0.333, on n’aurait pas eu le problème, car la division de
deux entiers donne un entier.
Attention donc à la cohérence des types dans vos critères. Le
problème peut se rencontrer même en joignant des int et des
bigint !
