• L’optimisation doit porter sur les différents composants
  • le serveur qui héberge le SGBDR : le matériel, la distribution, le noyau, les systèmes de fichiers
  • 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 et les requêtes

• Quelques considérations générales sur l’optimisation
• Choix et configuration du matériel
• Choix et configuration du système d’exploitation
• Configuration du serveur de bases de données
• Outils

• Deux points déterminants :
  • vision globale du système d’information
  • compréhension de l’utilisation de la base

• L’optimisation n’est pas un processus unique
  • il s’agit au contraire d’un processus itératif
• La base doit être surveillée régulièrement !
  • nécessité d’installer des outils de supervision

• Performances très liées aux possibilités du matériel et de l’OS
• 4 composants essentiels
  • les processeurs
  • la mémoire
  • les disques
  • le système disque (RAID, SAN)

• Trois critères importants
  • nombre de cœurs
  • fréquence
  • cache
• Privilégier
  • le nombre de cœurs si le nombre de sessions parallèles est important
  • ou la fréquence si les requêtes sont complexes
• 64 bits

• Essentielle pour un serveur de bases de données
• Plus il y en a, mieux c’est
  • moins d’accès disque
• Pour le système comme pour PostgreSQL

• Pour un SGBD :
  • RAID 1 : système, journaux de transactions
  • RAID 10 : fichiers de données
• RAID 5 déconseillé pour les bases de données (écritures)
• RAID soft déconseillé !
• Qualité des cartes !
• Cache :
  • en lecture : toujours
  • en écriture : si batterie présente & supervisée

• Pouvoir sélectionner les disques dans un groupe RAID
• Attention au cache
  • toujours activer le cache en lecture
  • activer le cache en écriture que si batterie présente
• Attention à la latence réseau !
• Attention au système de fichiers
  • NFS : risques de corruptions et problèmes de performance

• Masque les ressources physiques au système
  • plus difficile d’optimiser les performances
• Propose généralement des fonctionnalités d’overcommit
  • grandes difficultés à trouver la cause du problème du point de vue de la VM
  • dédier les ressources aux VM PostgreSQL
• En pause tant que l’hyperviseur ne dispose pas de l’ensemble des vCPU alloués à la machine virtuelle (steal time)
• Mutualise les disques = problèmes de performances
  • préférer attachement direct au SAN
  • disques de PostgreSQL en Thick Provisionning
  • éviter les snapshots de VM (selon la technologie)

• Éviter le time drift : même source NTP sur les VM et l’ESXi
• Utiliser les adaptateurs réseau paravirtualisés de type VMXNET3
• Utiliser l’adaptateur paravirtualisé PVSCSI pour les disques dédiés aux partitions PostgreSQL
• Si architecture matérielle NUMA :
  • dimensionner la mémoire de chaque VM pour qu’elle ne dépasse pas le volume de mémoire physique au sein d’un groupe NUMA

• Quel système choisir ?
• Quelle configuration réaliser ?

• PostgreSQL fonctionne sur différents systèmes
  • principalement développé et testé sous Linux
  • *BSD, macOS, Windows, Solaris, etc.
• Windows intéressant pour les postes des développeurs
  • mais moins performant que Linux
  • moins d’outillage

• Choisir la version la plus récente du noyau car
  • plus stable
  • plus compatible avec le matériel
  • plus de fonctionnalités
  • plus de performances
• Utiliser la version de la distribution Linux
  • ne pas le compiler soi-même

• À configurer :
  • cache disque système
  • swap
  • overcommit
  • huge pages
  • affinité entre cœurs et mémoire
  • scheduler

• Lissage de l’écriture des dirty pages
• Encore utile malgré les paramètres PostgreSQL *_flush_after
• Paramètres
  • vm.dirty_bytes = 1 Go
  • vm.dirty_background_bytes = 512 Mo
  • ou vm.dirty_ratio / vm.dirty_background_ratio

La mémoire sert de cache au disque, pas l’inverse !

• Swap : 2-4 Go
  • pour les processus d’arrière-plan
• et à décourager, sans l’empêcher

vm.swappiness = 10

Le noyau peut autoriser trop de réservation mémoire.

• Si saturation :
  • kill -9 du processus par l’OOM killer
  • et donc redémarrage
  • purge du cache
• Désactiver sur serveur dédié :

vm.overcommit_memory = 2
vm.overcommit_ratio  = ?  # à calculer, souvent 70-80

Pages mémoire de 2 Mo au lieu de 4 ko :

• Les processus consomment moins de mémoire
• shared buffers non swappés
• PostgreSQL :

huge_pages = try  # ou: on / off

• Noyau :

vm.nr_overcommit_hugepages = ?  # selon shared_buffers +10%
vm.overcommit_ratio        = ?  # à baisser

• Voir /proc/meminfo

• « Huge Pages » dynamiques
• À désactiver :

echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

# grub
transparent_hugepage=never

• /proc/meminfo/AnonHugePages

• Pour architecture NUMA (multisocket)
• Chaque socket travaille plus efficacement avec une zone mémoire allouée
• Peut pénaliser le cache disque système
  • vm.zone_reclaim_mode : passer à 0

• Réduire la propension du kernel à migrer les processus
  • kernel.sched_migration_cost_ns = 5000000 (×10) (si kernel < 5.13)
  • echo 5000000 > /sys/kernel/debug/sched/migration_cost_ns (kernel > 5.13)
• Désactiver le regroupement par session TTY
  • kernel.sched_autogroup_enabled = 0

• Outil
  • sysctl
