• Équivalent ensembliste d'une relation
• Composé principalement de
  • colonnes ordonnées
  • contraintes

• Définition de son nom
• Définition de ses colonnes
  • nom, type, contraintes éventuelles
• Clauses de stockage
• CREATE TABLE

• DEFAULT
  • affectation implicite
• Utiliser directement par les types sériés

• Création d'une table à partir d'une autre table
  • CREATE TABLE ... (LIKE table clause_inclusion)
• Avec les valeurs par défaut des colonnes :
  • INCLUDING DEFAULTS
• Avec ses autres contraintes :
  • INCLUDING CONSTRAINTS
• Avec ses index :
  • INCLUDING INDEXES

• ALTER TABLE
• Définition de la table
  • renommage de la table
  • ajout/modification/suppression d'une colonne
  • déplacement dans un schéma différent
  • changement du propriétaire
• Définition des colonnes
  • renommage d'une colonne
  • changement de type d'une colonne
• Définition des contraintes
  • ajout/suppression d'une contrainte

• Supprimer une table :

  DROP TABLE nom_table;

• Supprimer une table et tous les objets dépendants :

  DROP TABLE nom_table CASCADE;

• ACID
  • Cohérence
  • une transaction amène la base d'un état stable à un autre
• Assurent la cohérence des données
  • unicité des enregistrements
  • intégrité référentielle
  • vérification des valeurs
  • identité des enregistrements
  • règles sémantiques

• Identifie une ligne de manière unique
• Une seule clé primaire par table
• Une ou plusieurs colonnes
• À choisir parmi les clés candidates
  • parfois, utiliser une clé artificielle

Construction :

[CONSTRAINT nom_contrainte]
PRIMARY KEY ( nom_colonne [, ... ] )

• Garantie l'unicité des valeurs d'une ou plusieurs colonnes
• Permet les valeurs NULL
• Clause UNIQUE
• Contrainte UNIQUE != index UNIQUE

Construction :

