• L'optimisation doit porter sur les différents composants
  • Le serveur qui héberge le SGBDR : le matériel, la distribution et le kernel
  • Le moteur de la base de données : postgresql.conf
  • La base de données : l'organisation des fichiers de PostgreSQL
  • L'application en elle-même : le schéma, les requêtes et tout ce qui s'y rapporte
• Ce module se focalise sur ce dernier point

• Il est illusoire d'essayer d'optimiser une application sans déterminer au préalable les sources de ralentissement
• Loi de Pareto : « Le pourcentage de la population dont la richesse est supérieure à une valeur x est proportionnel à A/x^α » (Vilfredo Pareto, économiste du XIXe siècle)

• Principe de Pareto (dérivé) : 80% des effets sont produits par 20% des causes.

• L'optimisation doit donc être ciblée :
  • il s'agit de trouver ces « 20% » de causes.

• Utilisation de profiler
  • PostgreSQL : pgBadger, PoWA, pg_stat_statements, pg_stat_plans
  • Oracle : tkprof, statspack, AWR…
  • SQL Server : SQL Server Profiler

• 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)

• 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

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
• Définition simple par 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 »

• Un attribut (colonne) doit être atomique :

  • Modifier l'attribut sans en toucher un autre
  • Donnée correcte : boolean abs, boolean volant_a_gauche, enum couleur, etc. Difficile
  • Recherche efficace : accédé en entier dans une clause WHERE
  • Non respect = violation de la première forme normale

    Column      |  Type   |            Description             
----------------+---------+------------------------------------
immatriculation | text    | 
modele          | text    | 
couleur         | color   | Couleur vehicule (bleu,rouge,vert)
toit_ouvrant    | boolean | Option toit ouvrant
abs             | boolean | Option anti-blocage des roues
type_roue       | boolean | tole/aluminium
motricite       | boolean | 2 roues motrices / 4 roues motrices

NULL signifie habituellement :

• Valeur non renseignée
• Valeur inconnue

Dans tous les cas, c'est une absence d'information. Ou du moins la seule information est qu'on ne sait pas.

Une table remplie de NULLs est habituellement signe d'un problème de modélisation.

Plus rarement, on rencontre aussi :

• Une colonne de type varchar
• qui contient :
  • quelquefois un entier
  • quelquefois une date
  • un NULL
  • une chaîne autre
  • etc.
• À éviter comme la peste !

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

Comment lister tous les dba?

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)

• Parfois (souvent ?) ignorées pour diverses raisons :
  • faux gains de performance
  • flexibilité du modèle de données
  • compatibilité avec d'autres SGBD
  • commodité de développement
• 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

Pour les performances, on envisage souvent de distribuer la base sur plusieurs nœuds.