• Fichier de configuration
  • /etc/sysctl.conf
  • /etc/sysctl.d/*conf

• Windows :
  • NTFS
• Linux :
  • ext4, XFS
  • LVM pour la flexibilité
• Solaris :
  • ZFS
• Utiliser celui préconisé par votre système d’exploitation/distribution

• Quelques options à connaître :
  • noatime, nodiratime
  • dir_index
  • data=writeback
  • nobarrier
• Permet de gagner un peu en performance

Ni antivirus, ni IDS

• Version
• Configuration
• Emplacement des fichiers

• Chaque nouvelle version majeure a des améliorations de performance
  • mettre à jour est un bon moyen pour gagner en performances
• Ne pas compiler

• shared_buffers = ...
  • 25 % de la RAM en première intention
    • généralement acceptable
  • max 40 %
  • complémentaire du cache OS
• wal_buffers

• work_mem

  • par processus, voire nœud
  • valeur très dépendante de la charge et des requêtes
  • fichiers temporaires vs saturation RAM

• × hash_mem_multiplier

• maintenance_work_mem

• Des paramètres sont liés aux tablespaces
• io_combine_limit (v17)
  • 128 ko

• effective_cache_size
• random_page_cost

• Par défaut : 1 requête = 1 processus
• Grosses tables : parallel workers en complément
• Nombreux nœuds parallélisables

• Nombre de parallel workers :
  • max_parallel_workers_per_gather (défaut : 2)
  • max_parallel_workers (8)
  • max_worker_processes (8)
• Taille minimale des tables/index :
  • min_parallel_table_scan_size (8 Mo)
  • min_parallel_index_scan_size (512 ko)
• Indexation et VACUUM :
  • max_parallel_maintenance_workers (2)

• Write Ahead Logs (WAL)
• Chaque donnée est écrite 2 fois sur le disque !
• Avantages :
  • sécurité infaillible (après COMMIT), intégrité, durabilité
  • écriture séquentielle rapide, et un seul sync sur le WAL
  • fichiers de données écrits en asynchrone
  • sauvegarde PITR et réplication fiables

• fsync (on !)
• min_wal_size (80 Mo) / max_wal_size (1 Go)
• checkpoint_timeout (5 min, ou plus)
• checkpoint_completion_target (passer à 0.9)

• Traces à rendre plus verbeuses (log_*)
• Statistiques d’activité (track_*)
• https://dali.bo/h1_slides
• Pour analyse par :
  • pgBadger
  • pg_stat_statements…

• Ne jamais le désactiver
• À rendre plus agressif sur les grosses tables
  • autovacuum_vacuum/analyze_scale_factor (10 / 20 %), etc.
• Ne pas hésiter à le planifier chaque nuit
• Voir https://dali.bo/m5_slides

• Espace de stockage physique d’objets
  • et non logique !
• Simple répertoire (hors de PGDATA)
  • lien symbolique depuis pg_tblspc
• Pour :
  • répartir I/O et volumétrie
  • données froides/chaudes
  • tri sur disque séparé

CREATE TABLESPACE  ssd      LOCATION '/mnt/data_ssd/' ;
CREATE TABLESPACE  ssd_tmp1 LOCATION '/mnt/temp1' ;
CREATE TABLESPACE  ssd_tmp2 LOCATION '/mnt/temp2' ;
GRANT CREATE ON TABLESPACE  ssd  TO dupont ;
GRANT CREATE ON TABLESPACE  ssd_tmp1,ssd_tmp2  TO dupont ;

• default_tablespace

default_tablespace = ssd   # postgresql.conf

ALTER DATABASE/ROLE nomdb SET default_tablespace = ssd ;

• temp_tablespaces :
  • tri & tables temporaires, en alternance
  • protéger le PGDATA

  ALTER ROLE etl SET temp_tablespaces = ssd_tmp1,ssd_tmp2;

• Temps d’accès
  • seq_page_cost (1)
  • random_page_cost (4)
• Opérations simultanées sur le disque
  • effective_io_concurrency (1)
  • maintenance_io_concurrency (10)

ALTER TABLESPACE ssd SET ( random_page_cost = 1 );
ALTER TABLESPACE ssd SET ( effective_io_concurrency   = 500,
                           maintenance_io_concurrency = 500 ) ;

• Placer les journaux sur un autre disque
• Option --wal-dir de l’outil initdb
• Lien symbolique

• Avant la v15
  • placer les fichiers statistiques sur un autre disque
  • option stats_temp_directory
• À partir de la v15
  • cela peut être intéressant pour pg_stat_statements

• pgtune
• pgbench

• Outil écrit en Python, par Greg Smith
  • repris en Ruby par Alexey Vasiliev
• Propose quelques meilleures valeurs pour certains paramètres
• Quelques options pour indiquer des informations système
• Version web : https://pgtune.leopard.in.ua/
• Il existe également pgconfig : https://www.pgconfig.org/

• Outil pour réaliser rapidement des tests de performance
• Fourni dans les modules de contrib de PostgreSQL
• Travaille sur une base de test créée par l’outil…
  • … ou sur une vraie base de données

• On peut faire varier différents paramètres, tel que :
  • le nombre de clients
  • le nombre de transactions par client
  • faire un test de performance en SELECT only, UPDATE only ou TPC-B
  • faire un test de performance dans son contexte applicatif
  • exécuter le plus de requêtes possible sur une période de temps donné
  • etc.

• pgbench est capable de créer son propre environnement de test
• Environnement adapté pour des tests de type TPC-B
• Permet de rapidement tester une configuration PostgreSQL
  • en termes de performance
  • en termes de charge
• Ou pour expérimenter/tester

• pgbench est capable de travailler avec une base existante
• Lecture des requêtes depuis un ou plusieurs fichiers
• Utilisation possible de variables et commandes

• PostgreSQL propose de nombreuses voies d’optimisation.

• Cela passe en priorité par un bon choix des composants matériels et par une configuration pointilleuse.

• Mais ceci ne peut se faire qu’en connaissance de l’ensemble du système, et notamment des applications utilisant les bases de l’instance.

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

https://dali.bo/j1_quiz

• Qu’est-ce qu’un plan d’exécution ?
• Quels outils peuvent aider

• Exécution globale d’une requête
• Optimiseur
• EXPLAIN
• Nœuds d’un plan
• Outils

• SQL est un langage déclaratif
• Une requête décrit le résultat à obtenir
  • mais pas la façon pour l’obtenir
• C’est à l’optimiseur de déduire le moyen de parvenir au résultat demandé : comment ?

Le modèle vise à minimiser un coût :

• Énumérer tous les plans d’exécution
  • ou presque tous…
• Statistiques + configuration + règles → coût calculé
• Coût le plus bas = meilleur plan

• Comment accéder aux lignes ?
  • parcours de table, d’index, de fonction, etc.
• Comment joindre les tables ?
  • ordre
  • type
• Comment agréger ?
  • brut, tri, hachage…

• Chaque opération a un coût :
  • lire un bloc selon sa position sur le disque
  • manipuler une ligne
  • appliquer un opérateur
  • …
• et généralement un paramètre associé

• Connaître le coût de traitement d’une ligne est bien
  • mais combien de lignes à traiter ?
• Statistiques sur les données
  • mises à jour : ANALYZE
• Sans bonnes statistiques, pas de bons plans !

• 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
  • le nœud final retourne les données à l’utilisateur

• Commande EXPLAIN
  • suivi de la requête complète
• Uniquement le plan finalement retenu

->  Seq Scan on t1  (cost=0.00..21.50 rows=9 width=8)
      Filter: (c2 < 10)

• cost : coûts de récupération
  • de la première ligne
  • de toutes les lignes
• rows
  • nombre de lignes en sortie du nœud
• width
  • largeur moyenne d’un enregistrement (octets)

Sort  (cost=21.64..21.67 rows=9 width=8)
  Sort Key: c1
Seq Scan on t1  (cost=0.00..21.50 rows=9 width=8)
  Filter: (c2 < 10)

• Sort
  • Sort Key : clé de tri
• Seq Scan
  • Filter : filtre (si besoin)
• Dépend
  • du type de nœud
  • des options de EXPLAIN
  • des paramètres de configuration
  • de la version de PostgreSQL

EXPLAIN (ANALYZE,VERBOSE,BUFFERS,SERIALIZE)
SELECT * FROM demotoast ;

                            QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on public.demotoast  (cost=0.00..177.00 rows=1000 width=1196) (actual time=0.012..0.182 rows=1000 loops=1)
   Output: i, t, png, j
   Buffers: shared hit=167
 Query Identifier: 698565989149231362
 Planning Time: 0.055 ms
 Serialization: time=397.480 ms  output=307084kB  format=text
   Buffers: shared hit=14500
 Execution Time: 397.776 ms

• PostgreSQL 17

Quel plan générique pour les requêtes préparées ?

EXPLAIN (GENERIC_PLAN)
SELECT * FROM t1 WHERE c1 < $1 ;

• PostgreSQL 16

• VERBOSE
  • affichage verbeux : schémas, colonnes, workers : conseillé
• MEMORY
  • mémoire utilisée pour le plan (v17)
• COSTS OFF
  • masquer les coûts
• TIMING OFF
  • désactiver le chronométrage & des informations vues/calculées par l’optimiseur
• SUMMARY
  • affichage du temps de planification et exécution (si applicable)
• FORMAT
  • sortie en texte, JSON, XML, YAML

SET track_io_timing TO on;
EXPLAIN (ANALYZE, BUFFERS)
SELECT   *   FROM t1   WHERE c2<10   ORDER BY c1 ;

                            QUERY PLAN
---------------------------------------------------------
 Sort  (cost=52.14..52.21 rows=27 width=8) (actual time=1.359..1.366 rows=27 loops=1)
   …
   Buffers: shared hit=3 read=14
   I/O Timings: read=0.388
   ->  Seq Scan on t1  (cost=0.00..51.50 rows=27 width=8) (actual time=0.086..1.233 rows=27 loops=1)
         Filter: (c2 < 10)
         Rows Removed by Filter: 2973
         Buffers: shared read=14
         I/O Timings: read=0.388
 Planning:
   Buffers: shared hit=43 read=14
   I/O Timings: read=0.469
 Planning Time: 1.387 ms
 Execution Time: 1.470 ms

• Tester la machine avec pg_test_timing

• Temps d’exécution de chaque opération
• Différence entre l’estimation du nombre de lignes et la réalité
• Boucles
  • appels, même rapides, nombreux
• Opérations utilisant beaucoup de blocs (BUFFERS)
• Opérations lentes de lecture/écriture (track_io_timing)

• Parcours
• Jointures
• Agrégats
• Tri

• Table
  • Seq Scan, Parallel Seq Scan
• Index
  • Index Scan, Bitmap Scan, Index Only Scan
  • et les variantes parallélisées
• Autres
  • Function Scan, Values Scan

• Algorithmes
  • Nested Loop
  • Hash Join
  • Merge Join
• Parallélisation possible
• Pour EXISTS, IN et certaines jointures externes
  • Semi Join
  • Anti Join

• Un résultat au total
  • Aggregate
• Un résultat par regroupement
  • Hash Aggregate
  • Group Aggregate
  • Mixed Aggregate
• Parallélisation
  • Partial Aggregate
  • Finalize Aggregate

• Sort
• Incremental Sort
• Limit
• Unique (DISTINCT)
• Append (UNION ALL), Except, Intersect
• Gather (parallélisme)
• Memoize (14+)

• pgAdmin
• explain.depesz.com
• explain.dalibo.com

• 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 avec affichage amélioré du EXPLAIN ANALYZE
• Lignes colorées pour indiquer les problèmes
• Installable en local

• Reprise de pev d’Alex Tatiyants, par Pierre Giraud (Dalibo)
• Page web avec affichage graphique d’un EXPLAIN [ANALYZE]
• Repérage des nœuds longs, lourds…
• Affichage flexible
• explain.dalibo.com
• Installable en local

• Un optimiseur très avancé
• Ne vous croyez pas plus malin que lui
• Mais il est important de savoir comment il fonctionne

https://dali.bo/j0_quiz

• Qu’est-ce qu’un index ?
• Comment indexer une base ?
• Les index B-tree dans PostgreSQL

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

• Uniquement destinés à l’optimisation
• À gérer d’abord par le développeur
  • Markus Winand : SQL Performance Explained

• Un index permet de :
  • trouver un enregistrement dans une table directement
  • récupérer une série d’enregistrements dans une table
  • voire tout récupérer dans l’index (Index Only Scan)
• Un index facilite :
  • certains tris
  • certains agrégats
• Obligatoires et automatique pour clés primaires & unicité
  • conseillé pour clés étrangères (FK)

Un index améliore les SELECT

• Sans index :

=# SELECT * FROM test WHERE id = 10000;
Temps : 1760,017 ms

• Avec index :

=# CREATE INDEX idx_test_id ON test (id);

=# SELECT * FROM test WHERE id = 10000;
Temps : 27,711 ms

• L’index n’est pas gratuit !
• Ralentit les écritures
  • maintenance
• Place disque
• Compromis à trouver

• Lourd…

-- bloque les écritures !
CREATE INDEX ON matable ( macolonne ) ;
-- ne bloque pas, peut échouer
CREATE INDEX CONCURRENTLY ON matable ( macolonne ) ;

• Si fragmentation :

REINDEX INDEX nomindex ;
REINDEX TABLE CONCURRENTLY nomtable ;

• Paramètres :
  • maintenance_work_mem (sinon : fichier temporaire !)
  • max_parallel_maintenance_workers

• Défaut : B-tree classique (équilibré)
• UNIQUE (préférer la contrainte)
• Mais aussi multicolonne, fonctionnel, partiel, couvrant
• Index spécialisés : hash, GiST, GIN, BRIN, HNSW….

• Structure associant des clés (termes) à des localisations (pages)
• Structure de données spécialisée, plusieurs types
• Séparée de la table
• Analogies :
  • fiches en carton des bibliothèques avant l’informatique (B-tree)
  • index d’un livre technique (GIN)

• 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
  • et le plus simple
• Utilisable pour les contraintes d’unicité
• Supporte les opérateurs : <, <=, =, >=, >
• Supporte le tri
• Ne peut pas indexer des colonnes de plus de 2,6 ko

SELECT name FROM ma_table WHERE id = 22

• Possibilité d’indexer plusieurs colonnes :

    CREATE INDEX ON ma_table (id, name) ;

• Ordre des colonnes primordial
  • accès direct aux premières colonnes de l’index
  • pour les autres, PostgreSQL lira tout l’index ou ignorera l’index

• Index Scan
• Bitmap Scan
• Index Only Scan
  • idéal pour les performances
• et les variantes parallélisées

• On indexe pour une requête
  • ou idéalement une collection de requêtes
• Et 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

C’est souvent tout à fait normal

• Utiliser l’index est-il rentable ?
• La requête est-elle compatible ?
• Bug de l’optimiseur : rare

• L’optimiseur pense qu’il n’est pas rentable
  • sélectivité trop faible
  • meilleur chemin pour remplir d’autres critères
  • index redondant
  • Index Only Scan nécessite un VACUUM fréquent
• Les estimations de volumétries doivent être assez bonnes !
  • statistiques récentes, précises

• Pas le bon type (CAST plus ou moins explicite)

EXPLAIN SELECT * FROM clients WHERE client_id = 3::numeric;

• Utilisation de fonctions, comme :

SELECT * FROM ma_table WHERE to_char(ma_date, 'YYYY')='2014' ;

SELECT * FROM fournisseurs WHERE commentaire LIKE 'ipsum%';

• Solution :

CREATE INDEX idx1 ON ma_table (col_varchar varchar_pattern_ops) ;

• CREATE INDEX … CONCURRENTLY peut échouer

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

• Index partiels
• Index fonctionnels
• Index couvrants
• Classes d’opérateur

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

CREATE INDEX on evenements (type) WHERE  traite IS FALSE ;

• Ne sert que si la clause est logiquement équivalente !
  • ou partie de la clause (inégalités, IN)
• Intérêt : index beaucoup plus petit

• Données chaudes et froides
• Index dédié à une requête avec une condition fixe

• Éviter les index de type :

CREATE INDEX ON matable ( champ_filtre ) WHERE champ_filtre = …

• Préférer :

CREATE INDEX ON matable ( champ_resultat ) WHERE champ_filtre = …

• Un index sur a est inutilisable pour :

  SELECT … WHERE upper(a)='DUPOND'

• Indexer le résultat de la fonction :

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

• Critère identique à la fonction dans l’index
• Fonction impérativement IMMUTABLE !
  • délicat avec les conversions de dates/heures
• Ne pas espérer d’Index Only Scan

• Ne pas oublier ANALYZE après création d’un index fonctionnel
  • les statistiques peuvent même être l’intérêt majeur (<v14)
• La fonction ne doit jamais tomber en erreur
• Si modification de la fonction
  • réindexation

Les index couvrants :

• répondent à la clause WHERE
• puis ajoutent toutes les colonnes pour la requête :

SELECT client_id FROM clients WHERE type_client = 'A' ;

CREATE INDEX ON clients ( type_client, client_id ) ;

• But : obtenir un Index Only Scan

• D’abord les critères, puis les champs
• L’index devient souvent trop gros
• VACUUM fréquent nécessaire !
• Pas tous les types d’index

• Un index utilise des opérateurs de comparaison
• Texte : différentes collations = différents tris… complexes
  • Index inutilisable sur :

  WHERE col_varchar LIKE 'chaine%'

• Solution : opérateur varchar_pattern_ops :
  • force le tri caractère par caractère, sans la collation

  CREATE INDEX idx1
  ON ma_table (col_varchar varchar_pattern_ops)

• Plus généralement :
  • nombreux autres opérateurs pour d’autres types d’index

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

https://dali.bo/j4_quiz

• Indispensables !
• Statistiques sur les données pour le planificateur
  • pour le choix du parcours
  • comme le choix des jointures
• Les statistiques indiquent :
  • la cardinalité d’un filtre → stratégie d’accès
  • la cardinalité d’une jointure → algorithme de jointure
  • la cardinalité d’un regroupement → algorithme de regroupement
• Sans bonnes statistiques, pas de bons plans !

• Statistiques de volumétrie
• Statistiques sur les données
  • principe
  • statistiques étendues
• ANALYZE et autovacuum

SELECT relpages, reltuples FROM pg_class WHERE relname = 'employes';

• pour tables et index
• relpages : taille en blocs
  • dont relallvisible
• reltuples : lignes
• Mise à jour : VACUUM/ANALYZE

• Échantillonner pour mieux planifier
• Estimer la sélectivité d’une clause WHERE ou JOIN
• Tables pg_statistic (par colonne), pg_statistic_ext…
• Vues pg_stats (par colonne), pg_stats_ext et pg_stats_ext_exprs

• Pour chaque colonne de chaque table
• Adaptées à la majorité des cas
  • forte ou faible contrainte de type
  • forte ou faible variabilité des données
• Calculées automatiquement par ANALYZE

-[ RECORD 1 ]----------+-----------------------------------------------
schemaname             | public
tablename              | employes
attname                | date_embauche
n_distinct             | -0.5

• Estimation basée sur l’échantillon
• Si > 0 : nombre de valeurs distinctes dans la colonne
  • valeur constante indépendante de la volumétrie
• Si < 0 : proportion de ligne distincte
  • valeur proportionnelle à la volumétrie
• -1 : toutes les valeurs diffèrent (ex : PK)
• La valeur peut être forcée

-[ RECORD 1 ]-----+----------------------------------------------------
schemaname        | public
tablename         | employes
attname           | date_embauche
most_common_vals  | {2006-03-01,2006-09-01,2000-06-01,2005-03-06,2006-01-01}
most_common_freqs | {0.214286,0.214286,0.142857,0.142857,0.142857}

• Liste des valeurs les plus fréquentes
• Liste des fréquences de ces valeurs
• Variable en fonction de l’échantillonnage

-[ RECORD 1 ]-------+------------------------------------------------------
schemaname          | public
tablename           | produits
attname             | prix
histogram_bounds    | {4.00,6.12,9.14,23.55,99.57}

• Décrit la répartition des données (hors MCV et NULL)
• Nombre de lignes sensiblement égal entre chaque borne de l’histogramme
• N’est pas calculé par ANALYZE si toutes les valeurs sont dans les MCV

-[ RECORD 1 ]----------+-----------------------------------------------
schemaname             | public
tablename              | clients
attname                | telephone_fixe
null_frac              | 0.98

• Estimation du nombre de valeur nulles dans la colonne

-[ RECORD 1 ]----------+-----------------------------------------------
schemaname             | public
tablename              | clients
attname                | date_inscription
correlation            | 1

• Varie de -1 à 1
• Indique si les données sont ordonnées dans les fichiers
• Arbitre la pertinence de l’usage d’un index et son type de parcours en estimant le nombre de bloc de la table à lire :
  • forte corrélation = données regroupées > index scan efficace
  • faible corrélation = données dispersées

-[ RECORD 1 ]----------+-----------------------------------------------
schemaname             | public
tablename              | employes
attname                | date_embauche
avg_width              | 4

• Calculé pour les champs non NULL de taille variable (text, json, jsonb, binaires, etc.)
• Constant pour les autres types (integer, boolean, char, etc.)

• Échantillon de 30 000 lignes par défaut
  • default_statistics_target = 100
  • × 300 lignes

-- Configurable pour chaque colonne
ALTER TABLE t ALTER COLUMN c SET STATISTICS 300 ;
-- Configurable pour chaque statistique étendue 
ALTER STATISTICS nom SET STATISTICS valeur ;

• Échantillon plus important
  • plus précis dans certains cas
  • temps de planification plus long
• ANALYZE pour rafraîchir les statistiques

• Pas par défaut
• CREATE STATISTICS
• Trois types de statistiques
  • nombre de valeurs distinctes
  • dépendances fonctionnelles
  • liste MCV
• ANALYZE après la création
• vue pg_stats_ext

CREATE STATISTICS stat_services (dependencies)
  ON code_postal, ville, departement
  FROM services;

• Indique une dépendance fonctionnelle entre deux colonnes
• La valeur d’une colonne varie en fonction d’une autre

CREATE STATISTICS stat_services (ndistinct)
  ON couleur, cépage
  FROM vins;

• Indique que plusieurs colonnes sont liées
• Utile pour les clauses GROUP BY

CREATE STATISTICS stat_services (mcv)
  ON couleur, cépage
  FROM vins;

• Même principe que pour une seule colonne
• Supporte les opérateurs d’inégalité >,>,<= et >=

CREATE STATISTICS employe_big_extract
ON extract('year' FROM date_embauche) FROM employes_big;

• Pas créées par défaut
• Résout le problème des statistiques difficiles à estimer
• À partir de la v14 (index fonctionnel nécessaire avant)
• ANALYZE après la création
• Vue pg_stat_ext_exprs

Vues disponibles :

• pg_stats_ext
• pg_stats_ext_exprs (pour les expressions, v14)

• À la demande
  • ANALYZE
  • VACUUM ANALYZE
  • vacuumdb --analyze / --analyze-only
• Automatiquement
  • processus autovacuum
  • paramètres autovacuum_analyze_*
• Suivi :
  • pg_stat_user_tables

ANALYZE; -- base entière
ANALYZE pgbench_tellers; -- une table
ANALYZE pgbench_accounts (abalance) ; -- une colonne

• Un échantillon de table → statistiques
• Table vide : conserve les anciennes statistiques
• Nouvelle table : valeur par défaut

• Dépend principalement de la fréquence des requêtes DML
• Le processus autovacuum fait l’ANALYZE mais…
  • pas sur les tables temporaires
  • pas assez rapidement parfois
• Planification au niveau système avec cron
  • psql -c "ANALYZE"
  • ou vacuumdb --analyze-only

• Statistiques obsolètes
• Opérations ponctuelles (batch, import de données)
• Dépendances fonctionnelles

Les statistiques sont-elles à jour ?

• Traitement lourd
  • faire tout de suite ANALYZE
• Table trop grosse
  • régler l’échantillonnage
  • régler l’autovacuum sur cette table
• Retard de mise à jour suite à crash ou restauration

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

• Si c1 = 1 pour 20 % des lignes
• et c2 = 2 pour 10 % des lignes
• Alors le planificateur calcule : 2 % des lignes (20 % × 10 %)
  • Mais en réalité ?
• Pour corriger :

CREATE STATISTICS corr1_c1_c2 ON c1,c2 FROM corr1 ;

• Un échantillonnage des données très efficace…
• …mais pas parfait !
• Il faut bien comprendre son fonctionnement
• Possibilité d’ajuster la collecte des statistiques si besoin

• Plongée profonde dans les plans d’exécution
  • Frédéric Yhuel, PGSessions 17, février 2024
  • https://youtu.be/xneDEIzBw3I
• A Deep Dive into Postgres Statistics
  • Louise Grandjonc Leinweber, PGConf.EU, octobre 2024
  • https://youtu.be/ApAClPFJ_rU

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

https://dali.bo/m6_quiz

• 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

• Procédures stockées (appelées avec CALL)
• 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
  • conditions de jointure
• Sélectivité
  • % 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 filtrages

• Tables
  • services : 4 lignes
  • services_big : 40 000 lignes
  • employes : 14 lignes
  • employes_big : ~500 000 lignes
• Index
  • service*.num_service (clés primaires)
  • employes*.matricule (clés primaires)
  • employes*.date_embauche
  • employes_big.num_service (clé étrangère)

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

 Hash Join  (cost=1.06..2.28 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)

Rappels :

• SQL est un langage déclaratif
• Planificateur : trouver le meilleur plan
• Énumère tous les plans d’exécution possible
  • tous ou presque…
• Statistiques + configuration + règles → coût
• Coût le plus bas = meilleur plan

• Règle 1 : récupérer le bon résultat

• Règle 2 : 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
  • ex : critere IN (SELECT ...)
• 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

L’optimiseur doit choisir :

• Stratégie d’accès aux lignes
  • parcours de table, d’index, fonction, etc.
• Stratégie d’utilisation des jointures
  • ordre
  • ordre des tables jointes
  • type (Nested Loop, Merge Join, Hash Join…)
• Stratégie d’agrégation
  • brut, trié, haché
• En version parallélisée ?
• Tenir compte de la consommation mémoire

• Processus supplémentaires pour certains nœuds
  • parer à la limitation par le CPU
• En lecture (sauf exceptions)
• Parcours séquentiel
• Jointures : Nested Loop / Hash Join / Merge Join
• Agrégats
• Parcours d’index (B-Tree uniquement)
• Création d’index B-Tree
• Certaines créations de table et vues matérialisées
• DISTINCT (v15)

• Lourd à déclencher
• Pas sur les écritures de données
• Très peu d’opérations DDL gérées
• Pas en cas de verrous
• Pas sur les curseurs
• En évolution à chaque version

• 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

• Coûts à connaître :
  • accès au disque séquentiel / non séquentiel
  • traitement d’un enregistrement issu d’une table
  • traitement d’un enregistrement issu d’un index
  • application d’un opérateur
  • traitement d’un enregistrement dans un parcours parallélisé
  • mise en place d’un parcours parallélisé
  • mise en place du JIT, du parallélisme…
• Chaque coût = un paramètre
  • modifiable dynamiquement avec SET

• ANALYZE : exécution (danger !)
  • BUFFERS : blocs read/hit/written/dirtied, shared/local/temp
  • WAL : écritures dans les journaux
  • SERIALIZE : coût de sérialisation (v17)
  • SETTINGS : paramètrage en cours
• VERBOSE : champs manipulés, workers
• MEMORY : mémoire consommée par le planificateur (v17)
• GENERIC_PLAN : plan générique (requête préparée, v16)
• Désactivables avec OFF :
  • COSTS, TIMING, SUMMARY (dont planification)
• FORMAT : sortie en texte, XML, JSON, YAML

• 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
• qui produisent des données
• ou en consomment et en retournent
• Chaque nœud consomme les données produites par le(s) nœud(s) parent(s)
• Le nœud final retourne les données à l’utilisateur

• Parcours de table
• Parcours d’index
• Autres parcours

• Seq Scan
• Parallel Seq Scan

• Seq Scan
  • seq_page_cost (défaut : 1)
  • cpu_tuple_cost & cpu_operator_cost
  • enable_seqscan
• Parallel Seq Scan
  • parallel_tuple_cost, min_parallel_table_scan_size
  • et les autres paramètres de la parallélisation

• Index Scan
• Index Only Scan
• Bitmap Index Scan
• et leurs versions parallélisées (B-Tree)

• Très performant
• Besoin d’un VACUUM récent
  • sinon trop de Heap Fetches
• Ne fonctionne pas pour les index fonctionnels
  • ajouter une colonne générée ?

• random_page_cost (4 ou moins ?)
• cpu_index_tuple_cost
• effective_cache_size (⅔ de la RAM ?)
• Selon le disque :
  • effective_io_concurrency
  • maintenance_io_concurrency
• min_parallel_index_scan_size
• enable_indexscan, enable_indexonlyscan, enable_bitmapscan

• Function Scan
• Values Scan
• …et d’autres

• 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
• Pour EXISTS, IN et certaines jointures externes
  • Hash Semi Join & Hash Anti Join
• Paramètres :
  • work_mem ( et hash_mem_multiplier )
  • seq_page_cost & random_page_cost.
  • enable_nestloop, enable_hashjoin, enable_mergejoin

• Deux nœuds de tri :
  • Sort
  • Incremental Sort
• Regroupement/agrégation :
  • Aggregate
  • Hash Aggregate
  • Group Aggregate
  • Mixed Aggregate
  • Partial/Finalize Aggregate
• Paramètres :
  • enable_hashagg
  • work_mem & hash_mem_multiplier

• Limit
• Unique (DISTINCT)
• Append (UNION ALL), Except, Intersect
• Gather (parallélisme)
• InitPlan, Subplan, etc.
• Memoize (14+)

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

• PostgreSQL choisit l’ordre des jointures
  • uniquement pour les X premières tables
  • où X = join_collapse_limit (défaut : 8)
• Les jointures supplémentaires sont ajoutées après
• … d’où plans non optimaux
• → augmenter join_collapse_limit si nécessaire (12-15)
  • ainsi que from_collapse_limit
• Algorithme GEQO

SELECT *
FROM employes_big
WHERE extract('year' from date_embauche) = 2006 ;

• 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 % …
• CREATE STATISTIC (v14)

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

• PostgreSQL peut utiliser un index dans ce cas
• MAIS si l’encodage n’est pas C
  • déclarer l’index avec une classe d’opérateur
  • varchar_pattern_ops / text_pattern_ops, etc.

  CREATE INDEX ON matable (champ_texte varchar_pattern_ops);

• Outils pour LIKE '%mot%' :
  • pg_trgm,
  • Full Text Search

• 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
  • souvent utilisé de manière abusive
  • tri !!
  • barrière à l’optimisation
• Penser à :
  • DISTINCT ON
  • GROUP BY
• Une clé primaire permet de dédoublonner efficacement

• Statistiques pas à jour/peu précises/oubliées
• Trop de lignes retournées
• Ordre des colonnes de l’index (B-tree)
• Index trop gros
• Prédicat avec transformation

WHERE col1 + 2 > 5  →  WHERE col1 > 5 - 2

• Opérateur non supporté par l’index

WHERE col1 <> 'valeur';

• Paramètres
  • random_page_cost
  • effective_cache_size

• NOT IN avec une sous-requête
  • remplacer par NOT EXISTS
• UNION entraîne un tri systématique
  • préférer UNION ALL
• Sous-requête dans le SELECT
  • utiliser LATERAL

• Certains regrettent l’absence de hints
• C’est la politique du projet :
  • vouloir ne signifie pas avoir besoin
  • PostgreSQL est un projet libre qui a le luxe de se défaire de la pression du marché
  • cela permet d’être plus facilement et rapidement mis au courant des problèmes de l’optimiseur
• Ne pensez pas être plus intelligent que le planificateur
• Mais il ne peut faire qu’avec ce qu’il a

• auto_explain
• pg_hint_plan
• plantuner
• HypoPG

• Tracer les plans des requêtes lentes automatiquement
• Contrib officielle
• Mise en place globale (traces) :
  • globale :

  shared_preload_libraries='auto_explain'   -- redémarrage !

  ALTER DATABASE erp SET auto_explain.log_min_duration = '3s' ;

  • session :

  LOAD 'auto_explain' ;
  SET auto_explain.log_analyze TO true;

• Pour :
  • interdire certains index
  • forcer à zéro les statistiques d’une table vide
• Intéressant en développement pour tester les plans
  • pas en production !

• Pour :
  • forcer l’utilisation d’un nœud entre deux tables
  • imposer une valeur de paramètre
  • appliquer automatiquement ces hints à des requêtes

• Extension PostgreSQL
• Création d’index hypothétiques pour tester leur intérêt
  • avant de les créer pour de vrai
• Limitations : surtout B-Tree, statistiques <!– Masqué jusqu’au le jour où hypoPG 2 sortira
• Partitions hypothétiques (pour la v2, actuellement en beta) –>

• Planificateur très avancé
• Ne pensez pas être plus intelligent que lui
• Il faut bien comprendre son fonctionnement

https://dali.bo/j2_quiz

• Types de nœuds principaux
  • parcours de table (Seq Scan)
  • parcours d’index (Index Scan & variantes)
  • jointures (Nested Loop, Merge Join, Hash Join…)
  • opérateurs sur des ensembles (Append, Except, Intersect…)
• Et quelques autres :
  • Sort, _*Aggregate_, Unique, Limit, Materialize, Memoize…
• Plusieurs variantes pour chacun

• Rien en entrée
• Renvoie un ensemble de données des tables
  • trié ou non
  • filtré ou non
• Exemples :
  • parcours séquentiel d’une table, avec ou sans filtrage des enregistrements produits
  • parcours via un index, avec ou sans filtrage supplémentaire

• Parcours séquentiel de la table (Sequential Scan ou Seq Scan)
  • parallélisation possible (Parallel Seq Scan)
• La table est lue entièrement
  • même si seulement quelques lignes satisfont la requête
  • sauf avec LIMIT sans ORDER BY
• Séquentiellement, par bloc de 8 ko

• Idéal pour :
  • petites tables
  • % de retour élevé des blocs
  • partitions entières
• Contre-performant pour :
  • quelques lignes d’une grande table
• Optimisation : synchronize_seqscans

• Parcours « aléatoire » de l’index
• Pour chaque enregistrement correspondant à la recherche
  • accès direct au bloc de la table
  • vérification de la visibilité de la ligne
  • renvoie des lignes triées

• Intéressant pour :
  • les accès à peu de lignes dans la table
  • les tris (ORDER BY…)
• Non adapté à :
  • récupération de trop nombreuses lignes
• Coût en maintenance
• Parallélisation (Parallel Index Scan)
  • B-Tree uniquement

• Bitmap Index Scan / Bitmap Heap Scan
• Réduire les allers-retours index ⇆ table
  • trouver les blocs de l’index
  • puis lecture des blocs de la table
• Combinaison de plusieurs index en mémoire
  • nœud BitmapAnd
  • nœud BitmapOr
• Index B-tree, GIN, BRIN…

• Indiqué pour :
  • de nombreuses lignes
  • bonne corrélation
  • combinaison de critères IN, ANY
  • combinaison d’index AND/OR
• Mais :
  • coût de démarrage important
  • pas intéressant avec LIMIT
• Parallélisation possible
• Sensible à :
  • work_mem (sinon lossy)
  • effective_cache_size
  • effective_io_concurrency

CREATE INDEX ON t1 (c1) ;

SELECT c1 FROM t1 WHERE c1 < 10 ;

• Avant 9.2 : PostgreSQL devait lire l’index + la table
• À présent : le planificateur utilise la Visibility Map
  • nœud Index Only Scan
  • B-tree
  • GiST, SP-GiST : selon opérateur

• Extrêmement performant
  • requêtes avec peu de colonnes
• Conditions
  • VACUUM fréquent
  • …sinon Heap Fetches

• TID Scan
• Function Scan
• Values
• Result

• Prend 2 ensembles de données en entrée
  • inner (interne)
  • outer (externe)
• Et renvoie un seul ensemble de données
• Nested Loop, Merge Join, Hash Join

« Boucles imbriquées »

• Ensemble externe parcouru une fois
• Pour chaque ligne :
  • chercher les lignes dans la relation interne selon la condition de jointure
    • émettre ces lignes en résultat

• Coût proportionnel à la taille des ensembles
• Intérêts
  • démarrage rapide
  • pas besoin de mémoire
  • marche toujours
• Inadapté à :
  • grands nombre de lignes
• Index chaudement conseillé

Jointure d’ensembles triés

• 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
  • si condition de jointure émettre la ligne

• Intérêt
  • le plus rapide sur une grosse volumétrie
  • pas de besoin de mémoire (si tri déjà fait…)
• Limites
  • uniquement les égalités
  • besoin de données triées (Sort ou index)
• Produit un résultat trié

Jointure par hachage

• Hacher les lignes de l’ensemble interne
• Pour chaque ligne de l’ensemble externe
  • hacher la ligne lue
  • comparer ce hachage aux lignes hachées de l’ensemble interne
  • si correspondance : émettre la ligne

• Avantages :
  • idéal pour joindre une grande table à une petite
• Limites :
  • uniquement conditions avec égalité
  • coût de démarrage important
  • mémoire, volumétrie !
  • sensible aux mauvaises estimations
• Paramétrage mémoire :
  • work_mem × hash_mem_multiplier
• Parallélisable

• Entrée : un ou plusieurs ensembles de données
• Sortie : un ensemble de données
• Souvent des requêtes sur des tables partitionnées ou héritées
• Exemples typiques
  • Append et MergeAppend
  • Intersect
  • Except

• Entrée :
  • plusieurs ensembles de données
• Sortie :
  • non triée, non dédupliquée
• Utilisation :
  • partitionnement, tables héritées
  • UNION ALL et des UNION
  • NB : UNION sans ALL élimine les doublons (lourd !)
• Parallélisable

• Append avec optimisation du tri
• Sortie triée
• Utilisation :
  • UNION ALL, partitionnement/héritage
  • avec parcours triés
  • idéal avec LIMIT

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

• Entrée :
  • un ensemble de données
• Renvoie :
  • un ensemble trié
• Nœuds
  • Sort,
  • Incremental Sort

• Utilisé pour le ORDER BY
  • mais aussi DISTINCT, GROUP BY, UNION
  • souvent avant une jointure Merge Join
• Gros délai de démarrage
• Plusieurs méthodes :
  • quicksort (en mémoire)
  • top-N heapsort (en mémoire, si LIMIT)
  • external merge (sur disque)

• Pour tri multicolonnes,
  • déjà partiellement triées
• Idéal quand un index existe sur les premières colonnes du tri
  • ORDER BY, GROUP BY, UNION
  • DISTINCT (v16)
  • jointures de type Merge Join
• Délai de démarrage réduit
• Moins de mémoire
• PostgreSQL 16

• Entrée :
  • un ensemble de données
• Renvoie :
  • un ensemble trié
• Nœuds
  • Aggregate
  • HashAggregate
  • GroupAggregate

• Pour un seul résultat
  • SELECT max(…), count(*), sum(…)…
  • plusieurs agrégats au besoin
• Penser aux index pour accélérer
  • count(*)
  • max/min

• Hachage de chaque n-uplet de regroupement
• Accès direct à chaque n-uplet pour appliquer la fonction d’agrégat
• Typiquement : GROUP BY
• Mémoire :
  • work_mem×hash_mem_multiplier

• Pré-requis :
  • données déjà triées : Sort ou index
• Parcours des données
  • regroupement du groupe précédent arrivé à une donnée différente
• GROUPING SETS, ROLLUP, CUBE…

• Pour plusieurs niveaux d’agrégation
• Utilise des tables de hachage
• Typiquement : ROLLUP, CUBE

• Exemples typiques
  • Unique
  • Limit
  • InitPlan, SubPlan

• Pour DISTINCT
• Entrée : données déjà triées
• Renvoie : la donnée précédente une fois arrivé à une donnée différente
• Résultat trié
• Souvent remplacé par un agrégat

• Limite le nombre de résultats renvoyés
• LIMIT et OFFSET
• Peut modifier complètement le plan
  • favorise les nœuds au coût de démarrage réduit (Seq Scan, Nested Loop)

• Cache de résultat
• Utilisable par la table interne des Nested Loop
• Utile si :
  • peu de valeurs distinctes dans l’ensemble interne
  • beaucoup de valeurs dans l’ensemble externe
  • peu de correspondance entre les deux ensembles
• Paramètres : work_mem×hash_mem_multiplier
• Sensible à ndistinct

• De nombreux nœuds
• Qu’il faut apprendre
• Expérimentez !

• Deux types de supervision
  • occasionnelle
  • automatique
• Superviser le matériel et le système
• Superviser PostgreSQL et ses statistiques
• Utiliser les bons outils

• Supervision occasionnelle système
  • Linux
  • Windows
• Supervision occasionnelle PostgreSQL
• Outils

• Nombreux outils
• Les tester pour les sélectionner

• ps est l’outil de base pour les processus
• Exemples
  • ps aux
  • ps f -f -u postgres

• Principal intérêt : %CPU et %MEM
• Intérêts secondaires
  • charge CPU
  • consommation mémoire
• Autres outils
  • atop, htop

• Principal intérêt : %IO
• Accès root nécessaire

• Outil le plus fréquemment utilisé
• Principal intérêt
  • lecture et écriture disque
  • iowait
• Intérêts secondaires
  • nombre de processus en attente

• Une ligne par partition
• Intéressant pour connaître la partition la plus concernée par
  • les lectures
  • ou les écritures

• Outil le plus ancien
• Récupère des statistiques de façon périodique
• Permet de lire les statistiques datant de plusieurs heures, jours, etc.

• Principal intérêt : connaître la répartition de la mémoire

• Là aussi, nombreux outils
• Les tester pour les sélectionner

• ps et grep en une commande

• Surveillance des processus
• Filtres
• Récupération de la ligne de commande, identificateur de session et utilisateur
• https://docs.microsoft.com/en-us/sysinternals/downloads/procmon

• Semblable à top
• https://docs.microsoft.com/en-us/sysinternals/downloads/process-explorer

• Semblable à sysstat
• Mais avec plus d’informations
• Et des graphes immédiats

• Plusieurs aspects à surveiller :
  • activité de la base
  • activité sur les tables
  • requêtes SQL
  • écritures

• Des informations globales à chaque base
• Nombre de sessions
• Nombre de transactions validées/annulées
• Nombre d’accès blocs
• Nombre d’accès enregistrements
• Taille et nombre de fichiers temporaires
• Erreurs de checksums
• Temps d’entrées/sorties

• qui est connecté ?
• qui fait quoi ?
• qui est bloqué ?
• qui bloque les autres ?
• comment arrêter une requête ?

• Liste des processus
  • sessions (backends)
  • processus en tâche de fond
• Requête en cours/dernière exécutée
  • query_id
• idle in transaction : attention !
• Sessions en attente : wait_events

• Annuler une requête
  • pg_cancel_backend (pid int)
  • pg_ctl kill INT pid (éviter)
  • kill -SIGINT pid, kill -2 pid (éviter)
• Fermer une connexion
  • pg_terminate_backend(pid int, timeout bigint)
  • pg_ctl kill TERM pid (éviter)
  • kill -SIGTERM pid, kill -15 pid (éviter)
• Jamais kill -9 ou kill -SIGKILL !!

Quand le SSL est activé sur le serveur, cette vue indique pour chaque connexion cliente les informations suivantes :

• SSL activé ou non
• Version SSL
• Suite de chiffrement
• Nombre de bits pour algorithme de chiffrement
• Compression activée ou non
• Distinguished Name (DN) du certificat client

• Visualisation des verrous en place
• Tous types de verrous sur objets
• Complexe à interpréter
  • verrous sur enregistrements pas directement visibles
  • https://kb.dalibo.com/utilisation/verrouillage

• Message dans les traces
  • uniquement pour les attentes de plus d’une seconde
  • paramètre log_lock_waits à on
  • rapport pgBadger disponible

• Message dans les traces
  • à chaque connexion/déconnexion
  • paramètre log_connections et log_disconnections
  • rapport pgBadger disponible

• Quelle taille font mes objets ?
• Quel est leur taux de fragmentation ?
• Comment sont-ils accédés ?

• Pour une table :

  • pg_relation_size : heap
  • pg_table_size : + TOAST + divers

• Index : pg_indexes_size

• Table + index : pg_total_relation_size

• Plus lisibles avec pg_size_pretty

• Fragmentation induite par MVCC
  • tables et index
• Mesure précise de la fragmentation :
  • extension pgstattuple
• Estimer la fragmentation :
  • https://github.com/ioguix/pgsql-bloat-estimation
  • pgstattuple_approx() (tables)
  • supervision avec check_pgactivity

• Statistiques niveau «ligne»
• Nombre de lignes insérées/mises à jour/supprimées
• Type et nombre d’accès
• Opérations de maintenance
• Détection des tables mal indexées ou très accédées

• Vue par index
• Nombre d’accès et efficacité

• Opérations au niveau bloc
• Demandés au système ou trouvés dans le cache de PostgreSQL
• Pour calculer des hit ratios :

   idx_blks_hit::float / (idx_blks_read + idx_blks_hit)

Vue synthétique des opérations disques selon :

• le type de backend
  • backend, autovacuum, checkpointer…
• le type d’objet
  • table ou table temporaire
• le contexte
  • normal, vacuum, bulkread/bulkwrite…

Penser à activer track_io_timing

• Quelles sont les requêtes lentes ?
• Quelles sont les requêtes les plus fréquentes ?
• Quelles requêtes génèrent des fichiers temporaires ?
• Quelles sont les requêtes bloquées ?
  • et par qui ?
• Progression d’une requête

• log_min_duration_statements = <temps minimal d’exécution>
  • 0 permet de tracer toutes les requêtes
  • trace des paramètres
  • traces exploitables par des outils tiers
  • pas d’informations sur les accès, ni des plans d’exécution
• log_min_duration_sample = <temps minimal d’exécution>
  • log_statement_sample_rate et/ou log_transaction_sample_rate
  • trace d’un ratio des requêtes
• D’autres paramètres existent mais sont peu intéressants

• log_temp_files = <taille minimale>
  • 0 trace tous les fichiers temporaires
  • associe les requêtes SQL qui les génèrent
  • traces exploitable par des outils tiers

• Ajoute la vue statistique pg_stat_statements
• Les requêtes sont normalisées
• Indique les requêtes exécutées
  • avec la durée d’exécution, l’utilisation du cache, etc.

Métriques intéressantes :

• Durée d’exécution :
  • total_exec_time
  • min_exec_time/max_exec_time
  • stddev_exec_time
  • mean_exec_time
• Avant la version 13, les colonnes n’avaient pas _exec dans leur nom
• Nombre de lignes retournées : rows

• Durée d’optimisation (v13+) :
  • total_plan_time
  • min_plan_time/max_plan_time
  • stddev_plan_time
  • mean_plan_time

• Accès à la mémoire partagée
  • shared_blks_hit/read/dirtied/written
• Accès à la mémoire de la session (tables temporaires…)
  • local_blks_hit/read/dirtied/written
• Lecture/écriture de fichiers temporaires
  • temp_blks_read/written
• Temps d’accès en entrée/sortie
  • blk_read_time/blk_write_time

• Journaux de transactions (v13+) :
  • wal_records
  • wal_fpi
  • wal_bytes

• JIT (v15+)
  • jit_functions
  • jit_generation_time
  • etc

• Vue pg_stat_activity
  • colonnes wait_event et wait_event_type
• Vue pg_locks
  • colonne granted
  • colonne waitstart (v14+)
• Fonction pg_blocking_pids

• Hélas, non

• Quelle quantité de données sont écrites ?
• Quel canal d’écriture est utilisé ?

• log_checkpoints = on
• Affiche des informations à chaque checkpoint :
  • mode de déclenchement
  • volume de données écrits
  • durée du checkpoint
• Trace exploitable par des outils tiers

• pg_stat_bgwriter

• pg_stat_checkpointer (v17)

• Activité des écritures dans les fichiers de données

• Visualisation du volume d’allocations et d’écritures

• Sauvegarde PITR & log shipping :
  • pg_stat_archiver
• Réplication :
  • pg_stat_replication
  • pg_stat_database_conflicts

• Bon fonctionnement de l’archivage
• Quand et combien d’erreurs d’archivages se sont produites

• pg_stat_replication :
  • État des serveurs secondaires (streaming)
  • Mesure du lag
• pg_stat_database_conflicts :
  • nombre de conflits de réplication
  • par type

• Différents outils existent autour de PostgreSQL
• Outils d’analyse occasionnel :
  • pg_activity
• Outils d’analyse des traces :
  • pgBadger
• Outils d’analyse des statistiques :
  • pgCluu, pg_stat_statements, PoWA

• top pour PostgreSQL
• Libre, script en python
• Affiche :
  • les requêtes en cours
  • les sessions bloquées
  • les sessions bloquantes
• https://github.com/dalibo/pg_activity/

• Script Perl
• Traite les journaux applicatifs
• Recherche des informations sur les requêtes
• Génération d’un rapport HTML très détaillé
• https://pgbadger.darold.net/

• Outils de collectes de métriques de performances
  • https://github.com/darold/pgcluu
  • génère un rapport HTML complet
• Différents aspects mesurés :
  • informations sur le système
  • consommation des ressources CPU, RAM, I/O
  • utilisation de la base de données

• Objectif : identifier les requêtes coûteuses
  • sans devoir accéder aux logs
  • quasi en temps-réel
• Background worker
  • dépendant de pg_stat_statements
• https://github.com/powa-team

• Un système est pérenne s’il est bien supervisé
• Les systèmes de supervision automatique ont souvent besoin d’être complétés
• PostgreSQL fourni énormément d’indicateurs utiles à la supervision
• Les outils de supervision ponctuels sont utiles pour rapidement diagnostiquer l’état d’un serveur

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

https://dali.bo/h2_quiz