[ CONSTRAINT nom_contrainte]
{ UNIQUE ( nom_colonne [, ... ] )

• Contrainte d'intégrité référentielle
  • ou Clé étrangère
• Référence une clé primaire ou un groupe de colonnes UNIQUE et NOT NULL
• Garantie l'intégrité des données
• FOREIGN KEY

• Vérification complète ou partielle d'une clé étrangère
• MATCH
  • MATCH FULL (complète)
  • MATCH SIMPLE (partielle)

• Identité d'un enregistrement
• GENERATED ... AS IDENTITY
  • ALWAYS
  • BY DEFAULT
• Préférer à serial
• Unicité non garantie sans contrainte explicite !

• Que faire en cas de mise à jour d'une clé primaire ?
  • les clés étrangères seront fausses
• ON UPDATE
• ON DELETE
• Définition d'une action au niveau de la clé étrangère
  • interdiction
  • propagation de la mise à jour
  • NULL
  • valeur par défaut

• Présence d'une valeur
  • NOT NULL
• Vérification de la valeur d'une colonne
  • CHECK

• Vérifications après chaque ordre SQL
  • problèmes de cohérence
• Différer les vérifications de contraintes
  • clause DEFERRABLE, NOT DEFERRABLE
  • INITIALLY DEFERED, INITIALLY IMMEDIATE

• Un trigger
  • si une contrainte porte sur plusieurs tables
  • si sa vérification nécessite une sous-requête
• Préférer les contraintes déclaratives

• SELECT peut lire les données d'une table ou plusieurs tables
  • mais ne peut pas les mettre à jour
• Ajout de données dans une table
  • INSERT
• Modification des données d'une table
  • UPDATE
• Suppression des données d'une table
  • DELETE

• Ajoute des lignes à partir des données de la requête
• Ajoute des lignes à partir d'une requête SELECT
• Syntaxe :

INSERT INTO nom_table [ ( nom_colonne [, ...] ) ]
    { liste_valeurs | requete }

• L'ordre physique peut changer dans le temps
  • résultats incohérents
  • requêtes en erreurs

• Ordre UPDATE
• Met à jour une ou plusieurs colonnes d'une même ligne
  • à partir des valeurs de la requête
  • à partir des anciennes valeurs
  • à partir d'une requête SELECT
  • à partir de valeurs d'une autre table

• Supprime les lignes répondant au prédicat
• Syntaxe :

DELETE FROM nom_table [ [ AS ] alias ]
    [ WHERE condition

• Spécifique à PostgreSQL
• Permet de retourner les lignes complètes ou partielles résultants de INSERT, UPDATE ou DELETE
• Syntaxe :

requete_sql RETURNING ( * | expression )

• ACID
  • atomicité
  • un traitement se fait en entier ou pas du tout
• TCL pour Transaction Control Language
  • valide une transaction
  • annule une transaction
  • points de sauvegarde

• Par défaut, PostgreSQL fonctionne en auto-commit
  • à moins d'ouvrir explicitement une transaction
• Ouvrir une transaction
  • BEGIN TRANSACTION

• Valider une transaction
  • COMMIT
• Annuler une transaction
  • ROLLBACK
• Sans validation, une transaction est forcément annulée

• Certains langages implémentent des méthodes de gestion des transactions
  • PHP, Java, etc.
• Utiliser ces méthodes prioritairement

• Certains traitements dans une transaction peuvent être annulés
  • mais la transaction est atomique
• Définir un point de sauvegarde
  • SAVEPOINT nom_savepoint
• Valider le traitement depuis le dernier point de sauvegarde
  • RELEASE SAVEPOINT nom_savepoint
• Annuler le traitement depuis le dernier point de sauvegarde
  • ROLLBACK TO SAVEPOINT nom_savepoint

• SQL : toujours un traitement d'ensembles d'enregistrements
  • c'est le côté relationnel
• Pour les définitions d'objets
  • CREATE, ALTER, DROP
• Pour les données
  • INSERT, UPDATE, DELETE

N'hésitez pas, c'est le moment !

• Après la définition des objets, leur lecture et leur écriture
• Aller plus loin dans l'écriture de requêtes
  • avec les jointures
  • avec les requêtes intégrées

• Valeur NULL
• Agrégats, GROUP BY, HAVING
• Sous-requêtes
• Jointures
• Expression conditionnelle CASE
• Opérateurs ensemblistes : UNION, EXCEPT, INTERSECT

• Comprendre l'intérêt du NULL
• Savoir écrire des requêtes complexes

• Comment représenter une valeur que l'on ne connait pas ?
  • Valeur NULL
• Trois sens possibles pour NULL :
  • valeur inconnue
  • valeur inapplicable
  • absence de valeur
• Logique 3 états

• Chris J. Date a écrit :
  • 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.
• Éviter d'utiliser NULL le plus possible
  • utiliser NULL correctement lorsqu'il le faut

• Assignation de NULL pour INSERT et UPDATE
• Explicitement :
  • NULL est indiqué explicitement dans les assignations
• Implicitement :
  • la colonne n'est pas affectée par INSERT
  • et n'a pas de valeur par défaut
• Empêcher la valeur NULL
  • contrainte NOT NULL

• Utilisation dans un calcul
  • propagation de NULL
• NULL est inapplicable
  • le résultat vaut NULL

• Dans un prédicat du WHERE :
  • opérateur IS NULL ou IS NOT NULL
• AND :
  • vaut false si NULL AND false
  • vaut NULL si NULL AND true ou NULL AND NULL
• OR :
  • vaut true si NULL OR true
  • vaut NULL si NULL OR false ou NULL OR NULL

• Opérateurs d'agrégats
  • ignorent NULL
  • sauf count(*)

• Remplacer NULL par une autre valeur
  • COALESCE(attribut, ...);

• Regroupement de données
• Calculs d'agrégats

• Regroupement de données :

  GROUP BY expression [, ...]

• Chaque groupe de données est ensuite représenté sur une seule ligne
• Permet d'appliquer des calculs sur les ensembles regroupés
  • comptage, somme, moyenne, etc.

• Effectue un calcul sur un ensemble de valeurs
  • somme, moyenne, etc.
• Retourne NULL si l'ensemble est vide
  • sauf count()

• Comptage :

  count(expression)

• compte les lignes : count(*)
  • compte les valeurs renseignées : count(colonne)

• Valeur minimale :

  min(expression)

• Valeur maximale :

  max(expression)

• Moyenne :

  avg(expression)

• Somme :

  sum(expression)

• Écart-type :

  stddev(expression)

• Variance :

  variance(expression)

• Possible d'avoir plusieurs paramètres sur la même fonction d'agrégat
• Quelques exemples
  • pente, regr_slope(Y,X)
  • intersection avec l'axe des ordonnées, regr_intercept(Y,X)
  • indice de corrélation, corr (Y,X)

• Filtrer sur des regroupements
  • HAVING
• WHERE s'applique sur les lignes lues
• HAVING s'applique sur les lignes groupées

• Corrélation requête/sous-requête
• Sous-requêtes retournant une seule ligne
• Sous-requêtes retournant une liste de valeur
• Sous-requêtes retournant un ensemble
• Sous-requêtes retournant un ensemble vide ou non-vide

• Fait référence à la requête principale
• Peut utiliser une valeur issue de la requête principale

• Une requête imbriquée dans une autre requête
• Le résultat de la requête principale dépend du résultat de la sous-requête
• Encadrée par des parenthèses : ( et )

• La sous-requête ne retourne qu'une seule ligne
  • sinon une erreur est levée
• Positionnée
  • au niveau de la liste des expressions retournées par SELECT
  • au niveau de la clause WHERE
  • au niveau d'une clause HAVING

• La sous-requête retourne
  • plusieurs lignes
  • sur une seule colonne
• Positionnée
  • avec une clause IN
  • avec une clause ANY
  • avec une clause ALL

expression IN (sous-requete)

• L'expression de gauche est évaluée et vérifiée avec la liste de valeurs de droite
• IN vaut true
  • si l'expression de gauche correspond à un élément de la liste de droite
• IN vaut false
  • si aucune correspondance n'est trouvée et la liste ne contient pas NULL
• IN vaut NULL
  • si l'expression de gauche vaut NULL
  • si aucune valeur ne correspond et la liste contient NULL

expression NOT IN (sous-requete)

• L'expression de droite est évaluée et vérifiée avec la liste de valeurs de gauche
• NOT IN vaut true
  • si aucune correspondance n'est trouvée et la liste ne contient pas NULL
• NOT IN vaut false
  • si l'expression de gauche correspond à un élément de la liste de droite
• NOT IN vaut NULL
  • si l'expression de gauche vaut NULL
  • si aucune valeur ne correspond et la liste contient NULL

expression operateur ANY (sous-requete)

• L'expression de gauche est comparée au résultat de la sous-requête avec l'opérateur donné
• La ligne de gauche est retournée
  • si le résultat d'au moins une comparaison est vraie
• La ligne de gauche n'est pas retournée
  • si aucun résultat de la comparaison n'est vrai
  • si l'expression de gauche vaut NULL
  • si la sous-requête ramène un ensemble vide

expression operateur ALL (sous-requete)

• L'expression de gauche est comparée à tous les résultats de la sous-requête avec l'opérateur donné
• La ligne de gauche est retournée
  • si tous les résultats des comparaisons sont vrais
  • si la sous-requête retourne un ensemble vide
• La ligne de gauche n'est pas retournée
  • si au moins une comparaison est fausse
  • si au moins une comparaison est NULL

• La sous-requête retourne
  • plusieurs lignes
  • sur plusieurs colonnes
• Positionnée au niveau de la clause FROM
• Nommée avec un alias de table

EXISTS (sous-requete)

• Intéressant avec une corrélation
• La clause EXISTS vérifie la présence ou l'absence de résultats
  • vrai si l'ensemble est non vide
  • faux si l'ensemble est vide

• Produit cartésien
• Jointure interne
• Jointures externes
• Jointure ou sous-requête ?

• Clause CROSS JOIN
• Réalise toutes les combinaisons entre les lignes d'une table et les lignes d'une autre
• À éviter dans la mesure du possible
  • peu de cas d'utilisation
  • peu performant

• Clause INNER JOIN
  • meilleure lisibilité
  • facilite le travail de l'optimiseur
• Joint deux tables entre elles
  • Selon une condition de jointure

• Condition de jointure par prédicats :

  table1 [INNER] JOIN table2 ON prédicat [...]

• Condition de jointure implicite par liste des colonnes impliquées :

  table1 [INNER] JOIN table2 USING (colonne [, ...])

• Liste des colonnes implicites :

  table1 NATURAL [INNER] JOIN table2

• Jointure externe à gauche
  • ramène le résultat de la jointure interne
  • ramène l'ensemble de la table de gauche qui ne peut être joint avec la table de droite
  • les attributs de la table de droite sont alors NULL

• Jointure externe à droite
  • ramène le résultat de la jointure interne
  • ramène l'ensemble de la table de droite qui ne peut être joint avec la table de gauche
  • les attributs de la table de gauche sont alors NULL

• Ramène le résultat de la jointure interne
• Ramène l'ensemble de la table de gauche qui ne peut être joint avec la table de droite
  • les attributs de la table de droite sont alors NULL
• Ramène l'ensemble de la table de droite qui ne peut être joint avec la table de gauche
  • les attributs de la table de gauche sont alors NULL

• Condition de jointure par prédicats :

  table1 LEFT [OUTER] JOIN table2 ON prédicat [...]

• Condition de jointure implicite par liste des colonnes impliquées :

  table1 LEFT [OUTER] JOIN table2 USING (colonne [, ...])

• Liste des colonnes implicites :

  table1 NATURAL LEFT [OUTER] JOIN table2

• Condition de jointure par prédicats :

  table1 RIGHT [OUTER] JOIN table2 ON prédicat [...]

• Condition de jointure implicite par liste des colonnes impliquées :

  table1 RIGHT [OUTER] JOIN table2 USING (colonne [, ...])

• Liste des colonnes implicites :

  table1 NATURAL RIGHT [OUTER] JOIN table2

• Condition de jointure par prédicats :

  table1 FULL OUTER JOIN table2 ON prédicat [...]

• Condition de jointure implicite par liste des colonnes impliquées :

  table1 FULL OUTER JOIN table2 USING (colonne [, ...])

• Liste des colonnes implicites :

  table1 NATURAL FULL OUTER JOIN table2

• Jointures
  • algorithmes très efficaces
  • ne gèrent pas tous les cas
• Sous-requêtes
  • parfois peu performantes
  • répondent à des besoins non couverts par les jointures

• Équivalent à l'instruction switch en C ou Java
• Emprunté au langage Ada
• Retourne une valeur en fonction du résultat de tests

• Comportement procédural
  • les expressions sont évaluées dans l'ordre d'apparition
• Transtypage
  • le type du retour de l'expression dépend du type de rang le plus élevé de toute l'expression
• Imbrication
  • des expressions CASE à l'intérieur d'autres expressions CASE
• Clause ELSE
  • recommandé

• UNION
• INTERSECT
• EXCEPT

• Regroupement avec dédoublonnage :

  requete_select1 UNION requete_select2

• Regroupement sans dédoublonnage :

  requete_select1 UNION ALL requete_select2

• Intersection de deux ensembles avec dédoublonnage :

  requete_select1 INTERSECT requete_select2

• Intersection de deux ensembles sans dédoublonnage :

  requete_select1 INTERSECT ALL requete_select2

• Différence entre deux ensembles avec dédoublonnage :

  requete_select1 EXCEPT requete_select2

• Différence entre deux ensembles sans dédoublonnage :

  requete_select1 EXCEPT ALL requete_select2

• Possibilité d'écrire des requêtes complexes
• C'est là où PostgreSQL est le plus performant

N'hésitez pas, c'est le moment !

• Aller encore plus loin
  • tout en évitant le pire
• Appréhender de nouveaux objets
  • fonctions utiles
  • vues
• Utiliser des requêtes préparées

• Fonctions de base
• Vues
• Requêtes préparées
• Index
• Ce qu'il ne faut pas faire

• Utiliser des objets avancées
• Gagner en performance
• Éviter les pièges les plus fréquents

• Transtypage
• Manipulation de chaines
• Manipulation de types numériques
• Manipulation de dates
• Génération de jeu de données

• Conversion d'un type de données vers un autre type de données
• CAST (expression AS type)
• expression::type

• Concaténation : chaîne1 || chaîne2
• Longueur de la chaîne : char_length(chaîne)
• Conversion en minuscules : lower(chaîne)
• Conversion en majuscules : upper(chaîne)

• Extrait une chaîne à partir d'une autre : substring(chaîne [from int] [for int])
• Emplacement d'une sous-chaîne : position(sous-chaîne in chaîne)

• Opérations arithmétiques
• Manipulation de types numériques
• Génération de données

• Addition : +
• Soustraction : -
• Multiplication : *
• Division : /
• Reste (modulo) : %

• Arrondi : round(numeric)

• Troncature : trunc(numeric [, precision])

• Entier le plus petit : floor(numeric)

• Entier le plus grand : ceil(numeric)

• Générer une suite d'entiers : generate_series(borne_debut, borne_fin, intervalle)

• Générer un nombre aléatoire : random()

• Obtenir la date et l'heure courante
• Manipuler des dates
• Opérations arithmétiques
• Formatage de données

• Retourne la date courante : current_date
• Retourne l'heure courante : current_time
• Retourne la date et l'heure courante : current_timestamp

• Âge
  • Par rapport à la date courante : age(timestamp)
  • Par rapport à une date de référence : age(timestamp, timestamp)

• Troncature d'une date : date_trunc(text, timestamp)
• Exemple : date_trunc('month' from date_naissance)
• Extrait une composante de la date : extract(text, timestamp)
• Exemple : extract('year' from date_naissance)

• Opérations arithmétiques sur timestamp, time ou date
  • date/time - date/time = interval
  • date/time + time = date/time
  • date/time + interval = date/time
• Opérations arithmétiques sur interval
  • interval * numeric = interval
  • interval / numeric = interval
  • interval + interval = interval

• Conversion d'une date en chaîne de caractères : to_char(timestamp, text)
• Exemple : to_char(current_timestamp, 'DD/MM/YYYY HH24:MI:SS')

• Conversion d'une chaîne de caractères en date : to_date(text, text) to_date('05/12/2000', 'DD/MM/YYYY')
• Conversion d'une chaîne de caractères en timestamp : to_timestamp(text, text) to_timestamp('05/12/2000 12:00:00', 'DD/MM/YYYY HH24:MI:SS')
• Paramètre datestyle

• Générer une suite de timestamp : generate_series(timestamp_debut, timestamp_fin, intervalle)

• Tables virtuelles
  • définies par une requête SELECT
  • définition stockée dans le catalogue de la base de données
• Objectifs
  • masquer la complexité d'une requête
  • masquer certaines données à l'utilisateur

• Une vue porte un nom au même titre qu'une table
  • elle sera nommée avec les mêmes règles
• Ordre de création d'une vue : CREATE VIEW vue (colonne ...) AS SELECT ...

• Une vue est lue comme une table
  • SELECT * FROM vue;

• Sécuriser une vue
  • droits avec GRANT et REVOKE
• Utiliser les vues comme moyen de filtrer les lignes est dangereux
  • option security_barrier

• Trigger INSTEAD OF
• Updatable view (PostgreSQL 9.3)

• Prolifération des vues
  • créer une vue doit se justifier
  • ne pas créer une vue par table

• Exécution en deux temps
  • préparation du plan d'exécution de la requête
  • exécution de la requête en utilisant le plan préparé
• Objectif :
  • éviter simplement les injections SQL
  • améliorer les performances

• PREPARE, préparation du plan d'exécution d'une requête
• EXECUTE, passage des paramètres de la requête et exécution réelle
• L'implémentation dépend beaucoup du langage de programmation utilisé
  • le connecteur JDBC supporte les requêtes préparées
  • le connecteur PHP/PDO également

• Comment gagner en performance
• Index
  • représentation valeur / pointeur
  • arbre de valeurs

• L'index permet une recherche plus rapide
• Il n'a besoin de lire que quelques blocs
  • et non pas la table entière

• Accélère la recherche
  • mais ralentit les écritures
• Une écriture dans la table doit mettre à jour l'index
• Faire attention à ne pas créer des index inutiles

• Un index ne sert pas qu'à la recherche
• Les données étant triées, il sert aussi à accélérer les tris

• N'indexer qu'une partie des données
• Requête typique : SELECT * FROM taches WHERE fait IS false ORDER BY date_rendu;
• Index typique : CREATE INDEX ON taches(date_rendu) WHERE fait IS false;

• Indexer le résultat d'une expression
• Fonctions immuables seulement
• Requête typique : SELECT * FROM employes WHERE upper(nom)='DUPONT';
• Index typique : CREATE INDEX ON employes(upper(nom));

• Modélisation
• Écriture de requêtes
• Conception de l'application

• Concerne surtout les clés étrangères
• Parfois (souvent?) ignorées pour diverses raisons :
  • performances
  • flexibilité du modèle de données
  • compatibilité avec d'autres SGBD
  • héritage de MySQL/MyISAM
  • commodité de développement
• Conséquences
  • problèmes d'intégrité des données
  • procédures de vérification de cohérence des données

• Entité-Attribut-Valeur
• Pourquoi
  • 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
• Solutions
  • revenir sur la conception du modèle de données
  • utiliser un type de données plus adapté (hstore, jsonb)

• Pourquoi
  • stocker plusieurs attributs pour une même ligne
  • exemple : les différents numéros de téléphone d'une personne
• Pratique courante
  • ajouter plusieurs colonnes portant le même nom
• 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 de dépendance
  • utiliser un type tableau

• Objectif
  • représenter des valeurs décimales
• Pratique courante
  • utiliser le type float ou double
• Problèmes :
  • les types flottants ne stockent qu'une approximation de la valeur représentée
  • les erreurs d'arrondis se multiplient
  • les requêtes produisent des résultats faux
• Solutions
  • privilégier le type numeric(x, y) pour les calculs précis (financiers notamment)

• Utilisation de NULL
• Ordre implicite des colonnes
• Requêtes spaghetti
• Moteur de recherche avec LIKE

• 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 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
• Vite ingérable
  • 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
  • Ne pas la patcher !

• 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
• Solution
  • utiliser Full Text Search

• Des objets de plus en plus complexes
  • mais performants
  • et remplissant un besoin
• Des conseils
  • pour améliorer les performances
  • et pour éviter les pièges

N'hésitez pas, c'est le moment !

• Le matériel, le système et la configuration sont importants pour les performances
• Mais il est aussi essentiel de se préoccuper des requêtes et de leurs performances

• Exécution globale d'une requête
• Planificateur : utilité, statistiques et configuration
• EXPLAIN
• Nœuds d'un plan
• Outils

• L'exécution peut se voir sur deux niveaux
  • Niveau système
  • Niveau SGBD
• De toute façon, composée de plusieurs étapes

• Le client envoie une requête au serveur de bases de données
• Le serveur l'exécute
• Puis il renvoie le résultat au client

• Requêtes DDL
• Instructions TRUNCATE et COPY
• Pas de réécriture, pas de plans d'exécution... une exécution directe

• Prédicat
  • filtre de la clause WHERE
• Sélectivité
  • pourcentage de lignes retournées après application d'un prédicat
• Cardinalité
  • nombre de lignes d'une table
  • nombre de lignes retournées après filtrage

Cette requête d'exemple :

SELECT matricule, nom, prenom, nom_service, fonction, localisation
  FROM employes emp
  JOIN services ser ON (emp.num_service = ser.num_service)
 WHERE ser.localisation = 'Nantes';

L'objet de ce module est de comprendre son plan d'exécution :

 Hash Join  (cost=1.06..2.29 rows=4 width=48)
   Hash Cond: (emp.num_service = ser.num_service)
   ->  Seq Scan on employes emp  (cost=0.00..1.14 rows=14 width=35)
   ->  Hash  (cost=1.05..1.05 rows=1 width=21)
         ->  Seq Scan on services ser  (cost=0.00..1.05 rows=1 width=21)
               Filter: ((localisation)::text = 'Nantes'::text)

• Chargé de sélectionner le meilleur plan d'exécution
• Énumère tous les plans d'exécution
  • Tous ou presque...
• Calcule leur coût suivant des statistiques, un peu de configuration et beaucoup de règles
• Sélectionne le meilleur (le moins coûteux)

• SQL est un langage déclaratif
• Une requête décrit le résultat à obtenir
  • Mais pas la façon de l'obtenir
• C'est au planificateur de déduire le moyen de parvenir au résultat demandé

• 1ère règle : Récupérer le bon résultat
• 2è règle : Le plus rapidement possible
  • En minimisant les opérations disques
  • En préférant les lectures séquentielles
  • En minimisant la charge CPU
  • En minimisant l'utilisation de la mémoire

• L'optimiseur s'appuie sur :
  • un mécanisme de calcul de coûts
  • des statistiques sur les données
  • le schéma de la base de données

• À partir du modèle de données
  • suppression de jointures externes inutiles
• Transformation des sous-requêtes
  • certaines sous-requêtes transformées en jointures
• Appliquer les prédicats le plus tôt possible
  • réduit le jeu de données manipulé
• Intègre le code des fonctions SQL simples (inline)
  • évite un appel de fonction coûteux

• Stratégie d'accès aux lignes
  • Par parcours d'une table, d'un index, de TID, etc
• Stratégie d'utilisation des jointures
  • Ordre des jointures
  • Type de jointure (Nested Loop, Merge/Sort Join, Hash Join)
  • Ordre des tables jointes dans une même jointure
• Stratégie d'agrégation
  • Brut, trié, haché

• Modèle basé sur les coûts
  • quantifier la charge pour répondre à une requête
• Chaque opération a un coût :
  • lire un bloc selon sa position sur le disque
  • manipuler une ligne issue d'une lecture de table ou d'index
  • appliquer un opérateur

• L'optimiseur a besoin de connaître :
  • le coût relatif d'un accès séquentiel au disque.
  • le coût relatif d'un accès aléatoire au disque.
  • le coût relatif de la manipulation d'une ligne en mémoire.
  • le coût de traitement d'une donnée issue d'un index.
  • le coût d'application d'un opérateur.
  • le coût de la manipulation d'une ligne en mémoire pour un parcours parallèle parallélisé.
  • le coût de mise en place d'un parcours parallélisé.

• Toutes les décisions du planificateur se basent sur les statistiques
  • Le choix du parcours
  • Comme le choix des jointures
• Statistiques mises à jour avec ANALYZE
• Sans bonnes statistiques, pas de bons plans

• L'optimiseur utilise les statistiques pour déterminer :
  • la cardinalité d'un filtre -> quelle stratégie d'accès
  • la cardinalité d'une jointure -> quel algorithme de jointure
  • la cardinalité d'un regroupement -> quel algorithme de regroupement

• Taille
• Cardinalité
• Stocké dans pg_class
  • relpages et reltuples

• Nombre de valeurs distinctes
• Nombre d'éléments qui n'ont pas de valeur (NULL)
• Largeur d'une colonne
• Distribution des données
  • tableau des valeurs les plus fréquentes
  • histogramme de répartition des valeurs

• Les informations statistiques vont dans la table pg_statistic
  • mais elle est difficile à comprendre
  • mieux vaut utiliser la vue pg_stats
  • une table vide n'a pas de statistiques
• Taille et cardinalité dans pg_class
  • colonnes relpages et reltuples

• Une ligne par colonne de chaque table
• 3 colonnes d'identification
  • schemaname, tablename, attname
• 8 colonnes d'informations statistiques
  • inherited, null_frac, avg_width, n_distinct
  • most_common_vals, most_common_freqs, histogram_bounds
  • most_common_elems, most_common_elem_freqs, elem_count_histogram
  • correlation

• Pas par défaut
• CREATE STATISTICS
• Deux types de statistique
  • nombre de valeurs distinctes
  • dépendances fonctionnelles
• À partir de la version 10

• Une ligne par objet statistique
• 4 colonnes d'identification
  • stxrelid, stxname, stxnamespace, stxkeys
• 1 colonne pour connaître le type de statistiques géré
  • stxkind
• 2 colonnes d'informations statistiques
  • stxndistinct
  • stxdependencies

• Ordre SQL de calcul de statistiques
  • ANALYZE [ VERBOSE ] [ table [ ( colonne [, ...] ) ] ]
• Sans argument : base entière
• Avec argument : la table complète ou certaines colonnes seulement
• Prend un échantillon de chaque table
• Et calcule des statistiques sur cet échantillon
• Si table vide, conservation des anciennes statistiques

• Dépend principalement de la fréquence des requêtes DML
• Cron
  • Avec psql
  • Avec vacuumdb (option --analyze-only en 9.0)
• Autovacuum fait du ANALYZE
  • Pas sur les tables temporaires
  • Pas assez rapidement dans certains cas

• Se configure dans postgresql.conf
  • default_statistics_target = 100

• Configurable par colonne

  ALTER TABLE nom ALTER [ COLUMN ] colonne SET STATISTICS valeur;

• Par défaut, récupère 30000 lignes au hasard
  • 300 * default_statistics_target
• Va conserver les 100 valeurs les plus fréquentes avec leur fréquence

• Plan d'exécution
  • représente les différentes opérations pour répondre à la requête
  • sous forme arborescente
  • composé des nœuds d'exécution
  • plusieurs opérations simples mises bout à bout

• Nœud
  • opération simple : lectures, jointures, tris, etc.
  • unité de traitement
  • produit et consomme des données
• Enchaînement des opérations
  • chaque nœud produit les données consommées par le nœud parent
  • nœud final retourne les données à l'utilisateur

• Des options supplémentaires
  • ANALYZE
  • BUFFERS
  • COSTS
  • TIMING
  • VERBOSE
  • SUMMARY
  • FORMAT
• Donnant des informations supplémentaires très utiles

• Différence importante entre l'estimation du nombre de lignes et la réalité
• Boucles
  • appels très nombreux dans une boucle (nested loop)
  • opération lente sur lesquels PostgreSQL boucle
• Temps d'exécution conséquent sur une opération
• Opérations utilisant beaucoup de blocs (option BUFFERS)

• Détermine à partir des statistiques
  • cardinalité des prédicats
  • cardinalité des jointures
• Coût d'accès déterminé selon
  • des cardinalités
  • volumétrie des tables

• Un plan est composé de nœuds
  • certains produisent des données
  • d'autres consomment des données et les retournent
  • le nœud final retourne les données à l'utilisateur
  • chaque nœud consomme au fur et à mesure les données produites par les nœuds parents

• Seq Scan
• Parallel Seq Scan
• Function Scan
• et des parcours d'index

• Index Scan
• Index Only Scan
• Bitmap Index Scan
• Et leurs versions parallélisées

• PostgreSQL implémente les 3 algorithmes de jointures habituels :
  • Nested Loop (boucle imbriquée)
  • Hash Join (hachage de la table interne)
  • Merge Join (tri-fusion)
• Parallélisation possible
  • version 9.6 pour Nested Loop et Hash Join
  • version 10 pour Merge Join
• Et pour EXISTS, IN et certaines jointures externes :
  • Semi Join et Anti Join

• Un seul noeud de tri :
  • Sort
• Regroupement/Agrégation :
  • Aggregate
  • HashAggregate
  • GroupAggregate
  • Partial Aggregate/Finalize Aggregate

• Limit
• Unique
• Append (UNION ALL), Except, Intersect
• Gather
• InitPlan, Subplan, etc.

• L'optimiseur se trompe parfois
  • mauvaises statistiques
  • écriture particulière de la requête
  • problèmes connus de l'optimiseur

SELECT * FROM t1 WHERE c1=1 AND c2=1

• c1=1 est vrai pour 20% des lignes
• c2=1 est vrai pour 10% des lignes
• Le planificateur va penser que le résultat complet ne récupérera que 20% * 10% (soit 2%) des lignes
  • En réalité, ça peut aller de 0 à 10% des lignes
• Problème corrigé en version 10
  • CREATE STATISTICS pour des statistiques multi-colonnes

SELECT *
FROM commandes
WHERE extract('year' from date_commande) = 2014;

• L'optimiseur n'a pas de statistiques sur le résultat de la fonction extract
  • il estime la sélectivité du prédicat à 0.5%.

SELECT * FROM t1 WHERE c2 LIKE 'x%';

• PostgreSQL peut utiliser un index dans ce cas
• Si l'encodage n'est pas C, il faut déclarer l'index avec une classe d'opérateur
  • varchar_pattern_ops, text_pattern_ops, etc
• En 9.1, il faut aussi faire attention au collationnement
• Ne pas oublier pg_trgm (surtout en 9.1) et FTS

• Exemple
  • EXPLAIN avec LIMIT 199
  • EXPLAIN avec LIMIT 200
• Corrigé en 9.2

• DELETE lent
• Généralement un problème de clé étrangère

Delete  (actual time=111.251..111.251 rows=0 loops=1)
  ->  Hash Join  (actual time=1.094..21.402 rows=9347 loops=1)
        ->  Seq Scan on lot_a30_descr_lot
            (actual time=0.007..11.248 rows=34934 loops=1)
        ->  Hash  (actual time=0.501..0.501 rows=561 loops=1)
              ->  Bitmap Heap Scan on lot_a10_pdl
                  (actual time=0.121..0.326 rows=561 loops=1)
                    Recheck Cond: (id_fantoir_commune = 320013)
                    ->  Bitmap Index Scan on...
                        (actual time=0.101..0.101 rows=561 loops=1)
                          Index Cond: (id_fantoir_commune = 320013)
Trigger for constraint fk_lotlocal_lota30descrlot:
  time=1010.358 calls=9347
Trigger for constraint fk_nonbatia21descrsuf_lota30descrlot:
  time=2311695.025 calls=9347
Total runtime: 2312835.032 ms

SELECT DISTINCT t1.* FROM t1 JOIN t2 ON (t1.id=t2.t1_id);

• DISTINCT est souvent utilisé pour dédoublonner les lignes de t1
  • mais génère un tri qui pénalise les performances
• GROUP BY est plus rapide
• Une clé primaire permet de dédoublonner efficacement des lignes
  • à utiliser avec GROUP BY

• Trop de lignes retournées

• Prédicat incluant une transformation :

  WHERE col1 + 2 > 5

• Statistiques pas à jour ou peu précises

• Opérateur non-supporté par l'index :

  WHERE col1 <> 'valeur';

• Paramétrage de PostgreSQL : effective_cache_size

• NOT IN avec une sous-requête
  • à remplacer par NOT EXISTS
• Utilisation systématique de UNION au lieu de UNION ALL
  • entraîne un tri systématique
• Sous-requête dans le SELECT
  • utiliser LATERAL

• pgAdmin3
• explain.depesz.com
• pev
• auto_explain
• plantuner

• Vision graphique d'un EXPLAIN
• Une icône par nœud
• La taille des flèches dépend de la quantité de données
• Le détail de chaque nœud est affiché en survolant les nœuds

• Site web proposant un affichage particulier du EXPLAIN ANALYZE
• Il ne travaille que sur les informations réelles
• Les lignes sont colorées pour indiquer les problèmes
  • Blanc, tout va bien
  • Jaune, inquiétant
  • Marron, plus inquiétant
  • Rouge, très inquiétant
• Installable en local

• Site web proposant un affichage particulier du EXPLAIN ANALYZE
  • mais différent de celui de Depesz
• Fournir un plan d'exécution en JSON
• Installable en local

• Extension pour PostgreSQL >= 8.4
• Connaître les requêtes lentes est bien
• Mais difficile de connaître leur plan d'exécution au moment où elles ont été lentes
• D'où le module auto_explain

• Extension, pour PostgreSQL >= 8.4
• Suivant la configuration
  • Interdit l'utilisation de certains index
  • Force à zéro les statistiques d'une table vide

• Planificateur très avancé
• Mais faillible
• Cependant
  • ne pensez pas être plus intelligent que le planificateur

N'hésitez pas, c'est le moment !

• Quatre types de nœuds
  • Parcours (de table, d'index, de TID, etc.)
  • Jointures (Nested Loop, Sort/Merge Join, Hash Join)
  • Opérateurs sur des ensembles (Append, Except, Intersect, etc.)
  • Et quelques autres (Sort, Aggregate, Unique, Limit, Materialize)

• Ne prend rien en entrée
• Mais renvoie un ensemble de données
  • Trié ou non, filtré ou non
• Exemples typiques
  • Parcours séquentiel d'une table, avec ou sans filtrage des enregistrements produits
  • Parcours par un index, avec ou sans filtrage supplémentaire

• Parcours séquentiel de la table (Sequential Scan, ou SeqScan)
• Aussi appelé FULL TABLE SCAN par d'autres SGBD
• La table est lue entièrement
  • Même si seulement quelques lignes satisfont la requête
  • Sauf dans le cas de la clause LIMIT sans ORDER BY
• Elle est lue séquentiellement par bloc de 8 Ko
• Optimisation synchronize_seqscans

• Parcours aléatoire de l'index
• Pour chaque enregistrement correspondant à la recherche
  • Parcours non séquentiel de la table (pour vérifier la visibilité de la ligne)
• Sur d'autres SGBD, cela revient à un
  • INDEX RANGE SCAN, suivi d'un TABLE ACCESS BY INDEX ROWID
• Gros gain en performance quand le filtre est très sélectif
• L'ensemble de lignes renvoyé est trié

• En VO, Bitmap Index Scan / Bitmap Heap Scan
• Disponible à partir de la 8.1
• Diminuer les déplacements de la tête de lecture en découplant le parcours de l'index du parcours de la table
  • Lecture en un bloc de l'index
  • Lecture en un bloc de la partie intéressante de la table
• Autre intérêt : pouvoir combiner plusieurs index en mémoire
  • Nœud BitmapAnd
  • Nœud BitmapOr
• Coût de démarrage généralement important
  • Parcours moins intéressant avec une clause LIMIT

• Avant la 9.2, pour une requête de ce type
  • SELECT c1 FROM t1 WHERE c1<10
• PostgreSQL devait lire l'index et la table
  • car les informations de visibilité ne se trouvent que dans la table
• En 9.2, le planificateur peut utiliser la « Visibility Map »
  • nouveau nœud « Index Only Scan »
  • Index B-Tree (9.2+)
  • Index SP-GiST (9.2+)
  • Index GiST (9.5+) => Types : point, box, inet, range

• TID Scan
• Function Scan
• Values
• Result

• Prend deux ensembles de données en entrée
  • L'une est appelée inner (interne)
  • L'autre est appelée outer (externe)
• Et renvoie un seul ensemble de données
• Exemples typiques
  • Nested Loop, Merge Join, Hash Join

• Pour chaque ligne de la relation externe
  • Pour chaque ligne de la relation interne
  • Si la condition de jointure est avérée
    • Émettre la ligne en résultat
• L'ensemble externe n'est parcouru qu'une fois
• L'ensemble interne est parcouru pour chaque ligne de l'ensemble externe
  • Avoir un index utilisable sur l'ensemble interne augmente fortement les performances

• Trier l'ensemble interne
• Trier l'ensemble externe
• Tant qu'il reste des lignes dans un des ensembles
  • Lire les deux ensembles en parallèle
  • Lorsque la condition de jointure est avérée
  • Émettre la ligne en résultat
• Parcourir les deux ensembles triés (d'où Sort-Merge Join)
• Ne gère que les conditions avec égalité
• Produit un ensemble résultat trié
• Le plus rapide sur de gros ensembles de données

• Calculer le hachage de chaque ligne de l'ensemble interne
• Tant qu'il reste des lignes dans l'ensemble externe
  • Hacher la ligne lue
  • Comparer ce hachage aux lignes hachées de l'ensemble interne
  • Si une correspondance est trouvée
  • Émettre la ligne trouvée en résultat
• Ne gère que les conditions avec égalité
• Idéal pour joindre une grande table à une petite table
• Coût de démarrage important à cause du hachage de la table

SELECT pg_class.relname, pg_class.reltuples
FROM pg_class
LEFT JOIN pg_namespace
       ON pg_class.relnamespace=pg_namespace.oid;

• Un index unique existe sur la colonne oid de pg_namespace
• Jointure inutile
  • sa présence ne change pas le résultat
• PostgreSQL peut supprimer la jointure à partir de la 9.0

• Trouver le bon ordre de jointure est un point clé dans la recherche de performances
• Nombre de possibilités en augmentation factorielle avec le nombre de tables
• Si petit nombre, recherche exhaustive
• Sinon, utilisation d'heuristiques et de GEQO
  • Limite le temps de planification et l'utilisation de mémoire
  • GEQO remplacé par Simulated Annealing ? (recuit simulé en VF)

• Prend un ou plusieurs ensembles de données en entrée
• Et renvoie un ensemble de données
• Concernent principalement les requêtes sur des tables partitionnées ou héritées
• Exemples typiques
  • Append
  • Intersect
  • Except

• Prend plusieurs ensembles de données
• Fournit un ensemble de données en sortie
  • Non trié
• Utilisé par les requêtes
  • Sur des tables héritées (partitionnement inclus)
  • Ayant des UNION ALL et des UNION
  • Attention que le UNION sans ALL élimine les duplicats, ce qui nécessite une opération supplémentaire de tri

• Append avec optimisation
• Fournit un ensemble de données en sortie trié
• Utilisé par les requêtes
  • UNION ALL ou partitionnement/héritage
  • Utilisant des parcours triés
  • Idéal avec Limit

• Nœud HashSetOp Except
  • instructions EXCEPT et EXCEPT ALL
• Nœud HashSetOp Intersect
  • instructions INTERSECT et INTERSECT ALL

• Prend un ensemble de données en entrée
• Et renvoie un ensemble de données
• Exemples typiques
  • Sort
  • Aggregate
  • Unique
  • Limit
  • InitPlan, SubPlan

• Utilisé pour le ORDER BY
  • Mais aussi DISTINCT, GROUP BY, UNION
  • Les jointures de type Merge Join
• Gros délai de démarrage
• Trois types de tri
  • En mémoire, tri quicksort
  • En mémoire, tri top-N heapsort (si clause LIMIT)
  • Sur disque

• Agrégat complet
• Pour un seul résultat

• Hachage de chaque n-uplet de regroupement (group by)
• accès direct à chaque n-uplet pour appliquer fonction d’agrégat
• Intéressant si l'ensemble des valeurs distinctes tient en mémoire, dangereux sinon

• Reçoit des données déjà triées
• Parcours des données
  • Regroupement du groupe précédent arrivé à une donnée différente

• Reçoit des données déjà triées
• Parcours des données
  • Renvoi de la donnée précédente une fois arrivé à une donnée différente
• Résultat trié

• Permet de limiter le nombre de résultats renvoyés
• Utilisé par
  • clauses LIMIT et OFFSET d'une requête SELECT
  • fonctions min() et max() quand il n'y a pas de clause WHERE et qu'il y a un index
• Le nœud précédent sera de préférence un nœud dont le coût de démarrage est peu élevé (SeqScan, NestedLoop)