• La complexité augmente (au niveau du code applicatif et/ou des procédures d'exploitation)
  • le risque d'erreur avec lui (programmation, fausse manipulation)
• Le retour à un état stable après un incident est complexe

• Le SQL est un langage déclaratif :
  • on décrit le résultat et pas la façon de l'obtenir
  • comme Prolog
  • c'est le travail de la base de déterminer le traitement à effectuer
• Traitement ensembliste :
  • par opposition au traitement procédural
  • « on effectue des opérations sur des relations pour obtenir des relations »

Les opérateurs purement relationnels sont les suivants :

• Projection
  • Clause SELECT (choix des colonnes)
• Sélection
  • Clause WHERE (choix des enregistrements)
• Jointure
  • Clause FROM/JOIN (choix des tables)
• Bref, tout ce qui détermine sur quelles données on travaille

• Les autres opérateurs sont non-relationnels :
  • ORDER BY
  • GROUP BY/DISTINCT
  • HAVING
  • Sous-requête, vue
  • Fonction (classique, d'agrégat, analytique)
  • Jointure externe

Le volume de données récupéré a un impact sur les performances.

• N'accéder qu'aux tables nécessaires
• N'accéder qu'aux colonnes nécessaires
• Plus le volume de données à traiter est élevé, plus les opérations seront lentes :
  • Tris et Jointures
  • Éventuellement stockage temporaire sur disque pour certains algorithmes

SQL : langage ensembliste et déclaratif

• Ne pas faire de traitement unitaire par enregistrement
• Utiliser les jointures, ne pas accéder à chaque table une-par-une
• Une seule requête, parcours de curseur
• Fréquent avec les ORM

Une vue est simplement une requête pré-déclarée en base.

• C'est l'équivalent relationnel d'une fonction
• Quand elle est utilisée dans une autre requête, elle est initialement traitée comme une sous-requête
• Attention aux vues avec DISTINCT, GROUP BY etc.
  • Impossible de l'inliner
  • Barrière d'optimisation
  • Donc mauvaises performances
• Les vues sont dangereuses en termes de performance
  • masquent la complexité

Le problème est similaire à tout autre langage.

• Code spaghetti pour le SQL :
  • Écriture d'une requête à partir d'une autre requête
  • É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
  • Un changement de spécification est un changement de sens, au niveau relationnel, de la requête
  • Ne pas la patcher !

• Si IN, limiter le nombre d'enregistrements grâce à DISTINCT

  SELECT * FROM t1
    WHERE val IN (SELECT DISTINCT …)

• Éviter une requête liée :

  SELECT a,b
    FROM t1
    WHERE val IN (SELECT f(b))

• Certaines sous-requêtes sont l'expression de Semi-join ou Anti-join

  SELECT * FROM t1 WHERE fk
     [NOT] IN (SELECT pk FROM t2 WHERE xxx)
  SELECT * FROM t1 WHERE [NOT] EXISTS
     (SELECT 1 FROM t2 WHERE t2.pk=t1.fk AND xxx)
  SELECT t1.* FROM t1 LEFT JOIN t2 ON (t1.fk=t2.pk)
     WHERE t2.id IS [NOT] NULL`

• sont strictement équivalentes !
  • L'optimiseur les exécute à l'identique (sauf NOT IN)

L'accès aux données est coûteux.

• Quelle que soit la base
• Dialogue entre client et serveur
  • Plusieurs aller/retours potentiellement
• Analyse d'un langage complexe
  • SQL PostgreSQL : gram.y de 14000 lignes
• Calcul de plan :
  • langage déclaratif => converti en impératif à chaque exécution

Se connecter coûte cher :

• Vérification authentification, permissions
• Création de processus, de contexte d'exécution
• Éventuellement négotiation SSL
• Acquisition de verrous

=> Maintenir les connexions coté applicatif ou utiliser un pooler.

Les spécifications sont souvent procédurales, voire objet !

• Il faut prendre du recul, et réfléchir de façon ensembliste
  • On travaille sur des ensembles de données
  • On peut faire encore mieux avec les CTE (SQL:1999)

• Le schéma représente la modélisation des données
  • Une application n'a pas à y toucher lors de son fonctionnement normal
  • Parfois : tables temporaires locales à des sessions
  • Toujours voir si une autre solution est possible
• SQL manipule les données en flux continu :
  • chaque étape d'un plan d'exécution n'attend pas la fin de la précédente
  • Passer par une table temporaire est probablement une perte de temps

Un ordre SQL peut effectuer de nombreuses choses :

• Les moteurs SQL sont très efficaces, et évoluent en permanence
• Ils ont de nombreuses méthodes de tri, de jointure, qu'ils choisissent en fonction du contexte
• Si vous utilisez le langage SQL, votre requête profitera des futures évolutions
• Si vous codez tout dans votre programme, vous devrez le maintenir et l'améliorer
• Faites un maximum du côté SQL : agrégats, fonctions analytiques, tris, numérotations, CASE, etc.
• Commentez votre code avec -- et /* */

Encore une fois, prendre de la distance vis-à-vis des spécifications fonctionnelles :

• Si le client existe, le mettre à jour :
  • Le mettre à jour, et regarder combien d'enregistrements ont été mis à jour
• Si le client existe :
  • Surtout pas de COUNT(*), éventuellement un test de l'existence d'UN enregistrement
• Gérer les exceptions plutôt que de vérifier préalablement que les conditions sont remplies (si l'exception est rare)

• La bonne utilisation d'un index est un sujet à part entière :
  • sujet effleuré ici
  • excellent livre par Markus Winand : SQL Performance Explained
• Compromis insertion/sélection
• Objet technique :
  • ni dans la théorie relationnelle
  • ni dans la norme SQL

• Un index permet de :
  • trouver un enregistrement dans une table directement
  • récupérer une série d'enregistrements dans une table
  • voire récupérer directement l'enregistrement de l'index s'il contient toutes les colonnes nécessaires
• En complément, un index facilite :
  • certains tris
  • certains agrégats
• Et est utilisé pour les contraintes d'unicité !

Un index améliore les SELECT

Sans index :

=# SELECT * FROM t1 WHERE i = 10000;
Temps : 1760,017 ms

Avec index :

=# CREATE INDEX idx_t1_i ON t1 (i);
=# SELECT * FROM t1 WHERE i = 10000;
Temps : 27,711 ms

La présence d'un index ralentit les mises à jour :

=# INSERT INTO t1 SELECT i FROM generate_series(1, 10000000) i;
Temps : 39674,079 ms

=# CREATE INDEX idx_t1_i ON t1 (i);
=# INSERT INTO t1 SELECT i FROM generate_series(1, 10000000) i;
Temps : 94925,140 ms

Compromis à trouver :

• favoriser les lectures
• mais pas au détriment des écritures

• Seules un certain nombre de requêtes sont critiques
  • utilisation d'outil de profiling pour les identifier
  • le travail d'optimisation se porte sur celles-ci uniquement
• Détermination des requêtes critiques :
  • longues en temps cumulé, coûteuses en ressources serveur
  • longues et interactives, mauvais ressenti des utilisateurs

• PostgreSQL dispose d'index spécialisés :
  • GIN (Generalized Inverted Index)
  • GiST (Generalized Search Tree)
  • BRIN (Block Range INdex)

• Ils permettent d'indexer des données non-scalaires :
  • Intervalles de dates
  • formes géométriques, données géographiques
  • Trigrammes, Full Text
  • Tableaux

• Les index BRIN sont utiles pour les grosses volumétries
• Les données sont corrélées avec leur emplacement physique

• Si une fonction est appliquée à une colonne dans un prédicat :

  SELECT ... FROM table WHERE f(colonne)=C

  • l'optimiseur n'utilise pas d'index
  • dénote un problème probable de normalisation
• Analogie : chercher dans un dictionnaire français
  • WHERE anglais(mot)='cheval' => il faut traduire chaque mot lu

Usage classique :

• Recherche sans la casse

• Avec un index fonctionnel, l'optimiseur sait utiliser un index :

  CREATE INDEX index ON dictionnaire_fr(anglais(mot))

• Prise de verrous : ils ne sont relâchés qu'à la fin
  • COMMIT
  • ROLLBACK
• Validation des données sur le disque au COMMIT
  • Écriture synchrone : coûteux
• Faire des transactions qui correspondent au fonctionnel
• Si traitement lourd, préférer des transactions de grande taille

• Chaque transaction prend des verrous :
  • sur les objets (tables, index, etc.) pour empêcher au moins leur suppression ou modification de structure pendant leur travail
  • sur les enregistrements
  • libérés à la fin de la transaction : les transactions très longues peuvent donc être problématiques
• Sous PostgreSQL, on peut quand même lire un enregistrement en cours de modification : on voit l'ancienne version (MVCC)

• Du fait de ces verrous :
  • On peut avoir des deadlocks (verrous mortels)
  • En théorie, on peut les éviter (en prenant toujours les verrous dans le même ordre)
  • En pratique, ça n'est pas toujours possible ou commode
  • Les SGBD tuent une des transactions responsables du deadlock
  • Une application générant de nombreux deadlocks est ralentie

• Ce document s'appuie sur de nombreuses sources.

• Si vous souhaitez approfondir les points abordés :
  • The World and the Machine, Michael Jackson
  • The Art of SQL, Stéphane Faroult
  • Refactoring SQL Applications, Stéphane Faroult
  • SQL Performance Explained, Markus Winand
  • Introduction aux bases de données, Chris Date
  • Vidéos de Stéphane Faroult (roughsealtd) sous Youtube

• 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)

• Qu'est-ce qu'un index ?
• Comment indexer une base ?
• Les différents types d'index

• Anatomie d'un index
• Les index « simples »
• Méthodologie
• Indexation avancée
• Outillage

• Comprendre ce qu'est un index
• Maîtriser le processus de création d'index
• Connaître les différents types d'index et leurs cas d'usages

• Analogie : index dans une publication scientifique
  • Structure séparée, associant des clés (termes) à des localisations (pages)
  • Même principe pour un index dans un SGBD
• Structure de données spécialisée, plusieurs types
• Existe en dehors de la table

• Un index ne résout pas tout
• Importance de la conception du schéma de données
• Importance de l'écriture de requêtes SQL correctes

• Type d'index le plus courant
• Mais aussi le plus simple
• Utilisé pour les contraintes d'unicité
• Supporte les opérateurs suivants : <, <=, =, >=, >
• Supporte le tri
• Ne peut pas indexer des colonnes de plus de 2.6 Ko

• L'index n'est pas gratuit !
• Espace disque nécessaire
• Maintenance à chaque opération DML
• Mesurer la pertinence de l'index

• Possibilité d'indexer plusieurs colonnes :

  CREATE INDEX ON ma_table (id, name) ;

• L'ordre des colonnes est primordial
  • permet de répondre aux requêtes sur les premières colonnes de l'index
  • pour les autres, PostreSQL lira tout l'index ou ignorera l'index

• On indexe pour une requête, ou idéalement une collection de requête
• On n'indexe pas « une table »

• Identifier les requêtes nécessitant un index
• Créer les index permettant de répondre à ces requêtes
• Valider le fonctionnement, en rejouant la requête avec :

EXPLAIN (ANALYZE, BUFFERS)

• Indexation des colonnes faisant référence à une autre
• Performances des DML
• Performances des jointures

• Pas le bon type (CAST)
• Pas les bons opérateurs
• Sélectivité trop faible
• Index redondants

De nombreuses possibilités d'indexation avancée :

• Index multi-colonnes
• Index fonctionnels
• Index partiels
• Covering indexes
• Classes d'opérateurs
• GiN
• GiST
• BRIN
• Hash

• N'indexe qu'une partie des données :

  CREATE INDEX ON table (colonne) WHERE condition;

• Ne sert que si la clause exacte est respectée !
• Intérêt : index beaucoup plus petit !

• Données chaudes et froides
• Index pour une requête ayant une condition fixe
• Éviter les index de type :

CREATE INDEX ON index(une_colonne) WHERE une_colonne = 'test`

• Il s'agit d'un index sur le résultat d'une fonction :

  WHERE upper(a)='DUPOND'

• l'index classique ne fonctionne pas

  CREATE INDEX mon_idx ON ma_table ((upper(a))

• La fonction doit être IMMUTABLE

On trouve parfois « index couvrants » dans la littérature française.

CREATE INDEX idx1 on T1 (col1,col2)

• Répondent à la clause WHERE

• ET contiennent toutes les colonnes demandées par la requête :

  SELECT col1,col2` FROM t1 WHERE col1>12

• Pas de visite de la table (donc peu d'accès aléatoires, l'index étant à peu près trié physiquement)

• Un index utilise des opérateurs de comparaison :
• Il peut exister plusieurs façons de comparer deux données du même type
• Par exemple, pour les chaînes de caractères
  • Différentes collations

  • Tri sans collation (pour LIKE)

    CREATE INDEX idx1 ON ma_table (col_varchar varchar_pattern_ops)

• Permet :

  SELECT ... FROM ma_table WHERE col_varchar LIKE 'chaine%'

GIN : Generalized Inverted iNdex

• Index inversé généralisé
• Index inversé ?
  • Index associe une valeur à la liste de ses adresses
  • Utile pour tableaux, listes…
• Pour chaque entrée du tableau
  • Liste d'adresses (TID) où le trouver
  • Compressée à partir de 9.4 => alternative à bitmap

GiST : Generalized Search Tree

• Arbre de recherche généralisé
• Indexation non plus des valeurs mais de la véracité de prédicats
• Moins performants car moins sélectifs que btree
• Mais peuvent indexer à peu près n'importe quoi
• Multi-colonnes dans n'importe quel ordre
• Sur-ensemble de btree et rtree

• KNN = K-Nearest neighbours, K plus proches voisins

• Requêtes de types

  ORDER BY ma_colonne <-> une_référence LIMIT 10

• Très utile pour la recherche de mots ressemblants, géographique

BRIN : Block Range INdex (9.5+)

• Utile pour les tables très volumineuses
  • L'index produit est petit
• Performant lorsque les valeurs sont corrélées à leur emplacement physique
• Types qui peuvent être triés linéairement (pour obtenir min/max)

Index Hash : * Journalisés uniquement depuis la version 10 * donc facilement corrompus sur les versions antérieures * Moins performants que les btree * Ne gèrent que les égalités, pas « < » et « > » * Mais plus compacts * À ne pas utiliser

• pour l'identification des requêtes
• pour l'identification des prédicats et des requêtes liées
• pour la validation de l'index à créer

• PgBadger
• pg_stat_statements
• PoWA

• pg_qualstats
  • avec PoWa

• PoWA
• HypoPG

• Responsabilité de l'indexation
• Compréhension des mécanismes
• Différents types d'index, différentes stratégies
• Outillage

• SQL et PostgreSQL proposent de nombreuses possibilités avancées
  • normes SQL:99, 2003, 2008 et 2011
  • parfois, extensions propres à PostgreSQL

• LIMIT/OFFSET
• jointures LATERAL
• UPSERT : INSERT ou UPDATE
• Common Table Expressions
• Serializable Snapshot Isolation

• Aller au-delà de SQL:92
• Concevoir des requêtes simples pour résoudre des problèmes complexes

• Clause LIMIT
• ou syntaxe en norme SQL : FETCH FIRST xx ROWS
• Utilisation :
  • limite le nombre de lignes du résultat

• Clause OFFSET
  • à utiliser avec LIMIT
• Utilité :
  • pagination de résultat
  • sauter les n premières lignes avant d'afficher le résultat

• Sans offset :

SELECT *
  FROM employes
 LIMIT 2
 ORDER BY matricule;

 matricule |   nom    | service  | salaire
-----------+----------+----------+----------
 00000001  | Dupuis   |          | 10000.00
 00000004  | Fantasio | Courrier |  4500.00

• En sautant les deux premières lignes :

SELECT *
  FROM employes
 ORDER BY matricule
 LIMIT 2
 OFFSET 2;

 matricule |   nom    |   service   | salaire
-----------+----------+-------------+---------
 00000006  | Prunelle | Publication | 4000.00
 00000020  | Lagaffe  | Courrier    | 3000.00

• OFFSET peut être problématique :
  • beaucoup de données lues
  • temps de réponse dégradés
• Alternative possible
  • utilisation d'un index
  • couplé aux données composites
• Article sur le sujet

• Clause RETURNING
• Utilité :
  • récupérer les enregistrements modifiés
  • avec INSERT
  • avec UPDATE
  • avec DELETE

• INSERT ou UPDATE ?
  • INSERT ... ON CONFLICT DO { NOTHING | UPDATE }
  • À partir de la version 9.5
• Utilité :
  • mettre à jour en cas de conflit sur un INSERT
  • ne rien faire en cas de conflit sur un INSERT

• Insérer une ligne déjà existante provoque une erreur :

INSERT INTO employes (matricule, nom, service, salaire)
 VALUES ('00000001', 'Marsupilami', 'Direction', 50000.00);
ERROR:  duplicate key value violates unique constraint
        "employes_pkey"
DETAIL:  Key (matricule)=(00000001) already exists.

• la clause ON CONFLICT DO NOTHING évite d'insérer une ligne existante :

=# INSERT INTO employes (matricule, nom, service, salaire)
   VALUES ('00000001', 'Marsupilami', 'Direction', 50000.00)
   ON CONFLICT DO NOTHING;
INSERT 0 0

• Avec plusieurs lignes insérées :

INSERT INTO employes (matricule, nom, service, salaire)
VALUES ('00000002', 'Moizelle Jeanne', 'Publication', 3000.00),
       ('00000040', 'Lebrac', 'Publication', 3100.00)
ON CONFLICT (matricule)
   DO UPDATE SET salaire = employes.salaire,
                 nom = excluded.nom
RETURNING *;

 matricule |       nom       |   service   | salaire
-----------+-----------------+-------------+----------
 00000002  | Moizelle Jeanne | Publication |  3000.00
 00000040  | Lebrac          | Publication |  3000.00

• colonne(s) portant(s) une contrainte d'unicité
• pseudo-table excluded

INSERT ....
ON CONFLICT (<colonne clé>)
  DO UPDATE
        SET colonne_a_modifier = excluded.colonne,
            autre_colonne_a_modifier = excluded.autre_colonne,
            ...;

• Jointures LATERAL
  • SQL:99
  • PostgreSQL 9.3
  • équivalent d'une boucle foreach
• Utilisations
  • top-N à partir de plusieurs tables
  • jointure avec une fonction retournant un ensemble

• Jointure LATERAL
  • équivalent de foreach
• Utilité :
  • Top-N à partir de plusieurs tables
  • exemple : afficher les 5 derniers messages des 5 derniers sujets actifs d'un forum

• Utilisation avec une fonction retournant un ensemble
  • clause LATERAL optionnelle
• Utilité :
  • extraire les données d'un tableau ou d'une structure JSON sous la forme tabulaire
  • utiliser une fonction métier qui retourne un ensemble X selon un ensemble Y fourni

• Common Table Expressions
  • clauses WITH et WITH RECURSIVE
• Utilité :
  • factoriser des sous-requêtes

• Utilité
  • factoriser des sous-requêtes
  • améliorer la lisibilité d'une requête

• Attention, une CTE est une barrière d'optimisation
  • pas de transformations
  • pas de propagation des prédicats

• CTE avec des requêtes en modification
  • avec INSERT/UPDATE/DELETE
  • et éventuellement RETURNING
  • obligatoirement exécuté sur PostgreSQL
• Exemple d'utilisation :
  • archiver des données
  • partitionner les données d'une table
  • débugger une requête complexe

• SQL permet d'exprimer des récursions
  • WITH RECURSIVE
• Utilité :
  • récupérer une arborescence de menu hiérarchique
  • parcourir des graphes (réseaux sociaux, etc.)

• 1ère étape : initialisation de la récursion

• récursion : la requête s'appelle elle-même

• Problèmes pouvant se poser :
  • UPDATE perdu
  • lecture non répétable
• Plusieurs solutions possibles
  • versionnement des lignes
  • SELECT FOR UPDATE
  • SERIALIZABLE

• SELECT FOR UPDATE
• Utilité :
  • "réserver" des lignes en vue de leur mise à jour
  • éviter les problèmes de concurrence d'accès

• SELECT FOR UPDATE SKIP LOCKED
  • PostgreSQL 9.5
• Utilité :
  • implémente des files d'attentes parallélisables

SSI : Serializable Snapshot Isolation (9.1+)

• Chaque transaction est seule sur la base
• Si on ne peut maintenir l'illusion
  • Une des transactions en cours est annulée
• Sans blocage
• On doit être capable de rejouer la transaction
• Toutes les transactions impliquées doivent être serializable
• default_transaction_isolation=serializable dans la configuration

• SQL est un langage très riche
• Connaître les nouveautés des versions de la norme depuis 20 ans permet de
  • gagner énormément de temps de développemment
  • mais aussi de performance

• Analyser des données est facile avec PostgreSQL
  • opérations d'agrégation disponibles
  • fonctions OLAP avancées

• agrégation de données
• clause FILTER
• fonctions window
• GROUPING SETS, ROLLUP, CUBE
• WITHIN GROUPS

• Écrire des requêtes encore plus complexes
• Analyser les données en amont
  • pour ne récupérer que le résultat

• SQL dispose de fonctions de calcul d'agrégats
• Utilité :
  • calcul de sommes, moyennes, valeur minimale et maximale
  • nombreuses fonctions statistiques disponibles

• agrégat + GROUP BY
• Utilité
  • effectue des calculs sur des regroupements : moyenne, somme, comptage, etc.
  • regroupement selon un critère défini par la clause GROUP BY
  • exemple : calcul du salaire moyen de chaque service

• Extension propriétaire de PostgreSQL
  • ORDER BY dans la fonction d'agrégat
• Utilité :
  • ordonner les données agrégées
  • surtout utile avec array_agg, string_agg et xmlagg

• Clause FILTER
• Utilité :
  • filtrer les données sur les agrégats
  • évite les expressions CASE complexes
• SQL:2003
• Intégré dans la version 9.4

• La syntaxe suivante était utilisée :

SELECT count(*) AS compte_pays,
       count(CASE WHEN r.nom_region='Europe' THEN 1
                  ELSE 0
              END) AS compte_pays_europeens
  FROM pays p
  JOIN regions r
    ON (p.region_id = r.region_id);

• La même requête écrite avec la clause FILTER :

SELECT count(*) AS compte_pays,
       count(*) FILTER (WHERE r.nom_region='Europe')
                AS compte_pays_europeens
  FROM pays p
  JOIN regions r
    ON (p.region_id = r.region_id);

• Fonctions window
  • travaille sur des ensembles de données regroupés et triés indépendamment de la requête principale
• Utilisation :
  • utiliser plusieurs critères d'agrégation dans la même requête
  • utiliser des fonctions de classement
  • faire référence à d'autres lignes de l'ensemble de données

• Regroupement
  • clause OVER (PARTITION BY ...)
• Utilité :
  • plusieurs critères de regroupement différents
  • avec des fonctions de calcul d'agrégats

• Tri
  • OVER (ORDER BY …)
• Utilité :
  • numéroter les lignes : row_number()
  • classer des résultats : rank(), dense_rank()
  • faire appel à d'autres ligne du résultat : lead(), lag()

• Pour numéroter des lignes :

SELECT row_number() OVER (ORDER BY matricule),
       matricule, nom
  FROM employes;

 row_number | matricule |   nom
------------+-----------+----------
          1 | 00000001  | Dupuis
          2 | 00000004  | Fantasio
          3 | 00000006  | Prunelle
          4 | 00000020  | Lagaffe
          5 | 00000040  | Lebrac
(5 lignes)

• Calcul d'une somme glissante :

SELECT matricule, salaire,
       SUM(salaire) OVER (ORDER BY matricule)
  FROM employes;

 matricule | salaire  |   sum
-----------+----------+----------
 00000001  | 10000.00 | 10000.00
 00000004  |  4500.00 | 14500.00
 00000006  |  4000.00 | 18500.00
 00000020  |  3000.00 | 21500.00
 00000040  |  3000.00 | 24500.00

• On peut combiner les deux
  • OVER (PARTITION BY .. ORDER BY ..)
• Utilité :
  • travailler sur des jeux de données ordonnés et isolés les uns des autres

• PostgreSQL dispose d'un certain nombre de fonctions analytiques
• Utilité :
  • faire référence à d'autres lignes du même ensemble
  • évite les auto-jointures complexes et lentes

• lead(colonne, n)
  • retourne la valeur d'une colonne, n lignes après la ligne courante
• lag(colonne, n)
  • retourne la valeur d'une colonne, n lignes avant la ligne courante

• first_value(colonne)
  • retourne la dernière valeur pour la colonne
• last_value(colonne)
  • retourne la dernière valeur pour la colonne
• nth_value(colonne, n)
  • retourne la n-ème valeur (en comptant à partir de 1) pour la colonne

• Pour factoriser la définition d'une fenêtre :

SELECT matricule, nom, salaire, service,
       rank() OVER w,
       dense_rank() OVER w
  FROM employes
 WINDOW w AS (ORDER BY salaire);

• La fenêtre de travail par défaut est :

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

• Deux modes possibles :
  • RANGE
  • ROWS
• Nécessite une clause ORDER BY

• Indique un intervalle à bornes flou
• Borne de départ :
  • UNBOUNDED PRECEDING: depuis le début de la partition
  • CURRENT ROW : depuis la ligne courante
• Borne de fin :
  • UNBOUNDED FOLLOWING : jusqu'à la fin de la partition

  • CURRENT ROW : jusqu'à la ligne courante

    OVER (PARTITION BY ...
      ORDER BY ...
      RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

• Indique un intervalle borné par un nombre de ligne défini avant et après la ligne courante
• Borne de départ :
  • xxx PRECEDING : depuis les xxx valeurs devant la ligne courante
  • CURRENT ROW : depuis la ligne courante
• Borne de fin :
  • xxx FOLLOWING : depuis les xxx valeurs derrière la ligne courante

  • CURRENT ROW : jusqu'à la ligne courante

    OVER (PARTITION BY ...
      ORDER BY ...
      ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING

• WITHIN GROUP
  • PostgreSQL 9.4
• Utilité :
  • calcul de médianes, centiles

• GROUPING SETS/ROLLUP/CUBE
• Extension de GROUP BY
• PostgreSQL 9.5
• Utilité :
  • présente le résultat de plusieurs agrégations différentes
  • réaliser plusieurs agrégations différentes dans la même requête

• On peut se passer de la clause GROUPING SETS
  • mais la requête sera plus lente

SELECT piece,NULL as region,sum(quantite)
  FROM stock
  GROUP BY piece
UNION ALL
SELECT NULL, region,sum(quantite)
  FROM STOCK
  GROUP BY region;

• ROLLUP
• PostgreSQL 9.5
• Utilité :
  • calcul de totaux dans la même requête

SELECT piece,region,sum(quantite)
FROM stock GROUP BY ROLLUP (piece,region);

Cette requête est équivalente à la requête suivante utilisant GROUPING SETS :

SELECT piece,region,sum(quantite)
FROM stock 
GROUP BY GROUPING SETS ((),(piece),(piece,region));

• CUBE
  • PostgreSQL 9.5
• Utilité :
  • calcul de totaux dans la même requête
  • sur toutes les clauses de regroupement

SELECT piece,region,sum(quantite)
FROM stock GROUP BY CUBE (piece,region);

Cette requête est équivalente à la requête suivante utilisant GROUPING SETS :

SELECT piece,region,sum(quantite)
FROM stock                              
GROUP BY GROUPING SETS (
  (),
  (piece),
  (region),
  (piece,region) 
  );