# SQL : Ce qu’il ne faut pas faire
> Mauvaises pratiques de modélisation, contraintes, requêtes et travaux pratiques.
---
Index LLMS : [llms.txt](/fr/llms.txt)
---
# SQL : Ce qu’il ne faut pas faire {#devpg-ch8}
**Figure 8-417-f01.** Figure 8\-417\-f01 \- Diapositive d’ouverture du chapitre 8\.
## Des mauvaises pratiques {#devpg-8-1}
- Modélisation
- Écriture de requêtes
- Conception de l’application
Cette partie présente différents problèmes fréquemment rencontrés et leurs solutions. Elles ont trait aussi bien à des problèmes courants qu’à des mauvaises pratiques.
## Problèmes de modélisation {#devpg-8-2}
- Rappels sur le modèle relationnel
- Formes normales
- Atomicité!
### Que veut dire « relationnel »? {#devpg-8-2-1}
- PostgreSQL est un SGBD-R, un système de gestion de bases de données relationnel
- Le schéma est d’une importance capitale
- « Relationnel » n’est pas « relation entre tables »
- Les tables SONT les relations (entre attributs)
Contrairement à une idée assez fréquemment répandue, le terme relationnel ne désigne pas le fait que les tables soient liées entre elles. Les « tables » SONT les relations. On fait référence ici à l’algèbre relationnelle, inventée en 1970 par Edgar Frank Codd. Les bases de données dites relationnelles n’implémentent habituellement pas exactement cet algèbre, mais en sont très proches. Le langage SQL, entre autres, ne respecte pas l’algèbre relationnelle. Le sujet étant vaste et complexe, il ne sera pas abordé ici. Si vous voulez approfondir le sujet, le livre Introduction aux bases de données de Chris J. Date, est un des meilleurs ouvrages sur l’algèbre relationnelle et les déficiences du langage SQL à ce sujet.
### Quelques rappels sur le modèle relationnel {#devpg-8-2-2}
- Le but est de modéliser un ensemble de faits
- Le modèle relationnel a été introduit à l’époque des bases de données hiérarchiques
- pointeur : incohérence à terme
- formalisme : relations, modélisation évitant les incohérences suite à modification
- formes normales
- Un modèle n’est qu’un modèle : il ne traduit pas la réalité, simplement ce qu’on
souhaite en représenter
- Identifier rapidement les problèmes les plus évidents
Le modèle relationnel est apparu suite à un constat : les bases de données de l’époque (hiérarchiques) reposaient sur la notion de pointeur. Une mise à jour pouvait donc facilement casser le modèle : doublons simples, données pointant sur du « vide », doublons incohérents entre eux, etc. Le modèle relationnel a donc été proposé pour remédier à tous ces problèmes. Un système relationnel repose sur le concept de relation (table en SQL). Une relation est un ensemble de faits. Chaque fait est identifié par un identifiant (clé naturelle). Le fait lie cet identifiant à un certain nombre d’attributs. Une relation ne peut donc pas avoir de doublon. La modélisation relationnelle étant un vaste sujet en soi, nous n’allons pas tout détailler ici, mais plutôt rappeler les points les plus importants.
### Formes normales {#devpg-8-2-3}
Il existe une définition mathématique précise de chacune des 7 formes normales.
- La troisième forme normale peut toujours être atteinte
- La forme suivante (forme normale de Boyce-Codd, ou FNBC) ne peut pas toujours
être atteinte
- La cible est donc habituellement la 3FN
- Chris Date :
- « Chaque attribut dépend de la clé, de TOUTE la clé, et QUE de la clé »
- « The key, the whole key, nothing but the key »
Une relation (table) est en troisième forme normale si tous les attributs (colonnes) dépendent de la clé (primaire), de toute la clé (pas d’un sous-ensemble de ses colonnes), et de rien d’autre que de la clé (une colonne supplémentaire). Si vos tables vérifient déjà ces trois points, votre modélisation est probablement assez bonne. Voir l’article wikipedia 1 présentant l’ensemble des formes normales.
1 https://fr.wikipedia.org/wiki/Forme_normale_(bases_de_donn%C3%A9es_relationnelles)
## Atomicité {#devpg-8-3}
- Un attribut (colonne) doit être atomique :
- Modifier l’attribut sans en toucher un autre
- Donnée correcte (délicat!)
- Recherche efficace : accédé en entier dans une clause WHERE
- Non respect = violation de la première forme normale
L’exemple suivant utilise une table voiture . Si vous n’avez pas déjà une base voitures, les deux tables voitures et voitures_ecv peuvent être téléchargées installées comme suit :
**Exemple 8-421-e01.** Exemple ou sortie extraite du PDF\, page 421\.
```text
createdb voitures
curl -kL https://dali.bo/tp_voitures -o /tmp/voitures.dmp
pg_restore -d voitures /tmp/voitures.dmp
# un message sur le schéma public préexistant est normal
```
Ne pas oublier d’effectuer un VACUUM ANALYZE .
### Atomicité - mauvais exemple {#devpg-8-3-1}
Immatriculation Modèle Caractéristiques
NH-415-DG twingo 4 roues motrices,toit ouvrant, climatisation EO-538-WR clio boite automatique,abs,climatisation
**Exemple 8-421-e02.** Exemple ou sortie extraite du PDF\, page 421\.
```text
INSERT INTO voitures
VALUES ('AD-057-GD','clio','toit ouvrant,abs');
```
Cette modélisation viole la première forme normale (atomicité des attributs). Si on recherche toutes les voitures qui ont l’ABS, on va devoir utiliser une clause WHERE de ce type :
**Exemple 8-421-e03.** Exemple ou sortie extraite du PDF\, page 421\.
```text
SELECT * FROM voitures
WHERE caracteristiques LIKE '%abs%'
```
ce qui sera évidemment très inefficace. Par ailleurs, on n’a évidemment aucun contrôle sur ce qui est mis dans le champ caractéristiques , ce qui est la garantie de données incohérentes au bout de quelques jours (heures?) d’utilisation. Par exemple, rien n’empêche d’ajouter une ligne avec des caractéristiques similaires légèrement différentes, comme « ABS », « boîte automatique ». Ce modèle ne permet donc pas d’assurer la cohérence des données.
### Atomicité - propositions {#devpg-8-3-2}
- Champs dédiés :
**Tableau 8-422-t01.** Tableau ou résultat de commande extrait du PDF\, page 422\.
| Column | Type | Description |
| --- | --- | --- |
| immatriculation | text | Clé primaire |
| modele | text | |
| couleur | color | Couleur vehicule (bleu,rouge,vert) |
| abs | boolean | Option anti-blocage des roues |
| type_roue | boolean | tole/aluminium |
| motricite | boolean | 2 roues motrices / 4 roues motrices |
- Plusieurs valeurs : contrainte CHECK /enum/table de référence
- Beaucoup de champs : clé/valeur (plusieurs formes possibles)
Une alternative plus fiable est de rajouter des colonnes boolean quatre_roues_motrices , boolean abs , varchar couleur . C’est ce qui est à privilégier si le nombre de caractéristiques est fixe et pas trop important. Dans le cas où un simple booléen ne suffit pas, un champ avec une contrainte est possible. Il y a plusieurs méthodes :
- une contrainte simple :
**Exemple 8-422-e01.** Exemple ou sortie extraite du PDF\, page 422\.
```sql
ALTER TABLE voitures ADD COLUMN couleur text
CHECK (couleur IN ('rouge','bleu','vert')) ;
```
- un type « énumération 2 » :
**Exemple 8-422-e02.** Exemple ou sortie extraite du PDF\, page 422\.
```text
CREATE TYPE color AS ENUM ('bleu', 'rouge', 'vert') ;
ALTER TABLE voitures ADD COLUMN couleur color ;
```
(Les énumérations ne sont pas adaptées à des modifications fréquentes et nécessitent parfois un transtypage vers du text ).
- une table de référence avec contrainte, c’est le plus flexible :
**Exemple 8-422-e03.** Exemple ou sortie extraite du PDF\, page 422\.
```sql
CREATE TABLE couleurs (
couleur_id int PRIMARY KEY,
couleur text
) ;
```
**Exemple 8-422-e04.** Exemple ou sortie extraite du PDF\, page 422\.
```text
ALTER TABLE voitures ADD COLUMN couleur_id REFERENCES couleurs ;
```
Ce modèle facilite les recherches et assure la cohérence. L’indexation est facilitée, et les performances ne sont pas dégradées, bien au contraire. Dans le cas où le nombre de propriétés n’est pas aussi bien défini qu’ici, ou est grand, même un modèle clé-valeur dans une associée vaut mieux que l’accumulation de propriétés dans un champ texte. Même une simple table des caractéristiques est plus flexible (voir le TP). 2 https://docs.postgresql.fr/current/datatype-enum.html
Un modèle clé/valeur existe sous plusieurs variantes (table associée, champs hstore ou JSON…) et a ses propres inconvénients, mais il offre au moins plus de flexibilité et de possibilités d’indexation ou de validation des données. Ce sujet est traité plus loin.
## Contraintes absentes {#devpg-8-4}
- Parfois (souvent?) ignorées pour diverses raisons :
- faux gains de performance
- flexibilité du modèle de données
- compatibilité avec d’autres SGBD (MySQL/MyISAM…)
- commodité de développement
Les contraintes d’intégrité et notamment les clés étrangères sont parfois absentes des modèles de données. Les problématiques de performance et de flexibilité sont souvent mises en avant, alors que les contraintes sont justement une aide pour l’optimisation de requêtes par le planificateur, mais surtout une garantie contre de très coûteuses corruption de données logiques. L’absence de contraintes a souvent des conséquences catastrophiques.
### Conséquences de l’absence de contraintes {#devpg-8-4-1}
- Conséquences
- problèmes d’intégrité des données
- fonctions de vérification de cohérence des données
- Les contraintes sont utiles à l’optimiseur :
- déterminent l’unicité des valeurs
- éradiquent des lectures de tables inutiles sur des LEFT JOIN
- utilisent les contraintes CHECK pour exclure une partition
De plus, l’absence de contraintes va également entraîner des problèmes d’intégrité des données. Il est par exemple très compliqué de se prémunir efficacement contre une race condition 3 en l’absence de clé étrangère. Imaginez le scénario suivant :
- la transaction x1 s’assure que la donnée est présente dans la table t1 ;
- la transaction x2 supprime la donnée précédente dans la table t1 ;
- la transaction x1 insère une ligne dans la table t2 faisant référence à la ligne de t1 qu’elle pense
encore présente. Ce cas est très facilement gérable pour un moteur de base de donnée si une clé étrangère existe. Redévelopper ces mêmes contrôles dans la couche applicative sera toujours plus coûteux en terme de performance, voire impossible à faire dans certains cas sans passer par la base de donnée elle-même (multiples serveurs applicatifs accédant à la même base de donnée). 3 Situation où deux sessions ou plus modifient des données en tables au même moment.
Il peut s’ensuivre des calculs d’agrégats faux et des problèmes applicatifs de toute sorte. Souvent, plutôt que de corriger le modèle de données, des fonctions de vérification de la cohérence des données seront mises en place, entraînant ainsi un travail supplémentaire pour trouver et corriger les incohérences. Lorsque ces problèmes d’intégrité seront détectés, il s’en suivra également la création de procédures de vérification de cohérence des données qui vont aussi alourdir les développements, entraînant ainsi un travail supplémentaire pour trouver et corriger les incohérences. Ce qui a été gagné d’un côté est perdu de l’autre, mais sous une forme différente. De plus, les contraintes d’intégrité sont des informations qui garantissent non seulement la cohérence des données mais qui vont également influencer l’optimiseur dans ses choix de plans d’exécution. Parmi les informations utilisées par l’optimiseur, les contraintes d’unicité permettent de déterminer sans difficulté la répartition des valeurs stockées dans une colonne : chaque valeur est simplement unique. L’utilisation des index sur ces colonnes sera donc probablement favorisée. Les contraintes d’intégrité permettent également à l’optimiseur de pouvoir éliminer des jointures inutiles avec un
**Exemple 8-425-e01.** Exemple ou sortie extraite du PDF\, page 425\.
```text
LEFT JOIN . Enfin, les contraintes CHECK sur des tables partitionnées permettent de cibler les lec-
```
tures sur certaines partitions seulement, et donc d’exclure les partitions inutiles.
### Suspension des contraintes le temps d’une transaction {#devpg-8-4-2}
- Solution :
- contraintes DEFERRABLE !
Parfois, les clés étrangères sont supprimées simplement parce que des transactions sont en erreur car des données sont insérées dans une table fille sans avoir alimenté la table mère. Des identifiants de clés étrangères de la table fille sont absents de la table mère, entraînant l’arrêt en erreur de la transaction. Il est possible de contourner cela en différant la vérification des contraintes d’intégrité à la fin de la transaction Une contrainte DEFERRABLE associée à un SET CONSTRAINT … DEFERRED n’est vérifiée que lors du
**Exemple 8-425-e02.** Exemple ou sortie extraite du PDF\, page 425\.
```text
COMMIT . Elle ne gêne donc pas le développeur, qui peut insérer les données dans l’ordre qu’il veut
```
ou insérer temporairement des données incohérentes. Ce qui compte est que la situation soit saine à la fin de la transaction, quand les données seront enregistrées et deviendront visibles par les autres sessions. L’exemple ci-dessous montre l’utilisation de la vérification des contraintes d’intégrité en fin de transaction.
**Exemple 8-425-e03.** Exemple ou sortie extraite du PDF\, page 425\.
```text
CREATE TABLE mere (id integer, t text);
```
**Exemple 8-425-e04.** Exemple ou sortie extraite du PDF\, page 425\.
```text
CREATE TABLE fille (id integer, mere_id integer, t text);
```
**Exemple 8-425-e05.** Exemple ou sortie extraite du PDF\, page 425\.
```text
ALTER TABLE mere ADD CONSTRAINT pk_mere PRIMARY KEY (id);
```
**Exemple 8-425-e06.** Exemple ou sortie extraite du PDF\, page 425\.
```text
ALTER TABLE fille
```
ADD CONSTRAINT fk_mere_fille FOREIGN KEY (mere_id) REFERENCES mere (id) MATCH FULL
**Exemple 8-426-e01.** Exemple ou sortie extraite du PDF\, page 426\.
```text
ON UPDATE NO ACTION
ON DELETE CASCADE
DEFERRABLE;
```
La transaction insère d’abord les données dans la table fille, puis ensuite dans la table mère :
**Exemple 8-426-e02.** Exemple ou sortie extraite du PDF\, page 426\.
```text
BEGIN ;
SET CONSTRAINTS ALL DEFERRED ;
```
**Exemple 8-426-e03.** Exemple ou sortie extraite du PDF\, page 426\.
```text
INSERT INTO fille (id, mere_id, t) VALUES (1, 1, 'val1');
INSERT INTO fille (id, mere_id, t) VALUES (2, 2, 'val2');
```
**Exemple 8-426-e04.** Exemple ou sortie extraite du PDF\, page 426\.
```text
INSERT INTO mere (id, t) VALUES (1, 'val1'), (2, 'val2');
```
**Exemple 8-426-e05.** Exemple ou sortie extraite du PDF\, page 426\.
```text
COMMIT;
```
Sans le SET CONSTRAINTS ALL DEFERRED , le premier ordre serait tombé en erreur.
## Stockage Entité-Clé-Valeur {#devpg-8-5}
- Entité-Attribut-Valeur (ou Entité-Clé-Valeur)
- Quel but ?
- flexibilité du modèle de données
- adapter sans délai ni surcoût le modèle de données
- Conséquences :
- création d’une table : identifiant / nom_attribut / valeur
- requêtes abominables et coûteuses
Le modèle relationnel a été critiqué depuis sa création pour son manque de souplesse pour ajouter de nouveaux attributs ou pour proposer plusieurs attributs sans pour autant nécessiter de redévelopper l’application. La solution souvent retenue est d’utiliser une table « à tout faire » entité-attribut-valeur qui est associée à une autre table de la base de données. Techniquement, une telle table comporte trois colonnes. La première est un identifiant généré qui permet de référencer la table mère. Les deux autres colonnes stockent le nom de l’attribut représenté et la valeur représentée. Ainsi, pour reprendre l’exemple des informations de contacts pour un individu, une table personnes permet de stocker un identifiant de personne. Une table personne_attributs permet d’associer des données à un identifiant de personne. Le type de données de la colonne est souvent prévu largement pour faire tenir tout type d’informations, mais sous forme textuelle. Les données ne peuvent donc pas être validées.
**Exemple 8-427-e01.** Exemple ou sortie extraite du PDF\, page 427\.
```text
CREATE TABLE personnes (id SERIAL PRIMARY KEY);
```
**Exemple 8-427-e02.** Exemple ou sortie extraite du PDF\, page 427\.
```text
CREATE TABLE personne_attributs (
id_pers INTEGER NOT NULL,
nom_attr varchar(20) NOT NULL,
val_attr varchar(100) NOT NULL
);
```
**Exemple 8-427-e03.** Exemple ou sortie extraite du PDF\, page 427\.
```text
INSERT INTO personnes (id) VALUES (nextval('personnes_id_seq')) RETURNING id;
id
```
**Exemple 8-427-e04.** Exemple ou sortie extraite du PDF\, page 427\.
```text
----
1
```
**Exemple 8-427-e05.** Exemple ou sortie extraite du PDF\, page 427\.
```text
INSERT INTO personne_attributs (id_pers, nom_attr, val_attr)
VALUES (1, 'nom', 'Prunelle'),
(1, 'prenom', 'Léon');
(...)
```
Un tel modèle peut sembler souple mais pose plusieurs problèmes. Le premier concerne l’intégrité des données. Il n’est pas possible de garantir la présence d’un attribut comme on le ferait avec une contrainte NOT NULL . Si l’on souhaite stocker des données dans un autre format qu’une chaîne de caractère, pour bénéficier des contrôles de la base de données sur ce type, la seule solution est de
créer autant de colonnes d’attributs qu’il y a de types de données à représenter. Ces colonnes ne permettront pas d’utiliser des contraintes CHECK pour garantir la cohérence des valeurs stockées avec ce qui est attendu, car les attributs peuvent stocker n’importe quelle donnée.
### Stockage Entité-Clé-Valeur : exemple {#devpg-8-5-1}
Comment lister tous les DBA ?
id_pers nom_attr val_attr
1 nom Prunelle 1 prenom Léon 1 telephone 0123456789 1 fonction dba
### Stockage Entité-Clé-Valeur : requête associée {#devpg-8-5-2}
**Exemple 8-428-e01.** Exemple ou sortie extraite du PDF\, page 428\.
```text
SELECT id, att_nom.val_attr AS nom,
att_prenom.val_attr AS prenom,
att_telephone.val_attr AS tel
FROM personnes p
JOIN personne_attributs AS att_nom
ON (p.id=att_nom.id_pers AND att_nom.nom_attr='nom')
JOIN personne_attributs AS att_prenom
ON (p.id=att_prenom.id_pers AND att_prenom.nom_attr='prenom')
JOIN personne_attributs AS att_telephone
ON (p.id=att_telephone.id_pers AND att_telephone.nom_attr='telephone')
JOIN personne_attributs AS att_fonction
ON (p.id=att_fonction.id_pers AND att_fonction.nom_attr='fonction')
WHERE att_fonction.val_attr='dba';
```
Les requêtes SQL qui permettent de récupérer les données requises dans l’application sont également particulièrement lourdes à écrire et à maintenir, à moins de récupérer les données attribut par attribut.
Des problèmes de performances vont donc très rapidement se poser. Cette représentation des données entraîne souvent l’effondrement des performances d’une base de données relationnelle. Les requêtes sont difficilement optimisables et nécessitent de réaliser beaucoup d’entrées-sorties disque, car les données sont éparpillées un peu partout dans la table.
### Stockage Entité-Clé-Valeur, hstore, JSON {#devpg-8-5-3}
- Solutions :
- revenir sur la conception du modèle de données
- utiliser un type de données plus adapté : hstore , jsonb
- On économise jointures et place disque.
Lorsque de telles solutions sont déployées pour stocker des données transactionnelles, il vaut mieux revenir à un modèle de données traditionnel qui permet de typer correctement les données, de mettre en place les contraintes d’intégrité adéquates et d’écrire des requêtes SQL efficaces. Dans d’autres cas où le nombre de champs est vraiment élevé et variable, il vaut mieux utiliser un type de données de PostgreSQL qui est approprié, comme hstore qui permet de stocker des données sous la forme clé->valeur . On conserve ainsi l’intégrité des données (on n’a qu’une ligne par personne), on évite de très nombreuses jointures source d’erreurs et de ralentissements, et même de la place disque. De plus, ce type de données peut être indexé pour garantir de bons temps de réponses des requêtes qui nécessitent des recherches sur certaines clés ou certaines valeurs. Voici l’exemple précédent revu avec l’extension hstore :
**Exemple 8-429-e01.** Exemple ou sortie extraite du PDF\, page 429\.
```text
CREATE EXTENSION hstore;
CREATE TABLE personnes (id SERIAL PRIMARY KEY, attributs hstore);
```
**Exemple 8-429-e02.** Exemple ou sortie extraite du PDF\, page 429\.
```text
INSERT INTO personnes (attributs) VALUES ('nom=>Prunelle, prenom=>Léon');
INSERT INTO personnes (attributs) VALUES ('prenom=>Gaston,nom=>Lagaffe');
INSERT INTO personnes (attributs) VALUES ('nom=>DeMaesmaker');
```
**Exemple 8-429-e03.** Exemple ou sortie extraite du PDF\, page 429\.
```text
SELECT * FROM personnes;
```
**Tableau 8-429-t01.** Tableau ou résultat de commande extrait du PDF\, page 429\.
| id | attributs |
| --- | --- |
| 1 | "nom"=>"Prunelle", "prenom"=>"Léon" |
| 2 | "nom"=>"Lagaffe", "prenom"=>"Gaston" |
| 3 | "nom"=>"DeMaesmaker" |
**Exemple 8-429-e04.** Exemple ou sortie extraite du PDF\, page 429\.
```text
SELECT id, attributs->'prenom' AS prenom FROM personnes;
```
**Tableau 8-429-t02.** Tableau ou résultat de commande extrait du PDF\, page 429\.
| id | prenom |
| --- | --- |
| 1 | Léon |
| 2 | Gaston |
| 3 | |
**Exemple 8-429-e05.** Exemple ou sortie extraite du PDF\, page 429\.
```text
SELECT id, attributs->'nom' AS nom FROM personnes;
```
**Tableau 8-429-t03.** Tableau ou résultat de commande extrait du PDF\, page 429\.
| id | nom |
| --- | --- |
| 1 | Prunelle |
| 2 | Lagaffe |
| 3 | DeMaesmaker |
Le principe du JSON est similaire.
## Attributs multicolonnes {#devpg-8-6}
- Pourquoi
- stocker plusieurs attributs pour une même ligne
- exemple : les différents numéros de téléphone d’une personne
- Pratique courante
- ex : telephone_1 , telephone_2
- Conséquences
- et s’il faut rajouter encore une colonne?
- maîtrise de l’unicité des valeurs?
- requêtes complexes à maintenir
- Solutions
- créer une table dépendante
- ou un type tableau
Dans certains cas, le modèle de données doit être étendu pour pouvoir stocker des données complémentaires. Un exemple typique est une table qui stocke les informations pour contacter une personne. Une table personnes ou contacts possède une colonne telephone qui permet de stocker le numéro de téléphone d’une personne. Or, une personne peut disposer de plusieurs numéros. Le premier réflexe est souvent de créer une seconde colonne telephone_2 pour stocker un numéro de téléphone complémentaire. S’en suit une colonne telephone_3 voire telephone_4 en fonction des besoins. Dans de tels cas, les requêtes deviennent plus complexes à maintenir et il est difficile de garantir l’unicité des valeurs stockées pour une personne car l’écriture des contraintes d’intégrité devient de plus en plus complexe au fur et à mesure que l’on ajoute une colonne pour stocker un numéro. La solution la plus pérenne pour gérer ce cas de figure est de créer une table de dépendance qui est dédiée au stockage des numéros de téléphone. Ainsi, la table personnes ne portera plus de colonnes telephone , mais une table telephones portera un identifiant référençant une personne et un numéro de téléphone. Ainsi, si une personne dispose de trois, quatre… numéros de téléphone, la table telephones comportera autant de lignes qu’il y a de numéros pour une personne.
Les différents numéros de téléphone seront obtenus par jointure entre la table personnes et la table telephones . L’application se chargera de l’affichage.
Ci-dessous, un exemple d’implémentation du problème où une table telephones dans laquelle plusieurs numéros seront stockés sur plusieurs lignes plutôt que dans plusieurs colonnes.
**Exemple 8-431-e01.** Exemple ou sortie extraite du PDF\, page 431\.
```text
CREATE TABLE personnes (
per_id SERIAL PRIMARY KEY, nom VARCHAR(50) NOT NULL, pnom VARCHAR(50) NOT NULL,
```
**Exemple 8-431-e02.** Exemple ou sortie extraite du PDF\, page 431\.
```text
...
);
```
**Exemple 8-432-e01.** Exemple ou sortie extraite du PDF\, page 432\.
```text
CREATE TABLE telephones (
per_id INTEGER NOT NULL, numero VARCHAR(20), PRIMARY KEY (per_id, numero), FOREIGN KEY (per_id) REFERENCES personnes (per_id) );
```
L’unicité des valeurs sera garantie à l’aide d’une contrainte d’unicité posée sur l’identifiant per_id et le numéro de téléphone. Une autre solution consiste à utiliser un tableau pour représenter cette information. D’un point de vue conceptuel, le lien entre une personne et son ou ses numéros de téléphone est plus une « composition » qu’une réelle « relation » : le numéro de téléphone ne nous intéresse pas en tant que tel, mais uniquement en tant que détail d’une personne. On n’accédera jamais à un numéro de téléphone séparément : la table telephones donnée plus haut n’a pas de clé « naturelle », un simple rattachement à la table personnes par l’identifiant de la personne. Sans même parler de partitionnement, on gagnerait donc en performances en stockant directement les numéros de téléphone dans la table personnes , ce qui est parfaitement faisable sous PostgreSQL :
**Exemple 8-432-e02.** Exemple ou sortie extraite du PDF\, page 432\.
```text
CREATE TABLE personnes (
per_id SERIAL PRIMARY KEY, nom VARCHAR(50) NOT NULL, pnom VARCHAR(50) NOT NULL, numero VARCHAR(20)[] );
```
**Exemple 8-432-e03.** Exemple ou sortie extraite du PDF\, page 432\.
```text
-- Ajout d'une personne
INSERT INTO personnes (nom, pnom, numero)
VALUES ('Simpson', 'Omer', '{0607080910}');
```
**Exemple 8-432-e04.** Exemple ou sortie extraite du PDF\, page 432\.
```text
SELECT *
FROM personnes;
```
**Tableau 8-432-t01.** Tableau ou résultat de commande extrait du PDF\, page 432\.
| per_id | nom | pnom | numero |
| --- | --- | --- | --- |
| 1 | Simpson | Omer | {0607080910} |
**Exemple 8-432-e05.** Exemple ou sortie extraite du PDF\, page 432\.
```text
-- Ajout d'un numéro de téléphone pour une personne donnée :
UPDATE personnes
SET numero = numero || '{0102030420}'
WHERE per_id = 1;
```
**Exemple 8-432-e06.** Exemple ou sortie extraite du PDF\, page 432\.
```text
-- Vérification de l'ajout :
SELECT * FROM personnes;
```
**Tableau 8-432-t02.** Tableau ou résultat de commande extrait du PDF\, page 432\.
| per_id | nom | pnom | numero |
| --- | --- | --- | --- |
| 1 | Simpson | Omer | {0607080910,0102030420} |
**Exemple 8-432-e07.** Exemple ou sortie extraite du PDF\, page 432\.
```text
-- Séparation des éléments du tableau :
SELECT per_id, nom, pnom, unnest(numero) AS numero
FROM personnes;
```
**Tableau 8-432-t03.** Tableau ou résultat de commande extrait du PDF\, page 432\.
| per_id | nom | pnom | numero |
| --- | --- | --- | --- |
**Tableau 8-433-t01.** Tableau ou résultat de commande extrait du PDF\, page 433\.
| Colonne 1 | Colonne 2 | Colonne 3 | Colonne 4 |
| --- | --- | --- | --- |
| 1 | Simpson | Omer | 0607080910 |
| 1 | Simpson | Omer | 0102030420 |
## Nombreuses lignes de peu de colonnes {#devpg-8-7}
- Énormément de lignes, peu de colonnes
- Cas typique : séries temporelles
- Volumétrie augmentée par les entêtes
- Regrouper les valeurs dans un ARRAY ou un type composite
- Partitionner
Certaines applications, typiquement celles récupérant des données temporelles, stockent peu de colonnes (parfois juste date, capteur, valeur…) mais énormément de lignes. Dans le modèle MVCC de PostgreSQL, chaque ligne utilise au bas mot 23 octets pour stocker xmin , xmax et les autres informations de maintenance de la ligne. On peut donc se retrouver avec un overhead représentant la majorité de la table. Cela peut avoir un fort impact sur la volumétrie :
**Exemple 8-434-e01.** Exemple ou sortie extraite du PDF\, page 434\.
```text
CREATE TABLE valeurs_capteur (d timestamp, v smallint);
-- soit 8 + 2 = 10 octets de données utiles par ligne
```
**Exemple 8-434-e02.** Exemple ou sortie extraite du PDF\, page 434\.
```text
-- 100 valeurs chaque seconde pendant 100 000 s = 10 millions de lignes
INSERT INTO valeurs_capteur (d, v)
SELECT current_timestamp + (i%100000) * interval '1 s',
(random()*200)::smallint
FROM generate_series (1,10000000) i ;
```
**Exemple 8-434-e03.** Exemple ou sortie extraite du PDF\, page 434\.
```text
SELECT pg_size_pretty(pg_relation_size ('valeurs_capteur')) ;
pg_size_pretty
```
**Exemple 8-434-e04.** Exemple ou sortie extraite du PDF\, page 434\.
```text
----------------
422 MB
```
**Exemple 8-434-e05.** Exemple ou sortie extraite du PDF\, page 434\.
```text
-- dont seulement 10 octets * 10 Mlignes = 100 Mo de données utiles
```
Il est parfois possible de regrouper les valeurs sur une même ligne au sein d’un ARRAY , ici pour chaque seconde :
**Exemple 8-434-e06.** Exemple ou sortie extraite du PDF\, page 434\.
```text
CREATE TABLE valeurs_capteur_2 (d timestamp, tv smallint[]);
```
**Exemple 8-434-e07.** Exemple ou sortie extraite du PDF\, page 434\.
```text
INSERT INTO valeurs_capteur_2
SELECT current_timestamp+ (i%100000) * interval '1 s' ,
array_agg((random()*200)::smallint)
```
**Exemple 8-434-e08.** Exemple ou sortie extraite du PDF\, page 434\.
```text
FROM generate_series (1,10000000) i
GROUP BY 1 ;
```
**Exemple 8-434-e09.** Exemple ou sortie extraite du PDF\, page 434\.
```text
SELECT pg_size_pretty(pg_relation_size ('valeurs_capteur_2'));
pg_size_pretty
```
**Exemple 8-434-e10.** Exemple ou sortie extraite du PDF\, page 434\.
```text
----------------
25 MB
```
**Exemple 8-434-e11.** Exemple ou sortie extraite du PDF\, page 434\.
```text
-- soit par ligne :
-- 23 octets d'entête + 8 pour la date + 100 * 2 octets de valeurs smallint
```
Dans cet exemple, on économise la plupart des entêtes de ligne, mais aussi les données redondantes (la date), et le coût de l’alignement des champs. Avec suffisamment de valeurs à stocker, une partie des données peut même se retrouver compressée dans la partie TOAST de la table. La récupération des données se fait de manière à peine moins simple :
**Exemple 8-435-e01.** Exemple ou sortie extraite du PDF\, page 435\.
```text
SELECT unnest(tv) FROM valeurs_capteur_2
WHERE d = '2018-06-15 22:07:47.651295' ;
```
L’indexation des valeurs à l’intérieur du tableau nécessite un index GIN :
**Exemple 8-435-e02.** Exemple ou sortie extraite du PDF\, page 435\.
```text
CREATE INDEX tvx ON valeurs_capteur_2 USING gin(tv);
```
**Exemple 8-435-e03.** Exemple ou sortie extraite du PDF\, page 435\.
```text
EXPLAIN (ANALYZE) SELECT * FROM valeurs_capteur_2 WHERE '{199}' && tv ;
```
**Exemple 8-435-e04.** Exemple ou sortie extraite du PDF\, page 435\.
```text
QUERY PLAN
---------------------------------------------------------------------------------
Bitmap Heap Scan on valeurs_capteur_2 (cost=311.60..1134.20 rows=40000 width=232)
(actual time=8.299..20.460 rows=39792 loops=1)
Recheck Cond: ('{199}'::smallint[] && tv)
Heap Blocks: exact=3226
```
**Exemple 8-435-e05.** Exemple ou sortie extraite du PDF\, page 435\.
```text
-> Bitmap Index Scan on tvx (cost=0.00..301.60 rows=40000 width=0)
(actual time=7.723..7.723 rows=39792 loops=1)
Index Cond: ('{199}'::smallint[] && tv)
Planning time: 0.214 ms
Execution time: 22.386 ms
```
Évidemment cette technique est à réserver aux cas où les données mises en tableau sont insérées et mises à jour ensemble. Le maniement des tableaux est détaillé dans la documentation officielle 4 . Tout cela est détaillé et mesuré dans ce billet de Julien Rouhaud 5 . Il évoque aussi le cas de structures plus complexes : au lieu d’un hstore ou d’un ARRAY , on peut utiliser un type qui regroupe les différentes valeurs. Une autre option, complémentaire, est le partitionnement. Il peut être géré manuellement (tables générées par l’applicatif, par date et/ou par source de données…) ou profiter des deux modes de partitionnement de PostgreSQL. Il n’affectera pas la volumétrie totale mais permet de gérer des partitions plus maniables. Il a aussi l’intérêt de ne pas nécessiter de modification du code pour lire les données.
4 https://www.postgresql.org/docs/current/static/arrays.html 5 https://rjuju.github.io/postgresql/2016/09/16/minimizing-tuple-overhead.html
## Tables aux très nombreuses colonnes {#devpg-8-8}
Tables à plusieurs dizaines, voire centaines de colonnes :
- Les entités sont certainement trop grosses dans la modélisation
- Il y a probablement dépendance entre certaines colonnes (Only the key)
- On accède à beaucoup d’attributs inutiles (tout est stocké au même endroit)
Il arrive régulièrement de rencontrer des tables ayant énormément de colonnes (souvent à NULL d’ailleurs). Cela signifie qu’on modélise une entité ayant tous ces attributs (centaines d’attributs). Il est très possible que cette entité soit en fait composée de « sous-entités », qu’on pourrait modéliser séparément. On peut évidemment trouver des cas particuliers contraires, mais une table de ce type reste un bon indice. Surtout si vous trouvez dans les dernières colonnes des attributs comme attribut_supplementaire_1 …
## Choix d’un type numérique {#devpg-8-9}
- Pour : représenter des valeurs décimales
- Pratique courante :
- real ou double ( float )
- money
- … erreurs d’arrondis!
- Solution :
- numeric pour les calculs précis (financiers notamment)
Certaines applications scientifiques se contentent de types flottants standards, car ils permettent d’encoder des valeurs plus importantes que les types entiers standards. En pratique, les types float(x) correspondent aux types real ou double precision de PostgreSQL.
Néanmoins, les types flottants sont peu précis, notamment pour les applications financières où une erreur d’arrondi n’est pas tolérable. Par exemple :
**Exemple 8-437-e01.** Exemple ou sortie extraite du PDF\, page 437\.
```text
test=# CREATE TABLE comptes (compte_id serial PRIMARY KEY, solde float);
CREATE TABLE
test=# INSERT INTO comptes (solde) VALUES (100000000.1), (10.1), (10000.2), (100000000000000.1);
```
**Exemple 8-437-e02.** Exemple ou sortie extraite du PDF\, page 437\.
```text
INSERT 0 4
test=# SELECT SUM(solde) FROM comptes; sum
```
**Exemple 8-437-e03.** Exemple ou sortie extraite du PDF\, page 437\.
```text
-----------------
100000100010010
```
Le type numeric est alors généralement conseillé. Sa valeur est exacte et les calculs sont justes.
**Exemple 8-437-e04.** Exemple ou sortie extraite du PDF\, page 437\.
```text
test=# CREATE TABLE comptes (compte_id serial PRIMARY KEY, solde numeric);
CREATE TABLE
test=# INSERT INTO comptes (solde) VALUES (100000000.1), (10.1), (10000.2), (100000000000000.1);
```
**Exemple 8-437-e05.** Exemple ou sortie extraite du PDF\, page 437\.
```text
INSERT 0 4
test=# SELECT SUM(solde) FROM comptes; sum
```
**Exemple 8-437-e06.** Exemple ou sortie extraite du PDF\, page 437\.
```text
-------------------
100000100010010.5
```
numeric (sans autre indication de précision) autorise même un calcul exact sans arrondi avec des ordres de grandeur très différents; comme SELECT 1e9999 + 1e-9999 ; . Paradoxalement, le type money n’est pas adapté aux montants financiers : sa manipulation implique de convertir en numeric pour éviter des erreurs d’arrondis. Autant utiliser directement numeric : si l’on ne mentionne pas la précision, elle est exacte.
Le type numeric paye sa précision par un stockage parfois plus important et par des calculs plus lents que ceux des types natifs comme les intX et les floatX . Pour plus de détails, voir la documentation officielle :
- types à virgule flottante 6 ;
- type monétaire 7 ;
- type à prévision arbitraire 8 .
6 https://docs.postgresql.fr/current/datatype-numeric.html#DATATYPE-FLOAT 7 https://docs.postgresql.fr/current/datatype-money.html 8 https://docs.postgresql.fr/current/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
## Colonne de type variable {#devpg-8-10}
Plus rarement, on rencontre aussi :
- Une colonne de type varchar contenant
- quelquefois un entier
- quelquefois une date
- un NULL
- une chaîne autre
- etc.
- À éviter comme la peste!
- Plusieurs sens = plusieurs champs
On rencontre parfois ce genre de choses :
Immatriculation Camion Numero de tournee
TP-108-AX 12 TF-112-IR ANNULÉE
avec bien sûr une table tournée décrivant la tournée elle-même, avec une clé technique numérique. Cela pose un gros problème de modélisation : la colonne a un type de contenu qui dépend de l’information qu’elle contient. On va aussi avoir un problème de performance en joignant cette chaîne à la clé numérique de la table tournée . Le moteur n’aura que deux choix : convertir la chaîne en numérique, avec une exception à la clé en essayant de convertir « ANNULÉE », ou bien (ce qu’il fera) convertir le numérique de la table tournee en chaîne. Cette dernière méthode rendra l’accès à l’identifiant de tournée par index impossible. D’où un parcours complet (Seq Scan) de la table tournée à chaque accès et des performances qui décroissent au fur et à mesure que la table grossit. La solution est une supplémentaire (un booléen tournee_ok par exemple). Un autre classique est le champ date stocké au format texte. Le format correct de cette date ne peut être garanti par la base, ce qui mène systématiquement à des erreurs de conversion si un humain est impliqué. Dans un environnement international où l’on mélange DD-MM-YYYY et MM-DD-YYYY, un rattrapage manuel est même illusoire. Les calculs de date sont évidemment impossibles.
## Problèmes courants d’écriture de requêtes {#devpg-8-11}
- Utilisation de NULL
- Ordre implicite des colonnes
- Requêtes spaghetti
- Moteur de recherche avec LIKE
Le langage SQL est généralement méconnu, ce qui amène à l’écriture de requêtes peu performantes, voire peu pérennes.
## NULL {#devpg-8-12}
- NULL signifie habituellement :
- Valeur non renseignée
- Valeur inconnue
- Absence d’information
- Une table remplie de NULL est habituellement signe d’un problème de modélisation.
- NOT NULL recommandé
Une table qui contient majoritairement des valeurs NULL contient bien peu de faits utilisables. La plupart du temps, c’est une table dans laquelle on stocke beaucoup de choses n’ayant que peu de rapport entre elles, les champs étant renseignés suivant le type de chaque « chose ». C’est donc le plus souvent un signe de mauvaise modélisation. Cette table aurait certainement dû être éclatée en plusieurs tables, chacune représentant une des relations qu’on veut modéliser. Il est donc recommandé que tous les attributs d’une table portent une contrainte NOT NULL . Quelques colonnes peuvent ne pas porter ce type de contraintes, mais elles doivent être une exception. En effet, le comportement de la base de données est souvent source de problèmes lorsqu’une valeur NULL entre en jeu. Par exemple, la concaténation d’une chaîne de caractères avec une valeur NULL retourne une valeur NULL , car elle est propagée dans les calculs. D’autres types de problèmes apparaissent également pour les prédicats. Il faut avoir à l’esprit cette citation de Chris Date :
« La valeur NULL telle qu’elle est implémentée dans SQL peut poser plus de problèmes qu’elle n’en résout. Son comportement est parfois étrange et est source de nombreuses erreurs et de confusions. »
Il ne ne s’agit pas de remplacer ce NULL par des valeurs « magiques » (par exemple -1 pour « Non renseigné » , cela ne ferait que complexifier le code) mais de se demander si NULL a une vraie signification.
## Ordre implicite des colonnes {#devpg-8-13}
- Objectif
- s’économiser d’écrire la liste des colonnes dans une requête
- Problèmes
- si l’ordre des colonnes change, les résultats changent
- résultats faux
- données corrompues
- Solutions
- nommer les colonnes impliquées
Le langage SQL permet de s’appuyer sur l’ordre physique des colonnes d’une table. Or, faire confiance à la base de données pour conserver cet ordre physique peut entraîner de graves problèmes applicatifs en cas de changements. Dans le meilleur des cas, l’application ne fonctionnera plus, ce qui permet d’éviter les corruptions de données silencieuses, où une colonne prend des valeurs destinées normalement à être stockées dans une autre colonne. Si l’application continue de fonctionner, elle va générer des résultats faux et des incohérences d’affichage. Par exemple, l’ordre des colonnes peut changer notamment lorsque certains ETL sont utilisés pour modifier le type d’une colonne varchar(10) en varchar(11) . Par exemple, pour la colonne username , l’ETL Kettle génère les ordres suivants :
**Exemple 8-442-e01.** Exemple ou sortie extraite du PDF\, page 442\.
```text
ALTER TABLE utilisateurs ADD COLUMN username_KTL VARCHAR(11);
UPDATE utilisateurs SET username_KTL=username;
ALTER TABLE utilisateurs DROP COLUMN username;
ALTER TABLE utilisateurs RENAME username_KTL TO username
```
Il génère des ordres SQL inutiles et consommateurs d’entrées/sorties disques car il doit générer des ordres SQL compris par tous les SGBD du marché. Or, tous les SGBD ne permettent pas de changer le type d’une colonne aussi simplement que dans PostgreSQL. PostgreSQL, lui, ne permet pas de changer l’ordre d’apparition des colonnes. C’est pourquoi il est préférable de lister explicitement les colonnes dans les ordres INSERT et
**Exemple 8-442-e02.** Exemple ou sortie extraite du PDF\, page 442\.
```text
SELECT , afin de garder un ordre d’insertion déterministe.
```
Exemples Exemple de modification du schéma pouvant entraîner des problèmes d’insertion si les colonnes ne sont pas listées explicitement :
**Exemple 8-442-e03.** Exemple ou sortie extraite du PDF\, page 442\.
```text
CREATE TABLE insere (id integer PRIMARY KEY, col1 varchar(5), col2 integer);
```
**Exemple 8-442-e04.** Exemple ou sortie extraite du PDF\, page 442\.
```text
INSERT INTO insere VALUES (1, 'XX', 10);
```
**Exemple 8-442-e05.** Exemple ou sortie extraite du PDF\, page 442\.
```text
SELECT * FROM insere ;
```
**Tableau 8-442-t01.** Tableau ou résultat de commande extrait du PDF\, page 442\.
| id | col1 | col2 |
| --- | --- | --- |
| 1 | XX | 10 |
**Exemple 8-443-e01.** Exemple ou sortie extraite du PDF\, page 443\.
```text
ALTER TABLE insere ADD COLUMN col1_tmp varchar(6);
UPDATE insere SET col1_tmp = col1;
ALTER TABLE insere DROP COLUMN col1;
ALTER TABLE insere RENAME COLUMN col1_tmp TO col1;
```
**Exemple 8-443-e02.** Exemple ou sortie extraite du PDF\, page 443\.
```text
INSERT INTO insere VALUES (2, 'XXX', 10);
ERROR: invalid input syntax for integer: "XXX" LINE 1: INSERT INTO insere VALUES (2, 'XXX', 10); ^
```
**Exemple 8-443-e03.** Exemple ou sortie extraite du PDF\, page 443\.
```text
INSERT INTO insere (id, col1, col2) VALUES (2, 'XXX', 10);
```
**Exemple 8-443-e04.** Exemple ou sortie extraite du PDF\, page 443\.
```text
SELECT * FROM insere ;
```
**Tableau 8-443-t01.** Tableau ou résultat de commande extrait du PDF\, page 443\.
| id | col2 | col1 |
| --- | --- | --- |
| 1 | 10 | XX |
| 2 | 10 | XXX |
L’utilisation de SELECT * à la place d’une liste explicite est une erreur similaire. Le nombre de colonnes peut brutalement varier. De plus, toutes les colonnes sont rarement utilisées dans un tel cas, ce qui provoque un gaspillage de ressources.
## Code spaghetti {#devpg-8-14}
Le problème est similaire à tout autre langage :
- Code spaghetti pour le SQL
- Écriture d’une requête à partir d’une autre requête
- Ou évolution d’une requête au fil du temps avec des ajouts
- Non optimisable
- Vite ingérable
- Ne pas la patcher!
- Ne pas hésiter à reprendre la requête à zéro, en repensant sa sémantique
- Souvent, un changement de spécification est un changement de sens, au niveau relationnel, de la requête
Un exemple (sous Oracle) :
**Exemple 8-444-e01.** Exemple ou sortie extraite du PDF\, page 444\.
```text
SELECT Article.datem AS Article_1_9,
Article.degre_alcool AS Article_1_10, Article.id AS Article_1_19, Article.iddf_categor AS Article_1_20, Article.iddp_clsvtel AS Article_1_21, Article.iddp_cdelist AS Article_1_22, Article.iddf_cd_prix AS Article_1_23, Article.iddp_agreage AS Article_1_24, Article.iddp_codelec AS Article_1_25, Article.idda_compo AS Article_1_26, Article.iddp_comptex AS Article_1_27, Article.iddp_cmptmat AS Article_1_28, Article.idda_articleparent AS Article_1_29, Article.iddp_danger AS Article_1_30, Article.iddf_fabric AS Article_1_33, Article.iddp_marqcom AS Article_1_34, Article.iddp_nomdoua AS Article_1_35, Article.iddp_pays AS Article_1_37, Article.iddp_recept AS Article_1_40, Article.idda_unalvte AS Article_1_42, Article.iddb_sitecl AS Article_1_43, Article.lib_caisse AS Article_1_49, Article.lib_com AS Article_1_50, Article.maj_en_attente AS Article_1_61, Article.qte_stk AS Article_1_63, Article.ref_tech AS Article_1_64, 1 AS Article_1_70, CASE
```
**Exemple 8-444-e02.** Exemple ou sortie extraite du PDF\, page 444\.
```text
WHEN (SELECT COUNT(MA.id)
FROM da_majart MA
join da_majmas MM
ON MM.id = MA.idda_majmas
join gt_tmtprg TMT
ON TMT.id = MM.idgt_tmtprg
join gt_prog PROG
ON PROG.id = TMT.idgt_prog
```
**Exemple 8-445-e01.** Exemple ou sortie extraite du PDF\, page 445\.
```text
WHERE idda_article = Article.id
AND TO_DATE(TO_CHAR(PROG.date_lancement, 'DDMMYYYY')
|| TO_CHAR(PROG.heure_lancement, ' HH24:MI:SS'),
'DDMMYYYY HH24:MI:SS') >= SYSDATE) >= 1 THEN 1
ELSE 0
END AS Article_1_74,
Article.iddp_compnat AS Article_2_0, Article.iddp_modven AS Article_2_1, Article.iddp_nature AS Article_2_2, Article.iddp_preclin AS Article_2_3, Article.iddp_raybala AS Article_2_4, Article.iddp_sensgrt AS Article_2_5, Article.iddp_tcdtfl AS Article_2_6, Article.iddp_unite AS Article_2_8, Article.idda_untgrat AS Article_2_9, Article.idda_unpoids AS Article_2_10, Article.iddp_unilogi AS Article_2_11, ArticleComplement.datem AS ArticleComplement_5_6, ArticleComplement.extgar_depl AS ArticleComplement_5_9, ArticleComplement.extgar_mo AS ArticleComplement_5_10, ArticleComplement.extgar_piece AS ArticleComplement_5_11, ArticleComplement.id AS ArticleComplement_5_20, ArticleComplement.iddf_collect AS ArticleComplement_5_22, ArticleComplement.iddp_gpdtcul AS ArticleComplement_5_23, ArticleComplement.iddp_support AS ArticleComplement_5_25, ArticleComplement.iddp_typcarb AS ArticleComplement_5_27, ArticleComplement.mt_ext_gar AS ArticleComplement_5_36, ArticleComplement.pres_cpt AS ArticleComplement_5_44, GenreProduitCulturel.code AS GenreProduitCulturel_6_0, Collection.libelle AS Collection_8_1, Gtin.date_dern_vte AS Gtin_10_0, Gtin.gtin AS Gtin_10_1, Gtin.id AS Gtin_10_3, Fabricant.code AS Fabricant_14_0, Fabricant.nom AS Fabricant_14_2, ClassificationVenteLocale.niveau1 AS ClassificationVenteL_16_2, ClassificationVenteLocale.niveau2 AS ClassificationVenteL_16_3, ClassificationVenteLocale.niveau3 AS ClassificationVenteL_16_4, ClassificationVenteLocale.niveau4 AS ClassificationVenteL_16_5, MarqueCommerciale.code AS MarqueCommerciale_18_0, MarqueCommerciale.libellelong AS MarqueCommerciale_18_4, Composition.code AS Composition_20_0, CompositionTextile.code AS CompositionTextile_21_0, AssoArticleInterfaceBalance.datem AS AssoArticleInterface_23_0, AssoArticleInterfaceBalance.lib_envoi AS AssoArticleInterface_23_3, AssoArticleInterfaceCaisse.datem AS AssoArticleInterface_24_0, AssoArticleInterfaceCaisse.lib_envoi AS AssoArticleInterface_24_3, NULL AS TypeTraitement_25_0, NULL AS TypeTraitement_25_1, RayonBalance.code AS RayonBalance_31_0, RayonBalance.max_cde_article AS RayonBalance_31_5, RayonBalance.min_cde_article AS RayonBalance_31_6, TypeTare.code AS TypeTare_32_0, GrilleDePrix.datem AS GrilleDePrix_34_1, GrilleDePrix.libelle AS GrilleDePrix_34_3, FicheAgreage.code AS FicheAgreage_38_0, Codelec.iddp_periact AS Codelec_40_1,
```
Codelec.libelle AS Codelec_40_2, Codelec.niveau1 AS Codelec_40_3, Codelec.niveau2 AS Codelec_40_4, Codelec.niveau3 AS Codelec_40_5, Codelec.niveau4 AS Codelec_40_6, PerimetreActivite.code AS PerimetreActivite_41_0, DonneesPersonnalisablesCodelec.gestionreftech AS DonneesPersonnalisab_42_0, ClassificationArticleInterne.id AS ClassificationArticl_43_0, ClassificationArticleInterne.niveau1 AS ClassificationArticl_43_2, DossierCommercial.id AS DossierCommercial_52_0, DossierCommercial.codefourndc AS DossierCommercial_52_1, DossierCommercial.anneedc AS DossierCommercial_52_3, DossierCommercial.codeclassdc AS DossierCommercial_52_4, DossierCommercial.numversiondc AS DossierCommercial_52_5, DossierCommercial.indice AS DossierCommercial_52_6, DossierCommercial.code_ss_classement AS DossierCommercial_52_7, OrigineNegociation.code AS OrigineNegociation_53_0, MotifBlocageInformation.libellelong AS MotifBlocageInformat_54_3, ArbreLogistique.id AS ArbreLogistique_63_1, ArbreLogistique.codesap AS ArbreLogistique_63_5, Fournisseur.code AS Fournisseur_66_0, Fournisseur.nom AS Fournisseur_66_2, Filiere.code AS Filiere_67_0, Filiere.nom AS Filiere_67_2, ValorisationAchat.val_ach_patc AS Valorisation_74_3, LienPrixVente.code AS LienPrixVente_76_0, LienPrixVente.datem AS LienPrixVente_76_1, LienGratuite.code AS LienGratuite_78_0, LienGratuite.datem AS LienGratuite_78_1, LienCoordonnable.code AS LienCoordonnable_79_0, LienCoordonnable.datem AS LienCoordonnable_79_1, LienStatistique.code AS LienStatistique_81_0, LienStatistique.datem AS LienStatistique_81_1
**Exemple 8-446-e01.** Exemple ou sortie extraite du PDF\, page 446\.
```text
FROM da_article Article
join (SELECT idarticle, poids, ROW_NUMBER() over ( PARTITION BY RNA.id
```
**Exemple 8-446-e02.** Exemple ou sortie extraite du PDF\, page 446\.
```text
ORDER BY INNERSEARCH.poids) RN,
titre,
nom,
prenom
FROM da_article RNA
join (SELECT idarticle,
pkg_db_indexation.CALCULPOIDSMOTS(chaine,
'foire vins%') AS POIDS,
DECODE(index_clerecherche, 'Piste.titre', chaine,
'') AS TITRE,
DECODE(index_clerecherche, 'Artiste.nom_prenom',
SUBSTR(chaine, 0, INSTR(chaine, '_') - 1),
'') AS NOM,
DECODE(index_clerecherche, 'Artiste.nom_prenom',
SUBSTR(chaine, INSTR(chaine, '_') + 1),
'') AS PRENOM
FROM ((SELECT index_idenreg AS IDARTICLE,
C.cde_art AS CHAINE,
```
index_clerecherche
**Exemple 8-447-e01.** Exemple ou sortie extraite du PDF\, page 447\.
```text
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Article.codeArticle'
join da_article C
ON id = index_idenreg
WHERE mots_mot = 'foire'
INTERSECT
SELECT index_idenreg AS IDARTICLE,
C.cde_art AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Article.codeArticle'
join da_article C
ON id = index_idenreg
WHERE mots_mot LIKE 'vins%'
AND 1 = 1)
UNION ALL
(SELECT index_idenreg AS IDARTICLE,
C.cde_art_bal AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Article.codeArticleBalance'
join da_article C
ON id = index_idenreg
WHERE mots_mot = 'foire'
INTERSECT
SELECT index_idenreg AS IDARTICLE,
C.cde_art_bal AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Article.codeArticleBalance'
join da_article C
ON id = index_idenreg
WHERE mots_mot LIKE 'vins%'
AND 1 = 1)
UNION ALL
(SELECT index_idenreg AS IDARTICLE,
C.lib_com AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Article.libelleCommercial'
join da_article C
```
**Exemple 8-448-e01.** Exemple ou sortie extraite du PDF\, page 448\.
```text
ON id = index_idenreg
WHERE mots_mot = 'foire'
INTERSECT
```
**Exemple 8-448-e02.** Exemple ou sortie extraite du PDF\, page 448\.
```text
SELECT index_idenreg AS IDARTICLE,
C.lib_com AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Article.libelleCommercial'
join da_article C
ON id = index_idenreg
WHERE mots_mot LIKE 'vins%'
AND 1 = 1)
UNION ALL (SELECT idda_article AS IDARTICLE, C.gtin AS CHAINE, index_clerecherche
```
**Exemple 8-448-e03.** Exemple ou sortie extraite du PDF\, page 448\.
```text
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Gtin.gtin'
join da_gtin C
ON id = index_idenreg
WHERE mots_mot = 'foire'
INTERSECT
```
**Exemple 8-448-e04.** Exemple ou sortie extraite du PDF\, page 448\.
```text
SELECT idda_article AS IDARTICLE,
C.gtin AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Gtin.gtin'
join da_gtin C
ON id = index_idenreg
WHERE mots_mot LIKE 'vins%'
AND 1 = 1)
UNION ALL (SELECT idda_article AS IDARTICLE, C.ref_frn AS CHAINE, index_clerecherche
```
**Exemple 8-448-e05.** Exemple ou sortie extraite du PDF\, page 448\.
```text
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'ArbreLogistique.referenceFournisseur'
join da_arblogi C
ON id = index_idenreg
WHERE mots_mot = 'foire'
INTERSECT
```
**Exemple 8-448-e06.** Exemple ou sortie extraite du PDF\, page 448\.
```text
SELECT idda_article AS IDARTICLE,
C.ref_frn AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
```
**Exemple 8-449-e01.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON I.mots_id = M.mots_id
AND index_clerecherche =
'ArbreLogistique.referenceFournisseur'
join da_arblogi C
ON id = index_idenreg
WHERE mots_mot LIKE 'vins%'
AND 1 = 1))) INNERSEARCH
ON INNERSEARCH.idarticle = RNA.id) SEARCHMC
ON SEARCHMC.idarticle = Article.id
AND 1 = 1
left join da_artcmpl ArticleComplement
```
**Exemple 8-449-e02.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON Article.id = ArticleComplement.idda_article
left join dp_gpdtcul GenreProduitCulturel
```
**Exemple 8-449-e03.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON ArticleComplement.iddp_gpdtcul = GenreProduitCulturel.id
left join df_collect Collection
```
**Exemple 8-449-e04.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON ArticleComplement.iddf_collect = Collection.id
left join da_gtin Gtin
```
**Exemple 8-449-e05.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON Article.id = Gtin.idda_article
AND Gtin.principal = 1
AND Gtin.db_suplog = 0
left join df_fabric Fabricant
```
**Exemple 8-449-e06.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON Article.iddf_fabric = Fabricant.id
left join dp_clsvtel ClassificationVenteLocale
```
**Exemple 8-449-e07.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON Article.iddp_clsvtel = ClassificationVenteLocale.id
left join dp_marqcom MarqueCommerciale
```
**Exemple 8-449-e08.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON Article.iddp_marqcom = MarqueCommerciale.id
left join da_compo Composition
```
**Exemple 8-449-e09.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON Composition.id = Article.idda_compo
left join dp_comptex CompositionTextile
```
**Exemple 8-449-e10.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON CompositionTextile.id = Article.iddp_comptex
left join da_arttrai AssoArticleInterfaceBalance
```
**Exemple 8-449-e11.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON AssoArticleInterfaceBalance.idda_article = Article.id
AND AssoArticleInterfaceBalance.iddp_tinterf = 1
left join da_arttrai AssoArticleInterfaceCaisse
```
**Exemple 8-449-e12.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON AssoArticleInterfaceCaisse.idda_article = Article.id
AND AssoArticleInterfaceCaisse.iddp_tinterf = 4
left join dp_raybala RayonBalance
```
**Exemple 8-449-e13.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON Article.iddp_raybala = RayonBalance.id
left join dp_valdico TypeTare
```
**Exemple 8-449-e14.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON TypeTare.id = RayonBalance.iddp_typtare
left join df_categor Categorie
```
**Exemple 8-449-e15.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON Categorie.id = Article.iddf_categor
left join df_grille GrilleDePrix
```
**Exemple 8-449-e16.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON GrilleDePrix.id = Categorie.iddf_grille
left join dp_agreage FicheAgreage
```
**Exemple 8-449-e17.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON FicheAgreage.id = Article.iddp_agreage
join dp_codelec Codelec
```
**Exemple 8-449-e18.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON Article.iddp_codelec = Codelec.id
left join dp_periact PerimetreActivite
```
**Exemple 8-449-e19.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON PerimetreActivite.id = Codelec.iddp_periact
left join dp_perscod DonneesPersonnalisablesCodelec
```
**Exemple 8-449-e20.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON Codelec.id = DonneesPersonnalisablesCodelec.iddp_codelec
AND DonneesPersonnalisablesCodelec.db_suplog = 0
AND DonneesPersonnalisablesCodelec.iddb_sitecl = 1012124
left join dp_clsart ClassificationArticleInterne
```
**Exemple 8-449-e21.** Exemple ou sortie extraite du PDF\, page 449\.
```text
ON DonneesPersonnalisablesCodelec.iddp_clsart =
```
ClassificationArticleInterne.id left join da_artdeno ArticleDenormalise
**Exemple 8-450-e01.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON Article.id = ArticleDenormalise.idda_article
left join df_clasmnt ClassementFournisseur
```
**Exemple 8-450-e02.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON ArticleDenormalise.iddf_clasmnt = ClassementFournisseur.id
left join tr_dosclas DossierDeClassement
```
**Exemple 8-450-e03.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON ClassementFournisseur.id = DossierDeClassement.iddf_clasmnt
AND DossierDeClassement.date_deb <= '2013-09-27'
AND COALESCE(DossierDeClassement.date_fin,
TO_DATE('31129999', 'DDMMYYYY')) >= '2013-09-27'
left join tr_doscomm DossierCommercial
```
**Exemple 8-450-e04.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON DossierDeClassement.idtr_doscomm = DossierCommercial.id
left join dp_valdico OrigineNegociation
```
**Exemple 8-450-e05.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON DossierCommercial.iddp_dossref = OrigineNegociation.id
left join dp_motbloc MotifBlocageInformation
```
**Exemple 8-450-e06.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON MotifBlocageInformation.id = ArticleDenormalise.idda_motinf
left join da_arblogi ArbreLogistique
```
**Exemple 8-450-e07.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON Article.id = ArbreLogistique.idda_article
AND ArbreLogistique.princ = 1
AND ArbreLogistique.db_suplog = 0
left join df_filiere Filiere
```
**Exemple 8-450-e08.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON ArbreLogistique.iddf_filiere = Filiere.id
left join df_fourn Fournisseur
```
**Exemple 8-450-e09.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON Filiere.iddf_fourn = Fournisseur.id
left join od_dosal dossierALValo
```
**Exemple 8-450-e10.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON dossierALValo.idda_arblogi = ArbreLogistique.id
AND dossierALValo.idod_dossier IS NULL
left join tt_val_dal valoDossier
```
**Exemple 8-450-e11.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON valoDossier.idod_dosal = dossierALValo.id
AND valoDossier.estarecalculer = 0
left join tt_valo ValorisationAchat
```
**Exemple 8-450-e12.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON ValorisationAchat.idtt_val_dal = valoDossier.id
AND ValorisationAchat.date_modif_retro IS NULL
AND ValorisationAchat.date_debut_achat <= '2013-09-27'
AND COALESCE(ValorisationAchat.date_fin_achat,
TO_DATE('31129999', 'DDMMYYYY')) >= '2013-09-27'
AND ValorisationAchat.val_ach_pab IS NOT NULL
left join da_lienart assoALPXVT
```
**Exemple 8-450-e13.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON assoALPXVT.idda_article = Article.id
AND assoALPXVT.iddp_typlien = 14893
left join da_lien LienPrixVente
```
**Exemple 8-450-e14.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON LienPrixVente.id = assoALPXVT.idda_lien
left join da_lienart assoALGRAT
```
**Exemple 8-450-e15.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON assoALGRAT.idda_article = Article.id
AND assoALGRAT.iddp_typlien = 14894
left join da_lien LienGratuite
```
**Exemple 8-450-e16.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON LienGratuite.id = assoALGRAT.idda_lien
left join da_lienart assoALCOOR
```
**Exemple 8-450-e17.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON assoALCOOR.idda_article = Article.id
AND assoALCOOR.iddp_typlien = 14899
left join da_lien LienCoordonnable
```
**Exemple 8-450-e18.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON LienCoordonnable.id = assoALCOOR.idda_lien
left join da_lienal assoALSTAT
```
**Exemple 8-450-e19.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON assoALSTAT.idda_arblogi = ArbreLogistique.id
AND assoALSTAT.iddp_typlien = 14897
left join da_lien LienStatistique
```
**Exemple 8-450-e20.** Exemple ou sortie extraite du PDF\, page 450\.
```text
ON LienStatistique.id = assoALSTAT.idda_lien WHERE
```
SEARCHMC.rn = 1 AND ( ValorisationAchat.id IS NULL OR ValorisationAchat.date_debut_achat = (
**Exemple 8-451-e01.** Exemple ou sortie extraite du PDF\, page 451\.
```text
SELECT MAX(VALMAX.date_debut_achat)
FROM tt_valo VALMAX
WHERE VALMAX.idtt_val_dal = ValorisationAchat.idtt_val_dal
AND VALMAX.date_modif_retro IS NULL
AND VALMAX.val_ach_pab IS NOT NULL
AND VALMAX.date_debut_achat <= '2013-09-27') )
AND ( Article.id IN (SELECT A.id
FROM da_article A
join du_ucutiar AssoUcUtiAr
ON AssoUcUtiAr.idda_article = A.id
join du_asucuti AssoUcUti
ON AssoUcUti.id = AssoUcUtiAr.iddu_asucuti
WHERE ( AssoUcUti.iddu_uti IN ( 90000000000022 ) )
AND a.iddb_sitecl = 1012124) )
AND Article.db_suplog = 0
ORDER BY SEARCHMC.poids ASC
```
Comprendre un tel monstre implique souvent de l’imprimer pour acquérir une vision globale et prendre des notes :
**Figure 8-452-f01.** Figure 8\-452\-f01 \- Un exemple à ne pas suivre\.
Ce code a été généré initialement par Hibernate, puis édité plusieurs fois à la main.
## Recherche textuelle {#devpg-8-15}
- Objectif
- ajouter un moteur de recherche à l’application
- Pratique courante
- utiliser l’opérateur LIKE
- Problèmes
- requiert des index spécialisés
- recherche uniquement le terme exact
- Solutions
- pg_trgm
- Full Text Search
Les bases de données qui stockent des données textuelles ont souvent pour but de permettre des recherches sur ces données textuelles. La première solution envisagée lorsque le besoin se fait sentir est d’utiliser l’opérateur LIKE . Il permet en effet de réaliser des recherches de motif sur une colonne stockant des données textuelles. C’est une solution simple et qui peut s’avérer simpliste dans de nombreux cas. Tout d’abord, les recherches de type LIKE '%motif%' ne peuvent généralement pas tirer partie d’un index btree normal. Cela étant dit, l’extension pg_trgm permet d’optimiser ces recherches à l’aide d’un index GiST ou GIN. Elle fait partie des extensions standard et ne nécessite pas d’adaptation du code. Exemples L’exemple ci-dessous montre l’utilisation du module pg_trgm pour accélérer une recherche avec LIKE '%motif%' :
**Exemple 8-454-e01.** Exemple ou sortie extraite du PDF\, page 454\.
```text
CREATE INDEX idx_appellation_libelle ON appellation
USING btree (libelle varchar_pattern_ops);
```
**Exemple 8-454-e02.** Exemple ou sortie extraite du PDF\, page 454\.
```text
EXPLAIN SELECT * FROM appellation WHERE libelle LIKE '%wur%';
```
**Exemple 8-454-e03.** Exemple ou sortie extraite du PDF\, page 454\.
```text
QUERY PLAN
------------------------------------------------------------
Seq Scan on appellation (cost=0.00..6.99 rows=3 width=24)
Filter: (libelle ~~ '%wur%'::text)
```
**Exemple 8-454-e04.** Exemple ou sortie extraite du PDF\, page 454\.
```text
CREATE EXTENSION pg_trgm;
```
**Exemple 8-454-e05.** Exemple ou sortie extraite du PDF\, page 454\.
```text
CREATE INDEX idx_appellation_libelle_trgm ON appellation
USING gist (libelle gist_trgm_ops);
```
**Exemple 8-454-e06.** Exemple ou sortie extraite du PDF\, page 454\.
```text
EXPLAIN SELECT * FROM appellation WHERE libelle LIKE '%wur%';
```
**Exemple 8-454-e07.** Exemple ou sortie extraite du PDF\, page 454\.
```text
QUERY PLAN
```
**Exemple 8-455-e01.** Exemple ou sortie extraite du PDF\, page 455\.
```text
-----------------------------------------------------------------------------
Bitmap Heap Scan on appellation (cost=4.27..7.41 rows=3 width=24)
Recheck Cond: (libelle ~~ '%wur%'::text)
```
**Exemple 8-455-e02.** Exemple ou sortie extraite du PDF\, page 455\.
```text
-> Bitmap Index Scan on idx_appellation_libelle_trgm (cost=0.00..4.27...)
Index Cond: (libelle ~~ '%wur%'::text)
```
Mais cette solution n’offre pas la même souplesse que la recherche plein texte, en anglais Full Text Search, de PostgreSQL. Elle est cependant plus complexe à mettre en œuvre et possède une syntaxe spécifique.
## Conclusion {#devpg-8-16}
- La base est là pour vous aider
- Le modèle relationnel doit être compris et appliqué
- Avant de contourner un problème, chercher s’il n’existe pas une fonctionnalité
dédiée
## Quiz {#devpg-8-17}
https://dali.bo/s65_quiz
## Travaux pratiques {#devpg-8-18}
La version en ligne des solutions de ces TP est disponible sur https://dali.bo/s65_solutions. Ce TP utilise les tables voitures et voitures_ecv .
Si vous n’avez pas déjà une base voitures, les deux tables voitures et voitures_ecv peuvent être téléchargées installées comme suit :
**Exemple 8-458-e01.** Exemple ou sortie extraite du PDF\, page 458\.
```text
createdb voitures
curl -kL https://dali.bo/tp_voitures -o /tmp/voitures.dmp
pg_restore -d voitures /tmp/voitures.dmp
# un message sur le schéma public préexistant est normal
```
Ne pas oublier d’effectuer un VACUUM ANALYZE .
### Normalisation de schéma {#devpg-8-18-1}
But : Normaliser un schéma de données.
La table voitures viole la première forme normale (attribut répétitif, non atomique). De plus elle n’a pas de clé primaire.
Renommer la table en voitures_orig . \ Ne pas la supprimer (nous en aurons besoin plus tard).
É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.)
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!
Tenter d’insérer une Clio avec les caractéristiques « ABS » (majusucules) et « phares LED ».
Comparer les performances entre les deux modèles pour une recherche des voitures ayant un toit ouvrant.
Les plans sont-ils les mêmes si la caractéristique recherchée n’existe pas?
Indexer la colonne de clé étrangère caracteristiques_voitures.carateristique et voir ce que devient le plan de la dernière requête.
Rechercher une voitures possédant les 3 options ABS, toit ouvrant et 4 roues motrices, et voir le plan.
### Entité-clé-valeur {#devpg-8-18-2}
But : Manipuler des données au format entité/clé/valeur.
Une autre version de la table voiture existe aussi dans cette base au format « entité/clé/valeur » c’est la table voitures_ecv . Sa clé primaire est entite (immatriculation) / cle (caractéristique). En pratique il n’y a que des booléens.
Afficher toutes les caractéristiques d’une voiture au hasard (par exemple 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?
hstore est une extension qui permet de stocker des clés/valeur dans un champ. Sa documentation est sur le site du projet 9 .
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 .
Rechercher la voiture précédente.
Insérer une voiture avec les caractéristiques couleur=>vert et phares=>LED .
Définir un index de type GiST sur ce champ hstore . \ Retrouver la voiture insérée par ses caractéristiques. 9 https://docs.postgresql.fr/current/hstore.html
### Indexation de champs tableau {#devpg-8-18-3}
But : Indexer un champ tableau pour améliorer les performances.
Il est possible, si on peut réécrire la requête, d’obtenir de bonnes performances avec la première table voitures_orig . En effet, PostgreSQL sait indexer des tableaux et des fonctions. Il saurait donc indexer un tableau résultat d’une fonction sur le champ caracteristiques .
Trouver cette fonction dans la documentation de PostgreSQL (chercher dans les fonctions de découpage de chaîne de caractères).
Définir un index fonctionnel sur le résultat de cette fonction, de type GIN.
Rechercher toutes les voitures avec toit ouvrant et voir le plan.
### Pagination et index {#devpg-8-18-4}
But : Effectuer une requête avec pagination
La pagination est une fonctionnalité que l’on retrouve de plus en plus souvent, surtout depuis que les applications web ont pris une place prépondérante. Nous allons utiliser une version simplifiée d’une table de forum. Si vous ne l’avez pas déjà, la table posts (dump de 358 Mo, 758 Mo sur disque) peut être téléchargée et restaurée ainsi :
**Exemple 8-460-e01.** Exemple ou sortie extraite du PDF\, page 460\.
```text
curl -kL https://dali.bo/tp_posts -o /tmp/posts.dump
createdb posts
pg_restore -d posts /tmp/posts.dump
# le message sur le schéma public préexistant est normal
rm -- /tmp/posts.dump
```
Ne pas oublier d’effectuer ensuite un VACUUM ANALYZE . Nous voulons afficher le plus rapidement possible les messages (posts) associés à un article : les 10 premiers, puis du 11 au 20, etc. Nous allons examiner les différentes stratégies possibles. La table contient 5 000 articles de 1000 posts, d’au plus 200 signes. La description de la table est :
**Exemple 8-461-e01.** Exemple ou sortie extraite du PDF\, page 461\.
```text
# \d posts
Table « public.posts »
```
**Tableau 8-461-t01.** Tableau ou résultat de commande extrait du PDF\, page 461\.
| Colonne | Type | Collationnement | NULL-able | Par défaut |
| --- | --- | --- | --- | --- |
| id_article | integer | | | |
| id_post | integer | | | |
| ts | timestamp with time zone | | | |
| message | text | | | |
Index : "posts_ts_idx" btree (ts)
Pour la clarté des plans, désactiver le JIT et le parallélisme dans votre session :
**Exemple 8-461-e02.** Exemple ou sortie extraite du PDF\, page 461\.
```text
SET jit to off ;
SET max_parallel_workers_per_gather TO 0 ;
```
É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.
Créer un index permettant d’améliorer cette requête.
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?
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.
### Clauses WHERE et pièges {#devpg-8-18-5}
But : Mise en évidence de cas piégeux dans les clauses WHERE.
Nous utilisons toujours la table posts . Nous allons maintenant manipuler le champ ts , de type timestamp . Ce champ est indexé.
La requête
**Exemple 8-461-e03.** Exemple ou sortie extraite du PDF\, page 461\.
```text
SELECT * FROM posts WHERE to_char(ts,'YYYYMM')='201302'
```
retourne tous les enregistrements de février 2013. Examiner son plan d’exécution. Où est le problème?
Réécrire la clause WHERE avec une inégalité de dates pour utiliser l’index sur ts .
Plus compliqué : retourner tous les posts ayant eu lieu un dimanche, en 2013, en passant par un index et en une seule requête. (Indice : il est possible de générer la liste de tous les dimanches de l’année 2013 avec : generate_series('2013-01-06 00:00:00','2014-01-01 00:00:00', INTERVAL '7 days') )
On cherche un article à peu près au tiers de la liste avec la requête suivante. Pourquoi est-elle si lente?
**Exemple 8-462-e01.** Exemple ou sortie extraite du PDF\, page 462\.
```text
SELECT * FROM posts
WHERE id_article =
(SELECT max(id_article) * 0.333
FROM posts
) ;
```
## Travaux pratiques (solutions) {#devpg-8-19}
### Normalisation de schéma {#devpg-8-19-1}
Renommer la table en voitures_orig . \ Ne pas la supprimer (nous en aurons besoin plus tard).
**Exemple 8-463-e01.** Exemple ou sortie extraite du PDF\, page 463\.
```text
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.)
**Exemple 8-463-e02.** Exemple ou sortie extraite du PDF\, page 463\.
```text
CREATE TABLE voitures AS
SELECT DISTINCT ON (immatriculation) immatriculation, modele
FROM voitures_orig ;
```
**Exemple 8-463-e03.** Exemple ou sortie extraite du PDF\, page 463\.
```text
ALTER TABLE voitures ADD PRIMARY KEY (immatriculation);
```
**Exemple 8-463-e04.** Exemple ou sortie extraite du PDF\, page 463\.
```text
CREATE TABLE caracteristiques
AS SELECT *
```
**Exemple 8-463-e05.** Exemple ou sortie extraite du PDF\, page 463\.
```text
FROM (
SELECT DISTINCT
regexp_split_to_table(caracteristiques,',') caracteristique
FROM voitures_orig)
AS tmp
WHERE caracteristique <> '' ;
```
**Exemple 8-463-e06.** Exemple ou sortie extraite du PDF\, page 463\.
```text
ALTER TABLE caracteristiques ADD PRIMARY KEY (caracteristique);
```
**Exemple 8-463-e07.** Exemple ou sortie extraite du PDF\, page 463\.
```text
CREATE TABLE caracteristiques_voitures
AS SELECT DISTINCT *
```
**Exemple 8-463-e08.** Exemple ou sortie extraite du PDF\, page 463\.
```text
FROM (
SELECT
immatriculation,
regexp_split_to_table(caracteristiques,',') caracteristique
FROM voitures_orig
)
AS tmp
```
**Exemple 8-463-e09.** Exemple ou sortie extraite du PDF\, page 463\.
```text
WHERE caracteristique <> '';
```
**Exemple 8-463-e10.** Exemple ou sortie extraite du PDF\, page 463\.
```text
VACUUM ANALYZE ;
```
**Exemple 8-463-e11.** Exemple ou sortie extraite du PDF\, page 463\.
```text
\d+
Liste des relations
```
**Tableau 8-463-t01.** Tableau ou résultat de commande extrait du PDF\, page 463\.
| Schéma | Nom | Type | Propriétaire | ... | Taille | ... |
| --- | --- | --- | --- | --- | --- | --- |
| public | caracteristiques | table | postgres | | 48 kB | |
| public | caracteristiques_voitures | table | postgres | | 3208 kB | |
| public | voitures | table | postgres | | 5160 kB | |
| public | voitures_ecv | table | postgres | | 3336 kB | |
| public | voitures_orig | table | postgres | | 5736 kB | |
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 :
**Exemple 8-464-e01.** Exemple ou sortie extraite du PDF\, page 464\.
```text
ALTER TABLE caracteristiques_voitures
ADD PRIMARY KEY (immatriculation,caracteristique);
```
Clé étrangère de cette table vers les deux autres tables :
**Exemple 8-464-e02.** Exemple ou sortie extraite du PDF\, page 464\.
```text
ALTER TABLE caracteristiques_voitures
ADD FOREIGN KEY (immatriculation) REFERENCES voitures(immatriculation);
```
**Exemple 8-464-e03.** Exemple ou sortie extraite du PDF\, page 464\.
```text
ALTER TABLE caracteristiques_voitures
ADD 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 :
**Exemple 8-464-e04.** Exemple ou sortie extraite du PDF\, page 464\.
```text
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 ;
ERROR: insert or update on table "caracteristiques_voitures" violates foreign key
constraint "caracteristiques_voitures_caracteristique_fkey"
DÉTAIL : Key (caracteristique)=(ABS) is not present in table "caracteristiques".
ERROR: insert or update on table "caracteristiques_voitures" violates foreign key
constraint "caracteristiques_voitures_immatriculation_fkey"
DÉTAIL : Key (immatriculation)=(AA-007-JB) is not present in table "voitures".
```
É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é :
**Exemple 8-464-e05.** Exemple ou sortie extraite du PDF\, page 464\.
```text
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 :
**Exemple 8-465-e01.** Exemple ou sortie extraite du PDF\, page 465\.
```text
SELECT * FROM voitures_orig
WHERE caracteristiques like '%toit ouvrant%';
```
Plus rigoureusement ( [[:>:]] et [[:<:]] indiquent des frontières de mots.), on préférera :
**Exemple 8-465-e02.** Exemple ou sortie extraite du PDF\, page 465\.
```text
EXPLAIN ANALYZE
SELECT * FROM voitures_orig
WHERE caracteristiques ~ E'[[:<:]]toit ouvrant[[:>:]]' ;
```
**Exemple 8-465-e03.** Exemple ou sortie extraite du PDF\, page 465\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on voitures_orig (cost=0.00..1962.00 rows=8557 width=25) (actual
time=0.069..121.861 rows=8358.00 loops=1)
Filter: (caracteristiques ~ '[[:<:]]toit ouvrant[[:>:]]'::text)
Rows Removed by Filter: 91642
Planning Time: 1.221 ms
Execution Time: 126.361 ms
```
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 :
**Exemple 8-465-e05.** Exemple ou sortie extraite du PDF\, page 465\.
```text
SELECT *
FROM voitures
INNER JOIN caracteristiques_voitures
```
**Exemple 8-465-e06.** Exemple ou sortie extraite du PDF\, page 465\.
```text
ON ( 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 :
**Exemple 8-465-e07.** Exemple ou sortie extraite du PDF\, page 465\.
```text
SELECT *
FROM voitures
WHERE EXISTS (
SELECT 1 FROM caracteristiques_voitures
WHERE caracteristiques_voitures.immatriculation=voitures.immatriculation
AND caracteristique = 'toit ouvrant'
) ;
```
Dans les deux cas, on obtient ce plan 10 :
**Exemple 8-465-e08.** Exemple ou sortie extraite du PDF\, page 465\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Hash Join (cost=1226.55..3128.92 rows=8389 width=41) (actual time=13.714..119.595
rows=8358.00 loops=1)
```
10 https://explain.dalibo.com/plan/lz
**Exemple 8-466-e01.** Exemple ou sortie extraite du PDF\, page 466\.
```text
Hash Cond: (voitures.immatriculation = caracteristiques_voitures.immatriculation)
-> Seq Scan on voitures (cost=0.00..1639.89 rows=99989 width=16) (actual
time=0.011..45.753 rows=99989.00 loops=1)
-> Hash (cost=1121.69..1121.69 rows=8389 width=25) (actual time=13.676..13.679
rows=8358.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 577kB
-> Seq Scan on caracteristiques_voitures (cost=0.00..1121.69 rows=8389
width=25) (actual time=0.010..8.928 rows=8358.00 loops=1)
Filter: (caracteristique = 'toit ouvrant'::text)
Rows Removed by Filter: 49697
Planning Time: 0.780 ms
Execution Time: 123.269 ms
```
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 11 :
**Exemple 8-466-e03.** Exemple ou sortie extraite du PDF\, page 466\.
```text
EXPLAIN ANALYZE
SELECT *
FROM voitures
INNER JOIN caracteristiques_voitures
```
**Exemple 8-466-e04.** Exemple ou sortie extraite du PDF\, page 466\.
```text
ON ( caracteristiques_voitures.immatriculation=voitures.immatriculation)
WHERE caracteristique = 'ordinateur de bord' ;
```
**Exemple 8-466-e05.** Exemple ou sortie extraite du PDF\, page 466\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Nested Loop (cost=0.42..1130.12 rows=1 width=41) (actual time=4.991..4.994
rows=0.00 loops=1)
-> Seq Scan on caracteristiques_voitures (cost=0.00..1121.69 rows=1 width=25)
(actual time=4.988..4.990 rows=0.00 loops=1)
Filter: (caracteristique = 'ordinateur de bord'::text)
Rows Removed by Filter: 58055
-> Index Scan using voitures_pkey on voitures (cost=0.42..8.44 rows=1 width=16)
(never executed)
Index Cond: (immatriculation = caracteristiques_voitures.immatriculation)
Index Searches: 0
Planning Time: 0.424 ms
```
**Exemple 8-466-e06.** Exemple ou sortie extraite du PDF\, page 466\.
```text
Execution Time: 5.020 ms
```
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. 11 https://explain.dalibo.com/plan/Hij
**Exemple 8-467-e01.** Exemple ou sortie extraite du PDF\, page 467\.
```text
CREATE INDEX ON caracteristiques_voitures (caracteristique) ;
```
Le plan d’exécution 12 devient foudroyant, puisque la table caracteristiques_voitures n’est plus intégralement lue :
**Exemple 8-467-e02.** Exemple ou sortie extraite du PDF\, page 467\.
```text
EXPLAIN ANALYZE
SELECT *
FROM voitures
INNER JOIN caracteristiques_voitures
```
**Exemple 8-467-e03.** Exemple ou sortie extraite du PDF\, page 467\.
```text
ON ( caracteristiques_voitures.immatriculation=voitures.immatriculation)
WHERE caracteristique = 'ordinateur de bord' ;
```
**Exemple 8-467-e04.** Exemple ou sortie extraite du PDF\, page 467\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Nested Loop (cost=0.71..16.66 rows=1 width=41) (actual time=0.036..0.038 rows=0.00
loops=1)
```
**Exemple 8-467-e05.** Exemple ou sortie extraite du PDF\, page 467\.
```text
-> Index Scan using caracteristiques_voitures_caracteristique_idx on
caracteristiques_voitures (cost=0.29..8.23 rows=1 width=25) (actual
time=0.033..0.034 rows=0.00 loops=1)
Index Cond: (caracteristique = 'ordinateur de bord'::text)
Index Searches: 1
```
**Exemple 8-467-e06.** Exemple ou sortie extraite du PDF\, page 467\.
```text
-> Index Scan using voitures_pkey on voitures (cost=0.42..8.44 rows=1 width=16)
(never executed)
Index Cond: (immatriculation = caracteristiques_voitures.immatriculation)
Index Searches: 0
Planning Time: 0.575 ms
```
**Exemple 8-467-e07.** Exemple ou sortie extraite du PDF\, page 467\.
```text
Execution Time: 0.086 ms
```
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 13 :
**Exemple 8-467-e08.** Exemple ou sortie extraite du PDF\, page 467\.
```text
EXPLAIN (ANALYZE, COSTS OFF)
SELECT *
FROM voitures
JOIN 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' ;
```
**Exemple 8-467-e09.** Exemple ou sortie extraite du PDF\, page 467\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Nested Loop
-> Hash Join
```
12 https://explain.dalibo.com/plan/577 13 https://explain.dalibo.com/plan/O0H
**Exemple 8-468-e01.** Exemple ou sortie extraite du PDF\, page 468\.
```text
Hash Cond: (cr3.immatriculation = cr1.immatriculation)
-> Bitmap Heap Scan on caracteristiques_voitures cr3
Recheck Cond: (caracteristique = '4 roues motrices'::text)
-> Bitmap Index Scan on caracteristiques_voitures_caracteristique_idx
Index Cond: (caracteristique = '4 roues motrices'::text)
-> Hash
-> Hash Join
Hash Cond: (cr1.immatriculation = cr2.immatriculation)
-> Bitmap Heap Scan on caracteristiques_voitures cr1
Recheck Cond: (caracteristique = 'toit ouvrant'::text)
-> Bitmap Index Scan on
caracteristiques_voitures_caracteristique_idx
```
**Exemple 8-468-e02.** Exemple ou sortie extraite du PDF\, page 468\.
```text
Index Cond: (caracteristique = 'toit ouvrant'::text)
-> Hash
-> Bitmap Heap Scan on caracteristiques_voitures cr2
Recheck Cond: (caracteristique = 'abs'::text)
-> Bitmap Index Scan on
caracteristiques_voitures_caracteristique_idx
```
**Exemple 8-468-e03.** Exemple ou sortie extraite du PDF\, page 468\.
```text
Index Cond: (caracteristique = 'abs'::text)
-> Index Scan using voitures_pkey on voitures
Index Cond: (immatriculation = cr1.immatriculation)
```
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 {#devpg-8-19-2}
Afficher toutes les caractéristiques d’une voiture au hasard (par exemple ZY-745-KT).
**Exemple 8-468-e04.** Exemple ou sortie extraite du PDF\, page 468\.
```text
SELECT * FROM voitures_ecv
WHERE entite = 'ZY-745-KT' ;
```
**Tableau 8-468-t01.** Tableau ou résultat de commande extrait du PDF\, page 468\.
| entite | cle | valeur |
| --- | --- | --- |
| ZY-745-KT | climatisation | t |
| ZY-745-KT | jantes aluminium | t |
| ZY-745-KT | regulateur de vitesse | t |
| ZY-745-KT | toit ouvrant | t |
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 14 :
**Exemple 8-469-e01.** Exemple ou sortie extraite du PDF\, page 469\.
```text
EXPLAIN ANALYZE
SELECT * FROM voitures_ecv
WHERE EXISTS (
SELECT 1 FROM voitures_ecv test
WHERE test.entite=voitures_ecv.entite
AND cle = 'toit ouvrant' AND valeur = true
)
ORDER BY entite ;
```
**Exemple 8-469-e02.** Exemple ou sortie extraite du PDF\, page 469\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Sort (cost=3435.99..3473.81 rows=15128 width=26) (actual time=124.702..136.224
rows=17782.00 loops=1)
Sort Key: voitures_ecv.entite
Sort Method: quicksort Memory: 1525kB
-> Hash Join (cost=1240.76..2385.73 rows=15128 width=26) (actual
time=16.170..107.078 rows=17782.00 loops=1)
Hash Cond: (voitures_ecv.entite = test.entite)
-> Seq Scan on voitures_ecv (cost=0.00..992.55 rows=58055 width=26)
(actual time=0.010..34.524 rows=58055.00 loops=1)
-> Hash (cost=1137.69..1137.69 rows=8246 width=10) (actual
time=16.142..16.147 rows=8358.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 471kB
-> Seq Scan on voitures_ecv test (cost=0.00..1137.69 rows=8246
width=10) (actual time=0.006..10.346 rows=8358.00 loops=1)
Filter: (valeur AND (cle = 'toit ouvrant'::text))
Rows Removed by Filter: 49697
Planning Time: 0.498 ms
Execution Time: 146.760 ms
```
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.
**Exemple 8-469-e04.** Exemple ou sortie extraite du PDF\, page 469\.
```text
CREATE EXTENSION hstore;
```
**Exemple 8-469-e05.** Exemple ou sortie extraite du PDF\, page 469\.
```text
CREATE TABLE voitures_hstore
AS
```
**Exemple 8-469-e06.** Exemple ou sortie extraite du PDF\, page 469\.
```text
SELECT entite AS immatriculation,
hstore(array_agg(cle),array_agg(valeur)::text[]) AS caracteristiques
FROM voitures_ecv group by entite;
```
**Exemple 8-469-e07.** Exemple ou sortie extraite du PDF\, page 469\.
```text
ALTER TABLE voitures_hstore ADD PRIMARY KEY (immatriculation);
```
14 https://explain.dalibo.com/plan/nn2
Rechercher la voiture précédente.
**Exemple 8-470-e01.** Exemple ou sortie extraite du PDF\, page 470\.
```text
SELECT * FROM voitures_hstore
WHERE immatriculation = 'ZY-745-KT' \gx
```
**Exemple 8-470-e02.** Exemple ou sortie extraite du PDF\, page 470\.
```text
-[ RECORD 1 ]----+--------------------------------------------------------------
```
**Tableau 8-470-t01.** Tableau ou résultat de commande extrait du PDF\, page 470\.
| Colonne 1 | Colonne 2 |
| --- | --- |
| immatriculation | ZY-745-KT |
| caracteristiques | "toit ouvrant"=>"true", "climatisation"=>"true", |
| "jantes aluminium"=>"true", "regulateur de vitesse"=>"true" | |
L’accès à une caractéristique se fait ainsi (attention aux espaces) :
**Exemple 8-470-e03.** Exemple ou sortie extraite du PDF\, page 470\.
```text
SELECT immatriculation, caracteristiques -> 'climatisation'
FROM voitures_hstore
WHERE immatriculation = 'ZY-745-KT' ;
```
Insérer une voiture avec les caractéristiques couleur=>vert et phares=>LED .
**Exemple 8-470-e04.** Exemple ou sortie extraite du PDF\, page 470\.
```text
INSERT INTO voitures_hstore
VALUES ('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 :
**Exemple 8-470-e05.** Exemple ou sortie extraite du PDF\, page 470\.
```text
CREATE INDEX voitures_hstore_caracteristiques
ON voitures_hstore
USING gist (caracteristiques);
```
L’opérateur @> signifie « contient » :
**Exemple 8-470-e06.** Exemple ou sortie extraite du PDF\, page 470\.
```text
SELECT *
FROM voitures_hstore
WHERE caracteristiques @> 'couleur=>vert' AND caracteristiques @> 'phares=>LED' ;
```
**Exemple 8-470-e07.** Exemple ou sortie extraite du PDF\, page 470\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Bitmap Heap Scan on voitures_hstore (cost=4.32..19.24 rows=4 width=64) (actual
time=0.063..0.066 rows=1.00 loops=1)
Recheck Cond: ((caracteristiques @> '"couleur"=>"vert"'::hstore) AND (caracteristiques @> '"phares"=>"LED"'::hstore))
Heap Blocks: exact=1
```
**Exemple 8-470-e08.** Exemple ou sortie extraite du PDF\, page 470\.
```text
Buffers: shared hit=4
-> Bitmap Index Scan on voitures_hstore_caracteristiques (cost=0.00..4.32
rows=4 width=0) (actual time=0.044..0.045 rows=1.00 loops=1)
Index Cond: ((caracteristiques @> '"couleur"=>"vert"'::hstore) AND
(caracteristiques @> '"phares"=>"LED"'::hstore))
Index Searches: 1
```
**Exemple 8-470-e09.** Exemple ou sortie extraite du PDF\, page 470\.
```text
Buffers: shared hit=3
Planning:
Buffers: shared hit=20
Planning Time: 0.280 ms
Execution Time: 0.102 ms
```
### Indexation de champs tableau {#devpg-8-19-3}
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) :
**Exemple 8-471-e01.** Exemple ou sortie extraite du PDF\, page 471\.
```text
SELECT immatriculation, modele,
regexp_split_to_array(caracteristiques,',')
```
**Exemple 8-471-e02.** Exemple ou sortie extraite du PDF\, page 471\.
```text
FROM voitures_orig
LIMIT 10;
```
**Tableau 8-471-t01.** Tableau ou résultat de commande extrait du PDF\, page 471\.
| immatriculation | modele | regexp_split_to_array |
| --- | --- | --- |
| WW-649-AI | twingo | {"regulateur de vitesse"} |
| QZ-533-JD | clio | {"4 roues motrices","jantes aluminium"} |
| YY-854-LE | megane | {climatisation} |
| QD-761-QV | twingo | {""} |
| LV-277-QC | megane | {abs,"jantes aluminium"} |
| ZI-003-BQ | kangoo | {"boite automatique",climatisation} |
| WT-817-IK | megane | {""} |
| JK-791-XB | megane | {""} |
| WW-019-EK | megane | {""} |
| BZ-544-OS | twingo | {""} |
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.
**Exemple 8-471-e03.** Exemple ou sortie extraite du PDF\, page 471\.
```text
CREATE INDEX idx_voitures_array ON voitures_orig
USING gin (regexp_split_to_array(caracteristiques,','));
```
Rechercher toutes les voitures avec toit ouvrant et voir le plan.
**Exemple 8-471-e04.** Exemple ou sortie extraite du PDF\, page 471\.
```text
EXPLAIN ANALYZE
SELECT * FROM voitures_orig
WHERE regexp_split_to_array(caracteristiques,',') @> '{"toit ouvrant"}';
```
**Exemple 8-471-e05.** Exemple ou sortie extraite du PDF\, page 471\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Bitmap Heap Scan on voitures_orig (cost=23.52..711.60 rows=500 width=25) (actual
time=1.259..9.499 rows=8358.00 loops=1)
Recheck Cond: (regexp_split_to_array(caracteristiques, ','::text) @> '{"toit ouvrant"}'::text[])
Heap Blocks: exact=712
```
**Exemple 8-471-e06.** Exemple ou sortie extraite du PDF\, page 471\.
```text
-> Bitmap Index Scan on idx_voitures_array (cost=0.00..23.39 rows=500 width=0)
(actual time=1.061..1.063 rows=8358.00 loops=1)
Index Cond: (regexp_split_to_array(caracteristiques, ','::text) @> '{"toit
ouvrant"}'::text[])
Index Searches: 1
Planning Time: 0.264 ms
```
**Exemple 8-471-e07.** Exemple ou sortie extraite du PDF\, page 471\.
```text
Execution Time: 14.969 ms
```
Noter que les estimations de statistiques sont plus délicates sur un résultat de fonction.
### Pagination et index {#devpg-8-19-4}
É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.
**Exemple 8-472-e01.** Exemple ou sortie extraite du PDF\, page 472\.
```text
EXPLAIN ANALYZE
SELECT *
FROM posts
WHERE id_article =12
ORDER BY id_post
LIMIT 10 ;
```
Le plan 15 est un parcours complet de la table, rejetant 4 999 000 lignes et en gardant 1000 lignes, suivi d’un tri :
**Exemple 8-472-e02.** Exemple ou sortie extraite du PDF\, page 472\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Limit (cost=153681.84..153681.86 rows=10 width=116) (actual time=843.226..843.254
rows=10.00 loops=1)
-> Sort (cost=153681.84..153684.29 rows=980 width=116) (actual
time=843.220..843.232 rows=10.00 loops=1)
Sort Key: id_post
Sort Method: top-N heapsort Memory: 27kB
-> Seq Scan on posts (cost=0.00..153660.66 rows=980 width=116) (actual
time=4.031..842.191 rows=1000.00 loops=1)
Filter: (id_article = 12)
Rows Removed by Filter: 4999000
Planning Time: 1.197 ms
Execution Time: 843.344 ms
```
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 :
**Exemple 8-472-e04.** Exemple ou sortie extraite du PDF\, page 472\.
```text
CREATE INDEX posts_id_article_id_post ON posts (id_article, id_post);
```
Testons cet index :
**Exemple 8-472-e05.** Exemple ou sortie extraite du PDF\, page 472\.
```text
EXPLAIN ANALYZE
SELECT *
FROM posts
WHERE id_article =12
ORDER BY id_post
LIMIT 10 ;
```
Le plan 16 devient :
**Exemple 8-472-e06.** Exemple ou sortie extraite du PDF\, page 472\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
```
15 https://explain.dalibo.com/plan/xEs 16 https://explain.dalibo.com/plan/Fgy
**Exemple 8-473-e01.** Exemple ou sortie extraite du PDF\, page 473\.
```text
Limit (cost=0.43..18.26 rows=10 width=116) (actual time=0.165..0.212 rows=10.00
loops=1)
```
**Exemple 8-473-e02.** Exemple ou sortie extraite du PDF\, page 473\.
```text
-> Index Scan using posts_id_article_id_post on posts (cost=0.43..1747.65
rows=980 width=116) (actual time=0.160..0.190 rows=10.00 loops=1)
Index Cond: (id_article = 12)
Index Searches: 1
Planning Time: 1.785 ms
```
**Exemple 8-473-e03.** Exemple ou sortie extraite du PDF\, page 473\.
```text
Execution Time: 0.273 ms
```
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 :
**Exemple 8-473-e04.** Exemple ou sortie extraite du PDF\, page 473\.
```text
EXPLAIN ANALYZE
SELECT *
FROM posts
WHERE id_article = 12
ORDER BY id_post
LIMIT 10 OFFSET 10;
```
**Exemple 8-473-e05.** Exemple ou sortie extraite du PDF\, page 473\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Limit (cost=18.26..36.09 rows=10 width=116) (actual time=0.094..0.124 rows=10.00
loops=1)
```
**Exemple 8-473-e06.** Exemple ou sortie extraite du PDF\, page 473\.
```text
-> Index Scan using posts_id_article_id_post on posts (cost=0.43..1747.65
rows=980 width=116) (actual time=0.066..0.093 rows=20.00 loops=1)
Index Cond: (id_article = 12)
Index Searches: 1
Planning Time: 0.239 ms
```
**Exemple 8-473-e07.** Exemple ou sortie extraite du PDF\, page 473\.
```text
Execution Time: 0.173 ms
```
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 :
**Exemple 8-473-e08.** Exemple ou sortie extraite du PDF\, page 473\.
```text
EXPLAIN ANALYZE
SELECT *
FROM posts
WHERE id_article = 12
ORDER BY id_post
LIMIT 10 OFFSET 900 ;
```
Le plan 17 reste similaire :
**Exemple 8-473-e09.** Exemple ou sortie extraite du PDF\, page 473\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
```
17 https://explain.dalibo.com/plan/V05
**Exemple 8-474-e01.** Exemple ou sortie extraite du PDF\, page 474\.
```text
Limit (cost=1605.02..1622.85 rows=10 width=116) (actual time=2.929..2.978
rows=10.00 loops=1)
-> Index Scan using posts_id_article_id_post on posts (cost=0.43..1747.65
rows=980 width=116) (actual time=0.079..2.132 rows=910.00 loops=1)
Index Cond: (id_article = 12)
Index Searches: 1
Planning Time: 0.229 ms
```
**Exemple 8-474-e02.** Exemple ou sortie extraite du PDF\, page 474\.
```text
Execution Time: 3.039 ms
```
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 :
**Exemple 8-474-e03.** Exemple ou sortie extraite du PDF\, page 474\.
```text
SELECT id_article, id_post
FROM posts
WHERE id_article = 12
ORDER BY id_post
LIMIT 1 OFFSET 899 ;
```
**Tableau 8-474-t01.** Tableau ou résultat de commande extrait du PDF\, page 474\.
| id_article | id_post |
| --- | --- |
| 12 | 12900 |
(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 :
**Exemple 8-474-e04.** Exemple ou sortie extraite du PDF\, page 474\.
```text
EXPLAIN ANALYZE
SELECT *
FROM posts
WHERE id_article = 12
AND id_post> 12900
ORDER BY id_post
LIMIT 10;
```
**Exemple 8-474-e05.** Exemple ou sortie extraite du PDF\, page 474\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Limit (cost=0.43..18.29 rows=10 width=116) (actual time=0.042..0.089 rows=10.00
loops=1)
```
**Exemple 8-474-e06.** Exemple ou sortie extraite du PDF\, page 474\.
```text
-> Index Scan using posts_id_article_id_post on posts (cost=0.43..1746.56
rows=978 width=116) (actual time=0.038..0.067 rows=10.00 loops=1)
Index Cond: ((id_article = 12) AND (id_post > 12900))
Index Searches: 1
Planning Time: 0.259 ms
```
**Exemple 8-474-e07.** Exemple ou sortie extraite du PDF\, page 474\.
```text
Execution Time: 0.134 ms
```
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 {#devpg-8-19-5}
Nous allons maintenant manipuler le champ ts (de type timestamp ) de la table posts .
La requête
**Exemple 8-475-e01.** Exemple ou sortie extraite du PDF\, page 475\.
```text
SELECT * FROM posts WHERE to_char(ts,'YYYYMM')='201302'
```
retourne tous les enregistrements de février 2013. Examiner son plan d’exécution. Où est le problème?
**Exemple 8-475-e02.** Exemple ou sortie extraite du PDF\, page 475\.
```text
EXPLAIN ANALYZE
SELECT *
FROM posts
WHERE to_char(ts,'YYYYMM')='201302' ;
```
Le plan 18 est un parcours complet de la table :
**Exemple 8-475-e03.** Exemple ou sortie extraite du PDF\, page 475\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on posts (cost=0.00..166152.00 rows=25000 width=116) (actual
time=2825.917..4833.644 rows=20160.00 loops=1)
Filter: (to_char(ts, 'YYYYMM'::text) = '201302'::text)
Rows Removed by Filter: 4979840
Planning Time: 0.170 ms
Execution Time: 4846.098 ms
```
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 :
**Exemple 8-475-e05.** Exemple ou sortie extraite du PDF\, page 475\.
```text
EXPLAIN ANALYZE
SELECT *
FROM posts
WHERE ts >= '2013-02-01'
AND ts < '2013-03-01' ;
```
18 https://explain.dalibo.com/plan/ATT
Le plan 19 montre que l’index est maintenant utilisable :
**Exemple 8-476-e01.** Exemple ou sortie extraite du PDF\, page 476\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using posts_ts_idx on posts (cost=0.43..1143.80 rows=23111 width=116)
(actual time=0.110..21.781 rows=20160.00 loops=1)
Index Cond: ((ts >= '2013-02-01 00:00:00+01'::timestamp with time zone) AND (ts <
'2013-03-01 00:00:00+01'::timestamp with time zone))
Index Searches: 1
Planning Time: 0.261 ms
```
**Exemple 8-476-e02.** Exemple ou sortie extraite du PDF\, page 476\.
```text
Execution Time: 34.743 ms
```
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. (Indice : il est possible de générer la liste de tous les dimanches de l’année 2013 avec : 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) :
**Exemple 8-476-e03.** Exemple ou sortie extraite du PDF\, page 476\.
```text
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 :
**Exemple 8-476-e04.** Exemple ou sortie extraite du PDF\, page 476\.
```text
WITH premiersjours AS (
SELECT '2000-01-01'::timestamp + i * interval '1 year' AS jan1
FROM generate_series(1, 30) i
),
dimanches AS (
```
**Exemple 8-476-e05.** Exemple ou sortie extraite du PDF\, page 476\.
```text
SELECT jan1,
jan1 + mod(13-extract(dow FROM (jan1 - interval '1 day'))::int, 7) + interval '1 day' AS dim1
```
**Exemple 8-476-e06.** Exemple ou sortie extraite du PDF\, page 476\.
```text
FROM premiersjours
)
SELECT jan1, dim1
FROM dimanches ;
```
**Exemple 8-476-e07.** Exemple ou sortie extraite du PDF\, page 476\.
```text
SELECT i AS debut,
i + INTERVAL '1 day' AS fin
```
**Exemple 8-476-e08.** Exemple ou sortie extraite du PDF\, page 476\.
```text
FROM generate_series(
'2013-01-06 00:00:00',
'2013-12-31 00:00:00',
INTERVAL '7 days'
) g(i) ;
```
19 https://explain.dalibo.com/plan/GDY
**Tableau 8-477-t01.** Tableau ou résultat de commande extrait du PDF\, page 477\.
| debut | fin |
| --- | --- |
| 2013-01-06 00:00:00+01 | 2013-01-07 00:00:00+01 |
| 2013-01-13 00:00:00+01 | 2013-01-14 00:00:00+01 |
**Exemple 8-477-e01.** Exemple ou sortie extraite du PDF\, page 477\.
```text
...
```
**Tableau 8-477-t02.** Tableau ou résultat de commande extrait du PDF\, page 477\.
| Colonne 1 | Colonne 2 |
| --- | --- |
| 2013-12-29 00:00:00+01 | 2013-12-30 00:00:00+01 |
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 :
**Exemple 8-477-e02.** Exemple ou sortie extraite du PDF\, page 477\.
```text
EXPLAIN ANALYZE
WITH dimanches AS (
SELECT i AS debut,
i + INTERVAL '1 day' AS fin
```
**Exemple 8-477-e03.** Exemple ou sortie extraite du PDF\, page 477\.
```text
FROM generate_series(
' 2013-01-06 00:00:00',
' 2013-12-31 00:00:00',
INTERVAL '7 days'
) g(i)
```
**Exemple 8-477-e04.** Exemple ou sortie extraite du PDF\, page 477\.
```text
SELECT posts.*
FROM posts
JOIN dimanches
ON (posts.ts >= dimanches.debut AND posts.ts < dimanches.fin) ;
```
Le plan 20 devient :
**Exemple 8-477-e05.** Exemple ou sortie extraite du PDF\, page 477\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Nested Loop (cost=0.44..1286150.50 rows=28888889 width=116) (actual
time=0.127..138.875 rows=37440.00 loops=1)
-> Function Scan on generate_series g (cost=0.00..0.52 rows=52 width=8) (actual
time=0.042..0.128 rows=52.00 loops=1)
-> Index Scan using posts_ts_idx on posts (cost=0.43..19178.09 rows=555556
width=116) (actual time=0.059..1.206 rows=720.00 loops=52)
Index Cond: ((ts >= g.i) AND (ts < (g.i + '1 day'::interval)))
Index Searches: 52
Planning Time: 0.906 ms
```
**Exemple 8-477-e06.** Exemple ou sortie extraite du PDF\, page 477\.
```text
Execution Time: 173.315 ms
```
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?
**Exemple 8-477-e07.** Exemple ou sortie extraite du PDF\, page 477\.
```text
SELECT * FROM posts
```
20 https://explain.dalibo.com/plan/nN5
**Exemple 8-478-e01.** Exemple ou sortie extraite du PDF\, page 478\.
```text
WHERE id_article =
(SELECT max(id_article) * 0.333
FROM posts
) ;
```
Le plan 21 est :
**Exemple 8-478-e02.** Exemple ou sortie extraite du PDF\, page 478\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on posts (cost=0.48..166152.48 rows=25000 width=116) (actual
time=556.853..1835.350 rows=1000.00 loops=1)
Filter: ((id_article)::numeric = (InitPlan 2).col1)
Rows Removed by Filter: 4999000
InitPlan 2
-> Result (cost=0.46..0.48 rows=1 width=32) (actual time=0.081..0.090
rows=1.00 loops=1)
InitPlan 1
-> Limit (cost=0.43..0.46 rows=1 width=4) (actual time=0.069..0.075
rows=1.00 loops=1)
-> Index Only Scan Backward using posts_id_article_id_post on
posts posts_1 (cost=0.43..142391.12 rows=5000000 width=4) (actual
time=0.065..0.067 rows=1.00 loops=1)
Index Cond: (id_article IS NOT NULL)
Heap Fetches: 0
Index Searches: 1
Planning Time: 0.430 ms
Execution Time: 1836.592 ms
```
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 :
**Exemple 8-478-e04.** Exemple ou sortie extraite du PDF\, page 478\.
```text
Filter: ((id_article)::numeric = (InitPlan 2).col1)
```
(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!
**Exemple 8-478-e05.** Exemple ou sortie extraite du PDF\, page 478\.
```text
SELECT max(id_article) * 0.333
FROM posts
\gdesc
```
**Tableau 8-478-t01.** Tableau ou résultat de commande extrait du PDF\, page 478\.
| Column | Type |
| --- | --- |
| ?column? | numeric |
La conversion du critère en int peut se faire à plusieurs endroits. Par exemple :
**Exemple 8-478-e06.** Exemple ou sortie extraite du PDF\, page 478\.
```text
SELECT * FROM posts
```
21 https://explain.dalibo.com/plan/6GI
**Exemple 8-479-e01.** Exemple ou sortie extraite du PDF\, page 479\.
```text
WHERE id_article =
(SELECT max(id_article) * 0.333
FROM posts
)::int ;
```
Et l’index est donc utilisable immédiatement :
**Exemple 8-479-e02.** Exemple ou sortie extraite du PDF\, page 479\.
```text
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using posts_id_article_id_post on posts (cost=0.91..1799.95 rows=1009
width=116) (actual time=0.986..2.344 rows=1000.00 loops=1)
Index Cond: (id_article = ((InitPlan 2).col1)::integer)
Index Searches: 1
```
**Exemple 8-479-e03.** Exemple ou sortie extraite du PDF\, page 479\.
```text
InitPlan 2
-> Result (cost=0.46..0.48 rows=1 width=32) (actual time=0.048..0.055
rows=1.00 loops=1)
InitPlan 1
-> Limit (cost=0.43..0.46 rows=1 width=4) (actual time=0.034..0.039
rows=1.00 loops=1)
-> Index Only Scan Backward using posts_id_article_id_post on
posts posts_1 (cost=0.43..142391.12 rows=5000000 width=4) (actual
time=0.031..0.033 rows=1.00 loops=1)
Index Cond: (id_article IS NOT NULL)
Heap Fetches: 0
Index Searches: 1
Planning Time: 0.288 ms
Execution Time: 3.124 ms
```
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 !
## Travaux pratiques et solutions {#devpg-tp-ch8}
Les pages de travaux pratiques et leurs solutions sont conservées dans le manuel complet, pages PDF 458-479.
Pour poursuivre la lecture, voir [le chapitre suivant](/fr/devpg/09-plpgsql-langages-pl/#devpg-ch9).
Pour revenir au chapitre précédent, voir [le chapitre précédent](/fr/devpg/07-comprendre-explain/#devpg-ch7).