Dalibo SCOP
Formation | Module M2 |
Titre | Configuration de PostgreSQL |
Révision | 24.09 |
https://dali.bo/m2_pdf | |
EPUB | https://dali.bo/m2_epub |
HTML | https://dali.bo/m2_html |
Slides | https://dali.bo/m2_slides |
TP | https://dali.bo/m2_tp |
TP (solutions) | https://dali.bo/m2_solutions |
Vous trouverez en ligne les différentes versions complètes de ce document.
Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :
Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.
Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.
Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.
Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode
Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.
PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.
Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 12 à 16.
Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.
Sauf précision contraire, le système d’exploitation utilisé est Linux.
Ces paramètres sont en lecture seule, mais peuvent être consultés par
la commande SHOW
, ou en interrogeant la vue
pg_settings
. Il est possible aussi d’obtenir l’information
via la commande pg_controldata
.
block_size
est la taille d’un bloc de données de la
base, par défaut 8192 octets ;wal_block_size
est la taille d’un bloc de journal, par
défaut 8192 octets ;segment_size
est la taille maximum d’un fichier de
données, par défaut 1 Go ;wal_segment_size
est la taille d’un fichier de journal
de transactions (WAL), par défaut 16 Mo.Ces paramètres sont tous fixés à la compilation, sauf
wal_segment_size
à partir de la version 11 :
initdb
accepte alors l’option --wal-segsize
et
l’on peut monter la taille des journaux de transactions à 1 Go. Cela n’a
d’intérêt que pour des instances générant énormément de journaux.
Recompiler avec une taille de bloc de 32 ko s’est déjà vu sur de très
grosses installations (comme le rapporte par exemple Christophe
Pettus (San Francisco, 2023)) avec un shared_buffers
énorme, mais cette configuration est très peu testée, nous la
déconseillons dans le cas général.
Un moteur compilé avec des options non standard ne pourra pas ouvrir des fichiers n’ayant pas les mêmes valeurs pour ces options.
Des tailles non standard vous exposent à rencontrer des problèmes avec des outils s’attendant à des blocs de 8 ko. (Remontez alors le bug.)
Les fichiers de configuration sont habituellement les 4 suivants :
postgresql.conf
: il contient une liste de paramètres,
sous la forme paramètre=valeur
. Tous les paramètres énoncés
précédemment sont modifiables (et présents) dans ce fichier ;pg_hba.conf
: il contient les règles d’authentification
à la base.pg_ident.conf
: il complète pg_hba.conf
,
quand nous déciderons de nous reposer sur un mécanisme
d’authentification extérieur à la base (identification par le système ou
par un annuaire par exemple) ;postgresql.auto.conf
: il stocke les paramètres de
configuration fixés en utilisant la commande ALTER SYSTEM
et surcharge donc postgresql.conf
.C’est le fichier le plus important. Il contient le paramétrage de
l’instance. PostgreSQL le cherche au démarrage dans le PGDATA. Par
défaut, dans les versions compilées, ou depuis les paquets sur Red Hat,
CentOS ou Rocky Linux, il sera dans le répertoire principal avec les
données (/var/lib/pgsql/15/data/postgresql.conf
par
exemple). Debian le place dans /etc
(/etc/postgresql/15/main/postgresql.conf
pour l’instance
par défaut).
Dans le doute, il est possible de consulter la valeur du paramètre
config_file
, ici dans la configuration par défaut sur Rocky
Linux :
# SHOW config_file;
config_file
--------------------------------------------- /var/lib/postgresql/15/data/postgresql.conf
Ce fichier contient un paramètre par ligne, sous le format :
clé = valeur
Les commentaires commencent par « # » (croisillon) et les chaînes de caractères doivent être encadrées de « ’ » (single quote). Par exemple :
data_directory = '/var/lib/postgresql/15/main'
listen_addresses = 'localhost'
port = 5432
shared_buffers = 128MB
Les valeurs de ce fichier ne seront pas forcément les valeurs actives !
Nous allons en effet voir que l’on peut les surcharger.
Le paramétrage ne dépend pas seulement du contenu de
postgresql.conf
.
Nous pouvons inclure d’autres fichiers depuis
postgresql.conf
grâce à l’une de ces directives :
include = 'nom_fichier'
include_if_exists = 'nom_fichier'
include_dir = 'répertoire' # contient des fichiers .conf
Le ou les fichiers indiqués sont alors inclus à l’endroit où la
directive est positionnée. Avec include
, si le fichier
n’existe pas, une erreur FATAL
est levée ; au contraire la
directive include_if_exists
permet de ne pas s’arrêter si
le fichier n’existe pas. Ces directives permettent notamment des
ajustements de configuration propres à plusieurs machines d’un ensemble
primaire/secondaires dont le postgresql.conf
de base est
identique, ou de gérer la configuration hors de
postgresql.conf
.
Si des paramètres sont répétés dans postgresql.conf
,
éventuellement suite à des inclusions, la dernière occurrence écrase les
précédentes. Si un paramètre est absent, la valeur par défaut
s’applique.
Ces paramètres peuvent être surchargés par le fichier
postgresql.auto.conf
, qui contient le résultat des
commandes de ce type :
ALTER SYSTEM SET paramètre = valeur ;
Ce fichier est principalement utilisé par les administrateurs et les outils qui n’ont pas accès au système de fichiers.
Si des options sont passées directement en arguments à
pg_ctl
(situation rare), elles seront prises en compte en
priorité par rapport à celles de ces fichiers de configuration.
Il est possible de surcharger les options modifiables à chaud par utilisateur, par base, et par combinaison « utilisateur+base », avec par exemple :
ALTER ROLE nagios SET log_min_duration_statement TO '1min';
ALTER DATABASE dwh SET work_mem TO '1GB';
ALTER ROLE patron IN DATABASE dwh SET work_mem TO '2GB';
Ces surcharges sont visibles dans la table
pg_db_role_setting
ou via la commande \drds
de
psql
.
Ensuite, un utilisateur peut changer à volonté les valeurs de beaucoup de paramètres au sein d’une session :
SET parametre = valeur ;
ou même juste au sein d’une transaction :
SET LOCAL parametre = valeur ;
Au final, l’ordre des surcharges est le suivant :
paramètre par défaut
-> postgresql.conf
-> ALTER SYSTEM SET (postgresql.auto.conf)
-> option de pg_ctl / postmaster
-> paramètre par base
-> paramètre par rôle
-> paramètre base+rôle
-> paramètre dans la chaîne de connexion
-> paramètre de session (SET) -> paramètre de transaction (SET LOCAL)
La meilleure source d’information sur les valeurs actives est la vue
pg_settings
:
SELECT name,source,context,setting,boot_val,reset_val
FROM pg_settings
WHERE name IN ('client_min_messages', 'log_checkpoints', 'wal_segment_size');
name | source | context | setting | boot_val | reset_val
---------------------+----------+----------+----------+----------+-----------
client_min_messages | default | user | notice | notice | notice
log_checkpoints | default | sighup | off | off | off wal_segment_size | override | internal | 16777216 | 16777216 | 16777216
Nous constatons par exemple que, dans la session ayant effectué la
requête, la valeur du paramètre client_min_messages
a été
modifiée à la valeur debug
. Nous pouvons aussi voir le
contexte dans lequel le paramètre est modifiable : le
client_min_messages
est modifiable par l’utilisateur dans
sa session. Le log_checkpoints
seulement par
sighup
, c’est-à-dire par un pg_ctl reload
, et
le wal_segment_size
n’est pas modifiable après
l’initialisation de l’instance.
De nombreuses autres colonnes sont disponibles dans
pg_settings
, comme une description détaillée du paramètre,
l’unité de la valeur, ou le fichier et la ligne d’où provient le
paramètre. Le champ pending_restart
indique si un paramètre
a été modifié mais attend encore un redémarrage pour être appliqué.
Il existe aussi une vue pg_file_settings
, qui indique la
configuration présente dans les fichiers de configuration (mais pas
forcément active !). Elle peut être utile lorsque la configuration est
répartie dans plusieurs fichiers. Par exemple, suite à un
ALTER SYSTEM
, les paramètres sont ajoutés dans
postgresql.auto.conf
mais un rechargement de la
configuration n’est pas forcément suffisant pour qu’ils soient pris en
compte :
ALTER SYSTEM SET work_mem TO '16MB' ;
ALTER SYSTEM SET max_connections TO 200 ;
SELECT pg_reload_conf() ;
pg_reload_conf
---------------- t
SELECT * FROM pg_file_settings
WHERE name IN ('work_mem','max_connections')
ORDER BY name ;
-[ RECORD 1 ]-------------------------------------------------
sourcefile | /var/lib/postgresql/15/data/postgresql.conf
sourceline | 64
seqno | 2
name | max_connections
setting | 100
applied | f
error |
-[ RECORD 2 ]-------------------------------------------------
sourcefile | /var/lib/postgresql/15/data/postgresql.auto.conf
sourceline | 4
seqno | 17
name | max_connections
setting | 200
applied | f
error | setting could not be applied
-[ RECORD 3 ]-------------------------------------------------
sourcefile | /var/lib/postgresql/15/data/postgresql.auto.conf
sourceline | 3
seqno | 16
name | work_mem
setting | 16MB
applied | t error |
postgresql.conf
contient environ 300 paramètres. Il est
séparé en plusieurs sections, dont les plus importantes figurent
ci-dessous. Il n’est pas question de les détailler toutes.
La plupart des paramètres ne sont jamais modifiés. Les défauts sont souvent satisfaisants pour une petite installation. Les plus importants sont supposés acquis (au besoin, voir la formation DBA1).
Les principales sections sont :
Connections and authentication
S’y trouveront les classiques listen_addresses
,
port
, max_connections
,
password_encryption
, ainsi que les paramétrages TCP
(keepalive) et SSL.
Resource usage (except WAL)
Cette partie fixe des limites à certaines consommations de ressources.
Sont normalement déjà bien connus shared_buffers
,
work_mem
et maintenance_work_mem
(qui seront
couverts extensivement plus loin).
On rencontre ici aussi le paramétrage du VACUUM
(pas
directement de l’autovacuum !), du background writer, du
parallélisme dans les requêtes.
Write-Ahead Log
Les journaux de transaction sont gérés ici. Cette partie sera également détaillée dans un autre module.
Tout est prévu pour faciliter la mise en place d’une réplication sans avoir besoin de modifier cette partie sur le primaire.
Dans la partie Archiving, l’archivage des journaux peut être activé pour une sauvegarde PITR ou une réplication par log shipping.
Depuis la version 12, tous les paramètres de restauration (qui
peuvent servir à la réplication) figurent aussi dans les sections
Archive Recovery et Recovery Target. Auparavant, ils
figuraient dans un fichier recovery.conf
séparé.
Replication
Cette partie fournit le nécessaire pour alimenter un secondaire en réplication par streaming, physique ou logique.
Ici encore, depuis la version 12, l’essentiel du paramétrage nécessaire à un secondaire physique ou logique est intégré dans ce fichier.
Query tuning
Les paramètres qui peuvent influencer l’optimiseur sont à définir
dans cette partie, notamment seq_page_cost
et
random_page_cost
en fonction des disques, et éventuellement
le parallélisme, le niveau de finesse des statistiques, le JIT…
Reporting and logging
Si le paramétrage par défaut des traces ne convient pas, le modifier
ici. Il faudra généralement augmenter leur verbosité. Quelques
paramètres log_*
figurent dans d’autres sections.
Autovacuum
L’autovacuum fonctionne généralement convenablement, et des ajustements se font généralement table par table. Il arrive cependant que certains paramètres doivent être modifiés globalement.
Client connection defaults
Cette partie un peu fourre-tout définit le paramétrage au niveau d’un client : langue, fuseau horaire, extensions à précharger, tablespaces par défaut…
Lock management
Les paramètres de cette section sont rarement modifiés.
L’authentification est paramétrée au moyen du fichier
pg_hba.conf
. Dans ce fichier, pour une tentative de
connexion à une base donnée, pour un utilisateur donné, pour un
transport (IP, IPV6, Socket Unix, SSL ou non), et pour une source
donnée, ce fichier permet de spécifier le mécanisme d’authentification
attendu.
Si le mécanisme d’authentification s’appuie sur un système externe
(LDAP, Kerberos, Radius…), des tables de correspondances entre
utilisateur de la base et utilisateur demandant la connexion peuvent
être spécifiées dans pg_ident.conf
.
Ces noms de fichiers ne sont que les noms par défaut. Ils peuvent
tout à fait être remplacés en spécifiant de nouvelles valeurs de
hba_file
et ident_file
dans
postgresql.conf
(les installations Red Hat et Debian
utilisent là aussi des emplacements différents, comme pour
postgresql.conf
).
Leur utilisation est décrite dans notre première formation.
Par défaut, PostgreSQL se charge du placement des objets sur le disque, dans son répertoire des données, mais il est possible de créer des répertoires de stockage supplémentaires, nommés tablespaces.
Un tablespace, vu de PostgreSQL, est un espace de stockage des objets (tables et index principalement). Son rôle est purement physique, il n’a pas à être utilisé pour une séparation logique des tables (c’est le rôle des bases et des schémas), encore moins pour gérer des droits.
Pour le système d’exploitation, il s’agit juste d’un répertoire, déclaré ainsi :
CREATE TABLESPACE ssd LOCATION '/var/lib/postgresql/tbl_ssd';
L’idée est de séparer physiquement les objets suivant leur utilisation. Les cas d’utilisation des tablespaces dans PostgreSQL sont :
cannot extend file
.Sans un réel besoin physique, il n’y a pas besoin de créer des tablespaces, et de complexifier l’administration.
Un tablespace n’est pas adapté à une séparation logique des objets.
Si vous tenez à distinguer les fichiers de chaque base sans besoin
physique, rappelez-vous que PostgreSQL crée déjà un sous-répertoire par
base de données dans PGDATA/base/
.
PostgreSQL ne connaît pas de notion de tablespace en lecture seule, ni de tablespace transportable entre deux bases ou deux instances.
Il y a quelques pièges à éviter à la définition d’un tablespace :
Pour des raisons de sécurité et de fiabilité, le répertoire choisi
ne doit pas être à la racine d’un point de montage.
(Cela vaut aussi pour les répertoires PGDATA ou
pg_wal
).
Positionnez toujours les données dans un sous-répertoire, voire deux niveaux en-dessous du point de montage.
Par exemple, déclarez votre PGDATA dans
/<point de montage>/<version majeure>/<nom instance>
plutôt que directement dans /<point de montage>
.
Un tablespace ira dans
/<autre point de montage>/<nom répertoire>/
plutôt que directement dans
/<autre point de montage>/
.
(Voir Utilisation de systèmes de fichiers secondaires dans la documentation officielle, ou le bug à l’origine de ce conseil.)
Surtout, le tablespace doit impérativement être placé hors de PGDATA. Certains outils poseraient problème sinon.
Si ce conseil n’est pas suivi, PostgreSQL crée le tablespace mais renvoie un avertissement :
WARNING: tablespace location should not be inside the data directory CREATE TABLESPACE
Il est aussi déconseillé de mettre le numéro de version de PostgreSQL
dans le chemin du tablespace. PostgreSQL le gère à l’intérieur du
tablespace, et en tient notamment compte dans les migrations avec
pg_upgrade
.
Le répertoire du tablespace doit exister et les accès ouverts et restreints à l’utilisateur système sous lequel tourne l’instance (en général postgres sous Linux, Network Service sous Windows) :
# mkdir /SSD/tbl/chaud
# chown postgres:postgres /SSD/tbl/chaud
# chmod 700 /SSD/tbl/chaud
Les ordres SQL plus haut permettent de :
Quelques choses à savoir :
La table ou l’index est totalement verrouillé le temps du déplacement.
Les index existants ne « suivent » pas automatiquement une table déplacée, il faut les déplacer séparément.
Par défaut, les nouveaux index ne sont pas créés
automatiquement dans le même tablespace que la table, mais en fonction
de default_tablespace
.
Les tablespaces des tables sont visibles dans la vue système
pg_tables
, dans \d+
sous psql, et dans
pg_indexes
pour les index :
SELECT schemaname, indexname, tablespace
FROM pg_indexes
WHERE tablename = 'ma_table';
schemaname | indexname | tablespace
------------+--------------+------------
public | matable_idx | chaud public | matable_pkey |
Le paramètre default_tablespace
permet d’utiliser un
autre tablespace que celui par défaut dans PGDATA. En plus du
postgresql.conf
, il peut être défini au niveau rôle, base,
ou le temps d’une session :
ALTER DATABASE critique SET default_tablespace TO 'chaud' ; -- base
ALTER ROLE etl SET default_tablespace TO 'chaud' ; -- rôle
SET default_tablespace TO 'chaud' ; -- session
Les opérations de tri et les tables temporaires peuvent être
déplacées vers un ou plusieurs tablespaces dédiés grâce au paramètre
temp_tablespaces
. Le premier intérêt est de dédier aux tris
une partition rapide (SSD, disque local…). Un autre est de ne plus
risquer de saturer la partition du PGDATA en cas de fichiers temporaires
énormes dans base/pgsql_tmp/
.
Ne jamais utiliser de RAM disque (comme tmpfs
) pour des
tablespaces de tri : la mémoire de la machine ne doit servir qu’aux
applications et outils, au cache de l’OS, et aux tris en RAM. Favorisez
ces derniers en jouant sur work_mem
.
En cas de redémarrage, ce tablespace ne serait d’ailleurs plus utilisable. Un RAM disque est encore plus dangereux pour les tablespaces de données, bien sûr.
Il faudra ouvrir les droits aux utilisateurs ainsi :
GRANT CREATE ON TABLESPACE ssd_tri TO dupont ;
Si plusieurs tablespaces de tri sont paramétrés, chaque transaction en choisira un de façon aléatoire à la création d’un objet temporaire, puis utilisera alternativement chaque tablespace. Un gros tri sera donc étalé sur plusieurs de ces tablespaces. afin de répartir la charge.
Paramètres de performances :
Dans le cas de disques de performances différentes, il faut adapter les paramètres concernés aux caractéristiques du tablespace si la valeur par défaut ne convient pas. Ce sont des paramètres classiques qui ne seront pas décrits en détail ici :
seq_page_cost
(coût d’accès à un bloc pendant un
parcours) ;random_page_cost
(coût d’accès à un bloc isolé) ;effective_io_concurrency
(nombre d’I/O simultanées) et
maintenance_io_concurrency
(idem, pour une opération de
maintenance).Notamment : effective_io_concurrency
a pour but
d’indiquer le nombre d’opérations disques possibles en même temps pour
un client (prefetch). Seuls les parcours Bitmap Scan
sont impactés par ce paramètre. Selon la documentation,
pour un système disque utilisant un RAID matériel, il faut le configurer
en fonction du nombre de disques utiles dans le RAID (n s’il s’agit d’un
RAID 1, n-1 s’il s’agit d’un RAID 5 ou 6, n/2 s’il s’agit d’un RAID 10).
Avec du SSD, il est possible de monter à plusieurs centaines, étant
donné la rapidité de ce type de disque. À l’inverse, il faut tenir
compte du nombre de requêtes simultanées qui utiliseront ce nœud. Le
défaut est seulement de 1, et la valeur maximale est 1000. Attention, à
partir de la version 13, le principe reste le même, mais la valeur
exacte de ce paramètre doit être 2 à 5 fois plus élevée qu’auparavant,
selon la formule des notes de
version.
Toujours en version 13 apparaît
maintenance_io_concurrency
. similaire à
effective_io_concurrency
, mais pour les opérations de
maintenance. Celles‑ci peuvent ainsi se voir accorder plus de ressources
qu’une simple requête. Le défaut est de 10, et il faut penser à le
monter aussi si on adapte effective_io_concurrency
.
Par exemple, sur un système paramétré pour des disques classiques, un tablespace sur un SSD peut porter ces paramètres :
ALTER TABLESPACE chaud SET ( random_page_cost = 1 );
ALTER TABLESPACE chaud SET ( effective_io_concurrency = 500,
= 500 ) ; maintenance_io_concurrency
Le processus postmaster est en écoute sur les différentes sockets déclarées dans la configuration. Cette déclaration se fait au moyen des paramètres suivants :
port
: le port TCP. Il sera aussi utilisé dans le nom
du fichier socket Unix (par exemple : /tmp/.s.PGSQL.5432
ou
/var/run/postgresql/.s.PGSQL.5432
selon les
distributions) ;listen_adresses
: la liste des adresses IP du serveur
auxquelles s’attacher ;unix_socket_directories
: le répertoire où sera stocké
la socket Unix ;unix_socket_group
: le groupe (système) autorisé à
accéder à la socket Unix ;unix_socket_permissions
: les droits d’accès à la
socket Unix.Les connexions par socket Unix ne sont possibles sous Windows qu’à partir de la version 13.
Il faut bien faire la distinction entre session TCP et session de
PostgreSQL. Si une session TCP sert de support à une requête
particulièrement longue, laquelle ne renvoie pas de données pendant
plusieurs minutes, alors le firewall peut considérer la session
inactive, même si le statut du backend dans
pg_stat_activity
est active
.
Il est possible de préciser les propriétés keepalive des
sockets TCP, pour peu que le système d’exploitation les gère. Le
keepalive est un mécanisme de maintien et de vérification des sessions
TCP, par l’envoi régulier de messages de vérification sur une session
TCP inactive. tcp_keepalives_idle
est le temps en secondes
d’inactivité d’une session TCP avant l’envoi d’un message de keepalive.
tcp_keepalives_interval
est le temps entre un keepalive et
le suivant, en cas de non-réponse. tcp_keepalives_count
est
le nombre maximum de paquets sans réponse accepté avant que la session
ne soit déclarée comme morte.
Les valeurs par défaut (0) reviennent à utiliser les valeurs par défaut du système d’exploitation.
Le mécanisme de keepalive a deux intérêts :
Un autre cas peut survenir. Parfois, un client lance une requête.
Cette requête met du temps à s’exécuter et le client quitte la session
avant de récupérer les résultats. Dans ce cas, le serveur continue à
exécuter la requête et ne se rendra compte de l’absence du client qu’au
moment de renvoyer les premiers résultats. Depuis la version 14, il est
possible d’autoriser la vérification de la connexion pendant l’exécution
d’une requête. Il faut pour cela définir la durée d’intervalle entre
deux vérifications avec le paramètre
client_connection_check_interval
. Par défaut, cette option
est désactivée et sa valeur est de 0.
Il existe des options pour activer SSL et le paramétrer.
ssl
vaut on
ou off
,
ssl_ciphers
est la liste des algorithmes de chiffrement
autorisés, et ssl_renegotiation_limit
le volume maximum de
données échangées sur une session avant renégociation entre le client et
le serveur. Le paramétrage SSL impose aussi la présence d’un certificat.
Pour plus de détails, consultez la documentation
officielle.
Les différents processus de PostgreSQL collectent des statistiques d’activité qui ont pour but de mesurer l’activité de la base. Notamment :
Il ne faut pas confondre les statistiques d’activité avec celles sur les données (taille des tables, des enregistrements, fréquences des valeurs…), qui sont à destination de l’optimiseur de requête !
Pour des raisons de performance, ces stastistiques restent en mémoire (ou dans des fichiers temporaires jusque PostgreSQL 14) et ne sont stockées durablement qu’en cas d’arrêt propre. Un arrêt brutal implique donc leur réinitialisation !
Voici les paramètres concernés par cette collecte d’informations.
track_activities
(on
par défaut) précise si
les processus doivent mettre à jour leur activité dans
pg_stat_activity
.
track_counts
(on
par défaut) indique que
les processus doivent collecter des informations sur leur activité. Il
est vital pour le déclenchement de l’autovacuum.
track_activity_query_size
est la taille maximale du
texte de requête pouvant être stocké dans pg_stat_activity
.
1024 caractères est un défaut souvent insuffisant, à monter vers 10 000
si les requêtes sont longues, voire plus ; cette modification nécessite
un redémarrage vu qu’elle touche au dimensionnement de la mémoire
partagée.
Disponible depuis la version 14, compute_query_id
permet
d’activer le calcul de l’identifiant de la requête. Ce dernier sera
visible dans le champ query_id
de la vue
pg_stat_activity
, ainsi que dans les traces.
track_io_timing
(off
par défaut) précise si
les processus doivent collecter des informations de chronométrage sur
les lectures et écritures, pour compléter les champs
blk_read_time
et blk_write_time
des vues
pg_stat_database
et pg_stat_statements
, ainsi
que les plans d’exécutions appelés avec
EXPLAIN (ANALYZE,BUFFERS)
et les traces de l’autovacuum
(pour un VACUUM
comme un ANALYZE
). Avant de
l’activer sur une machine peu performante, vérifiez l’impact avec
l’outil pg_test_timing
(il doit montrer des durées de
chronométrage essentiellement sous la microseconde).
track_functions
indique si les processus doivent aussi
collecter des informations sur l’exécution des routines stockées. Les
valeurs sont none
(par défaut), pl
pour ne
tracer que les routines en langages procéduraux, all
pour
tracer aussi les routines en C et en SQL.
update_process_title
permet de modifier le titre du
processus, visible par exemple avec ps -ef
sous Unix. Il
est à on
par défaut sous Unix, mais il faut le laisser à
off
sous Windows pour des raisons de performance.
Avant la version 15, stats_temp_directory
servait à
indiquer le répertoire de stockage temporaire des statistiques, avant
copie dans pg_stat/
lors d’un arrêt propre. Ce répertoire
peut devenir gros, est réécrit fréquemment, et peut devenir source de
contention. Il est conseillé de le stocker ailleurs que dans le
répertoire de l’instance PostgreSQL, par exemple sur un RAM disque ou
tmpfs
(c’est le défaut sous Debian).
Ce répertoire existe toujours en version 15, notamment si vous
utilisez le module pg_stat_statements
. Cependant, en dehors
de ce module, rien d’autre ne l’utilise. Quant au paramètre
stats_temp_directory
, il a disparu.
PostgreSQL propose de nombreuses vues, accessibles en SQL, pour obtenir des informations sur son fonctionnement interne. Il est possible d’avoir des informations sur le fonctionnement des bases, des processus d’arrière-plan, des tables, les requêtes en cours…
Pour les statistiques aux objets, le système fournit à chaque fois trois vues différentes :
pg_statio_all_tables
par exemple ;pg_statio_sys_tables
par exemple ;pg_statio_user_tables
par
exemple.Les accès logiques aux objets (tables, index et routines) figurent
dans les vues pg_stat_xxx_tables
,
pg_stat_xxx_indexes
et
pg_stat_user_functions
.
Les accès physiques aux objets sont visibles dans les vues
pg_statio_xxx_indexes
, pg_statio_xxx_tables
et
pg_statio_xxx_sequences
. Une vision plus globale est
disponible dans pg_stat_io
(apparue avec
PostgreSQL 16).
Des statistiques globales par base sont aussi disponibles, dans
pg_stat_database
: le nombre de transactions validées et
annulées, quelques statistiques sur les sessions, et quelques
statistiques sur les accès physiques et en cache, ainsi que sur les
opérations logiques.
pg_stat_bgwriter
stocke les statistiques d’écriture des
buffers des Background Writer, Checkpointer et des sessions
elles-mêmes.
pg_stat_activity
est une des vues les plus utilisées et
est souvent le point de départ d’une recherche : elle donne des
informations sur les processus en cours sur l’instance, que ce soit des
processus en tâche de fond ou des processus backends associés aux
clients : numéro de processus, adresse et port, date de début d’ordre,
de transaction, de session, requête en cours, état, ordre SQL et nom de
l’application si elle l’a renseigné. (Noter qu’avant la version 10,
cette vue n’affichait que les processus backend ; à partir de la version
10 apparaissent des workers, le checkpointer, le walwriter… ; à partir
de la version 14 apparaît le processus d’archivage).
=# SELECT datname, pid, usename, application_name, backend_start, state, backend_type, query
FROM pg_stat_activity \gx
-[ RECORD 1 ]----+-------------------------------------------------------------
datname | ¤
pid | 26378
usename | ¤
application_name |
backend_start | 2019-10-24 18:25:28.236776+02
state | ¤
backend_type | autovacuum launcher
query |
-[ RECORD 2 ]----+-------------------------------------------------------------
datname | ¤
pid | 26380
usename | postgres
application_name |
backend_start | 2019-10-24 18:25:28.238157+02
state | ¤
backend_type | logical replication launcher
query |
-[ RECORD 3 ]----+-------------------------------------------------------------
datname | pgbench
pid | 22324
usename | test_performance
application_name | pgbench
backend_start | 2019-10-28 10:26:51.167611+01
state | active
backend_type | client backend
query | UPDATE pgbench_accounts SET abalance = abalance + -3810 WHERE…
-[ RECORD 4 ]----+-------------------------------------------------------------
datname | postgres
pid | 22429
usename | postgres
application_name | psql
backend_start | 2019-10-28 10:27:09.599426+01
state | active
backend_type | client backend
query | select datname, pid, usename, application_name, backend_start…
-[ RECORD 5 ]----+-------------------------------------------------------------
datname | pgbench
pid | 22325
usename | test_performance
application_name | pgbench
backend_start | 2019-10-28 10:26:51.172585+01
state | active
backend_type | client backend
query | UPDATE pgbench_accounts SET abalance = abalance + 4360 WHERE…
-[ RECORD 6 ]----+-------------------------------------------------------------
datname | pgbench
pid | 22326
usename | test_performance
application_name | pgbench
backend_start | 2019-10-28 10:26:51.178514+01
state | active
backend_type | client backend
query | UPDATE pgbench_accounts SET abalance = abalance + 2865 WHERE…
-[ RECORD 7 ]----+-------------------------------------------------------------
datname | ¤
pid | 26376
usename | ¤
application_name |
backend_start | 2019-10-24 18:25:28.235574+02
state | ¤
backend_type | background writer
query |
-[ RECORD 8 ]----+-------------------------------------------------------------
datname | ¤
pid | 26375
usename | ¤
application_name |
backend_start | 2019-10-24 18:25:28.235064+02
state | ¤
backend_type | checkpointer
query |
-[ RECORD 9 ]----+-------------------------------------------------------------
datname | ¤
pid | 26377
usename | ¤
application_name |
backend_start | 2019-10-24 18:25:28.236239+02
state | ¤
backend_type | walwriter query |
Cette vue fournit aussi des informations sur ce que chaque session
attend. Pour les détails sur wait_event_type
(type
d’événement en attente) et wait_event
(nom de l’événement
en attente), voir le tableau des événements
d’attente.
SELECT datname, pid, wait_event_type, wait_event, query FROM pg_stat_activity
# WHERE backend_type='client backend' AND wait_event IS NOT NULL \gx
-[ RECORD 1 ]---+--------------------------------------------------------------
datname | pgbench
pid | 1590
state | idle in transaction
wait_event_type | Client
wait_event | ClientRead
query | UPDATE pgbench_accounts SET abalance = abalance + 1438 WHERE…
-[ RECORD 2 ]---+--------------------------------------------------------------
datname | pgbench
pid | 1591
state | idle
wait_event_type | Client
wait_event | ClientRead
query | END;
-[ RECORD 3 ]---+--------------------------------------------------------------
datname | pgbench
pid | 1593
state | idle in transaction
wait_event_type | Client
wait_event | ClientRead
query | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES…
-[ RECORD 4 ]---+--------------------------------------------------------------
datname | postgres
pid | 1018
state | idle in transaction
wait_event_type | Client
wait_event | ClientRead
query | delete from t1 ;
-[ RECORD 5 ]---+--------------------------------------------------------------
datname | postgres
pid | 1457
state | active
wait_event_type | Lock
wait_event | transactionid query | delete from t1 ;
Des vues plus spécialisées existent :
pg_stat_replication
donne des informations sur les
serveurs secondaires connectés. Les statistiques sur les conflits entre
application de la réplication et requêtes en lecture seule sont
disponibles dans pg_stat_database_conflicts
.
pg_stat_ssl
donne des informations sur les connexions
SSL : version SSL, suite de chiffrement, nombre de bits pour
l’algorithme de chiffrement, compression, Distinguished Name (DN) du
certificat client.
pg_locks
permet de voir les verrous posés sur les objets
(principalement les relations comme les tables et les index).
pg_stat_progress_vacuum
,
pg_stat_progress_analyze
,
pg_stat_progress_create_index
,
pg_stat_progress_cluster
,
pg_stat_progress_basebackup
et
pg_stat_progress_copy
donnent respectivement des
informations sur la progression des VACUUM
, des
ANALYZE
, des créations d’index, des commandes de
VACUUM FULL
et CLUSTER
, de la commande de
réplication BASE BACKUP
et des COPY
.
pg_stat_archiver
donne des informations sur l’archivage
des wals et notamment sur les erreurs d’archivage.
Afin de calculer les plans d’exécution des requêtes au mieux, le
moteur a besoin de statistiques sur les données qu’il va interroger. Il
est très important pour lui de pouvoir estimer la sélectivité d’une
clause WHERE
, l’augmentation ou la diminution du nombre
d’enregistrements entraînée par une jointure, tout cela afin de
déterminer le coût approximatif d’une requête, et donc de choisir un bon
plan d’exécution.
Il ne faut pas les confondre avec les statistiques d’activité, vues précédemment !
Les statistiques sont collectées dans la table
pg_statistic
. La vue pg_stats
affiche le
contenu de cette table système de façon plus accessible.
Les statistiques sont collectées sur :
Le recueil des statistiques s’effectue quand on lance un ordre
ANALYZE
sur une table, ou que l’autovacuum le lance de son
propre chef.
Les statistiques sont calculées sur un échantillon égal à 300 fois le
paramètre STATISTICS
de la colonne (ou, s’il n’est pas
précisé, du paramètre default_statistics_target
, 100 par
défaut).
La vue pg_stats
affiche les statistiques
collectées :
\d pg_stats
View "pg_catalog.pg_stats"
Column | Type | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
schemaname | name | | |
tablename | name | | |
attname | name | | |
inherited | boolean | | |
null_frac | real | | |
avg_width | integer | | |
n_distinct | real | | |
most_common_vals | anyarray | | |
most_common_freqs | real[] | | |
histogram_bounds | anyarray | | |
correlation | real | | |
most_common_elems | anyarray | | |
most_common_elem_freqs | real[] | | | elem_count_histogram | real[] | | |
inherited
: la statistique concerne-t-elle un objet
utilisant l’héritage (table parente, dont héritent plusieurs tables)
;null_frac
: fraction d’enregistrements dont la colonne
vaut NULL ;avg_width
: taille moyenne de cet attribut dans
l’échantillon collecté ;n_distinct
: si positif, nombre de valeurs distinctes,
si négatif, fraction de valeurs distinctes pour cette colonne dans la
table. Il est possible de forcer le nombre de valeurs distinctes, s’il
est constaté que la collecte des statistiques n’y arrive pas :
ALTER TABLE xxx ALTER COLUMN yyy SET (n_distinct = -0.5) ; ANALYZE xxx;
par exemple indique à l’optimiseur que chaque valeur apparaît
statistiquement deux fois ;most_common_vals
et most_common_freqs
:
les valeurs les plus fréquentes de la table, et leur fréquence. Le
nombre de valeurs collectées est au maximum celui indiqué par le
paramètre STATISTICS
de la colonne, ou à défaut par
default_statistics_target
. Le défaut de 100 échantillons
sur 30 000 lignes peut être modifié par
ALTER TABLE matable ALTER COLUMN macolonne SET statistics 300 ;
(avec une évolution proportionnelle du nombre de lignes consultées)
sachant que le temps de planification augmente exponentiellement et
qu’il vaut mieux ne pas dépasser la valeur 1000 ;histogram_bounds
: les limites d’histogramme sur la
colonne. Les histogrammes permettent d’évaluer la sélectivité d’un
filtre par rapport à sa valeur précise. Ils permettent par exemple à
l’optimiseur de déterminer que 4,3 % des enregistrements d’une colonne
noms
commencent par un A, ou 0,2 % par AL. Le principe est
de regrouper les enregistrements triés dans des groupes de tailles
approximativement identiques, et de stocker les limites de ces groupes
(on ignore les most_common_vals
, pour lesquelles il y a
déjà une mesure plus précise). Le nombre d’histogram_bounds
est calculé de la même façon que les most_common_vals
;correlation
: le facteur de corrélation statistique
entre l’ordre physique et l’ordre logique des enregistrements de la
colonne. Il vaudra par exemple 1
si les enregistrements
sont physiquement stockés dans l’ordre croissant, -1
si ils
sont dans l’ordre décroissant, ou 0
si ils sont totalement
aléatoirement répartis. Ceci sert à affiner le coût d’accès aux
enregistrements ;most_common_elems
et
most_common_elems_freqs
: les valeurs les plus fréquentes
si la colonne est un tableau (NULL dans les autres cas), et leur
fréquence. Le nombre de valeurs collectées est au maximum celui indiqué
par le paramètre STATISTICS
de la colonne, ou à défaut par
default_statistics_target
;elem_count_histogram
: les limites d’histogramme sur la
colonne si elle est de type tableau.Parfois, il est intéressant de calculer des statistiques sur un
ensemble de colonnes ou d’expressions. Dans ce cas, il faut créer un
objet statistique en indiquant les colonnes et/ou expressions à traiter
et le type de statistiques à calculer (voir la documentation de
CREATE STATISTICS
).
Le langage SQL décrit le résultat souhaité. Par exemple :
SELECT path, filename
FROM file
JOIN path ON (file.pathid=path.pathid)
WHERE path LIKE '/usr/%'
Cet ordre décrit le résultat souhaité. Nous ne précisons pas au
moteur comment accéder aux tables path
et file
(par index ou parcours complet par exemple), ni comment effectuer la
jointure (PostgreSQL dispose de plusieurs méthodes). C’est à
l’optimiseur de prendre la décision, en fonction des informations qu’il
possède.
Les informations les plus importantes pour lui, dans le contexte de cette requête, seront :
path
est ramenée par le
critère path LIKE '/usr/%
’ ?file.pathid
, sur
path.pathid
?La stratégie la plus efficace ne sera donc pas la même suivant les informations retournées par toutes ces questions.
Par exemple, il pourrait être intéressant de charger les deux tables
séquentiellement, supprimer les enregistrements de path
ne
correspondant pas à la clause LIKE
, trier les deux jeux
d’enregistrements et fusionner les deux jeux de données triés (cette
technique est un merge join). Cependant, si les tables sont
assez volumineuses, et que le LIKE
est très discriminant
(il ramène peu d’enregistrements de la table path
), la
stratégie d’accès sera totalement différente : nous pourrions préférer
récupérer les quelques enregistrements de path
correspondant au LIKE
par un index, puis pour chacun de ces
enregistrements, aller chercher les informations correspondantes dans la
table file
(c’est un nested loop).
Afin de choisir un bon plan, le moteur essaie des plans d’exécution. Il estime, pour chacun de ces plans, le coût associé. Afin d’évaluer correctement ces coûts, il utilise plusieurs informations :
seq_page_cost
(1 par défaut) : coût de la lecture d’une
page disque de façon séquentielle (au sein d’un parcours séquentiel de
table par exemple) ;random_page_cost
(4 par défaut) : coût de la lecture
d’une page disque de façon aléatoire (lors d’un accès à une page d’index
par exemple) ;cpu_tuple_cost
(0,01 par défaut) : coût de traitement
par le processeur d’un enregistrement de table ;cpu_index_tuple_cost
(0,005 par défaut) : coût de
traitement par le processeur d’un enregistrement d’index ;cpu_operator_cost
(0,0025 par défaut) : coût de
traitement par le processeur de l’exécution d’un opérateur.Ce sont les coûts relatifs de ces différentes opérations qui sont
importants : l’accès à une page de façon aléatoire est par défaut 4 fois
plus coûteux que de façon séquentielle, du fait du déplacement des têtes
de lecture sur un disque dur. Ceci prend déjà en considération un
potentiel effet du cache. Sur une base fortement en cache, il est donc
possible d’être tenté d’abaisser le random_page_cost
à 3,
voire 2,5, ou des valeurs encore bien moindres dans le cas de bases
totalement en mémoire.
Le cas des disques SSD est particulièrement intéressant. Ces derniers
n’ont pas à proprement parler de tête de lecture. De ce fait, comme les
paramètres seq_page_cost
et random_page_cost
sont principalement là pour différencier un accès direct et un accès
après déplacement de la tête de lecture, la différence de configuration
entre ces deux paramètres n’a pas lieu d’être si les index sont placés
sur des disques SSD. Dans ce cas, une configuration très basse et
pratiquement identique (voire identique) de ces deux paramètres est
intéressante.
effective_io_concurrency
a pour but d’indiquer le nombre
d’opérations disques possibles en même temps pour un client
(prefetch). Seuls les parcours Bitmap Scan sont
impactés par ce paramètre. Selon la documentation,
pour un système disque utilisant un RAID matériel, il faut le configurer
en fonction du nombre de disques utiles dans le RAID (n s’il s’agit d’un
RAID 1, n-1 s’il s’agit d’un RAID 5 ou 6, n/2 s’il s’agit d’un RAID 10).
Avec du SSD, il est possible de monter à plusieurs centaines, étant
donné la rapidité de ce type de disque. À l’inverse, il faut tenir
compte du nombre de requêtes simultanées qui utiliseront ce nœud. Le
défaut est seulement de 1, et la valeur maximale est 1000. Attention, à
partir de la version 13, le principe reste le même, mais la valeur
exacte de ce paramètre doit être 2 à 5 fois plus élevée qu’auparavant,
selon la formule des notes de
version.
Toujours à partir de la version 13, un nouveau paramètre apparaît :
maintenance_io_concurrency
. Il a le même sens que
effective_io_concurrency
, mais pour les opérations de
maintenance, non les requêtes. Celles-ci peuvent ainsi se voir accorder
plus de ressources qu’une simple requête. Le défaut est de 10, et il
faut penser à le monter aussi si nous adaptons
effective_io_concurrency
.
seq_page_cost
, random_page_cost
,
effective_io_concurrency
et
maintenance_io_concurrency
peuvent être paramétrés par
tablespace, afin de refléter les caractéristiques de disques
différents.
La mise en place du parallélisme dans une requête représente un
coût : il faut mettre en place une mémoire partagée, lancer des
processus… Ce coût est pris en compte par le planificateur à l’aide du
paramètre parallel_setup_cost
. Par ailleurs, le transfert
d’enregistrement entre un worker et le processus principal a également
un coût représenté par le paramètre
parallel_tuple_cost
.
Ainsi une lecture complète d’une grosse table peut être moins coûteuse sans parallélisation du fait que le nombre de lignes retournées par les workers est très important. En revanche, en filtrant les résultats, le nombre de lignes retournées peut être moins important, la répartition du filtrage entre différents processeurs devient « rentable » et le planificateur peut être amené à choisir un plan comprenant la parallélisation.
Certaines autres informations permettent de nuancer les valeurs
précédentes. effective_cache_size
est la taille totale du
cache. Il permet à PostgreSQL de modéliser plus finement le coût réel
d’une opération disque, en prenant en compte la probabilité que cette
information se trouve dans le cache du système d’exploitation ou dans
celui de l’instance, et soit donc moins coûteuse à accéder.
Le parcours de l’espace des solutions est un parcours exhaustif. Sa complexité est principalement liée au nombre de jointures de la requête et est de type exponentiel. Par exemple, planifier de façon exhaustive une requête à une jointure dure 200 microsecondes environ, contre 7 secondes pour 12 jointures. Une autre stratégie, l’optimiseur génétique, est donc utilisée pour éviter le parcours exhaustif quand le nombre de jointure devient trop élevé.
Pour plus de détails, voir l’article sur les coûts de planification issu de notre base de connaissance.
Tous les paramètres suivants peuvent être modifiés par session.
Avant la version 10, PostgreSQL ne connaissait qu’un partitionnement
par héritage, où l’on crée une table parente et des tables filles
héritent de celle-ci, possédant des contraintes CHECK
comme
critères de partitionnement, par exemple
CHECK (date >='2011-01-01' and date < '2011-02-01')
pour une table fille d’un partitionnement par mois.
Afin que PostgreSQL ne parcourt que les partitions correspondant à la
clause WHERE
d’une requête, le paramètre
constraint_exclusion
doit valoir partition
(la
valeur par défaut) ou on
. partition
est moins
coûteux dans un contexte d’utilisation classique car les contraintes
d’exclusion ne seront examinées que dans le cas de requêtes
UNION ALL
, qui sont les requêtes générées par le
partitionnement.
Pour le nouveau partitionnement déclaratif,
enable_partition_pruning
, activé par défaut, est le
paramètre équivalent.
Pour limiter la complexité des plans d’exécution à étudier, il est
possible de limiter la quantité de réécriture autorisée par l’optimiseur
via les paramètres from_collapse_limit
et
join_collapse_limit
. Le premier interdit que plus de 8 (par
défaut) tables provenant d’une sous-requête ne soient déplacées dans la
requête principale. Le second interdit que plus de 8 (par défaut) tables
provenant de clauses JOIN
ne soient déplacées vers la
clause FROM
. Ceci réduit la qualité du plan d’exécution
généré, mais permet qu’il soit généré dans un temps raisonnable. Il est
fréquent de monter les valeurs à 10 ou un peu au-delà si de longues
requêtes impliquent beaucoup de tables.
Pour les requêtes préparées, l’optimiseur génère des plans
personnalisés pour les cinq premières exécutions d’une requête préparée,
puis il bascule sur un plan générique dès que celui-ci devient plus
intéressant que la moyenne des plans personnalisés précédents. Ceci
décrit le mode auto
en place depuis de nombreuses versions.
Depuis la version 12, il est possible de modifier ce comportement grâce
au paramètre de configuration plan_cache_mode
:
force_custom_plan
force le recalcul systématique d’un
plan personnalisé pour la requête (on n’économise plus le temps de
planification, mais le plan est calculé pour être optimal pour les
paramètres, et l’on conserve la protection contre les injections SQL
permise par les requêtes préparées) ;force_generic_plan
force l’utilisation d’un seul et
même plan dès le départ.Lors de l’utilisation de curseurs, le moteur n’a aucun moyen de
connaître le nombre d’enregistrements que souhaite récupérer réellement
l’utilisateur : peut-être seulement les premiers enregistrements. Si
c’est le cas, le plan d’exécution optimal ne sera plus le même. Le
paramètre cursor_tuple_fraction
, par défaut à 0,1, permet
d’indiquer à l’optimiseur la fraction du nombre d’enregistrements qu’un
curseur souhaitera vraisemblablement récupérer, et lui permettra donc de
choisir un plan en conséquence. Si vous utilisez des curseurs, il vaut
mieux indiquer explicitement le nombre d’enregistrements dans les
requêtes avec LIMIT
, et passer
cursor_tuple_fraction
à 1,0.
Quand plusieurs requêtes souhaitent accéder séquentiellement à la
même table, les processus se rattachent à ceux déjà en cours de
parcours, afin de profiter des entrées-sorties que ces processus
effectuent, le but étant que le système se comporte comme si un seul
parcours de la table était en cours, et réduise donc fortement la charge
disque. Le seul problème de ce mécanisme est que les processus se
rattachant ne parcourent pas la table dans son ordre physique : elles
commencent leur parcours de la table à l’endroit où se trouve le
processus auquel elles se rattachent, puis rebouclent sur le début de la
table. Les résultats n’arrivent donc pas forcément toujours dans le même
ordre, ce qui n’est normalement pas un problème (on est censé utiliser
ORDER BY
dans ce cas). Mais il est toujours possible de
désactiver ce mécanisme en passant synchronize_seqscans
à
off
.
PostgreSQL, pour les requêtes trop complexes, bascule vers un optimiseur appelé GEQO (GEnetic Query Optimizer). Comme tout algorithme génétique, il fonctionne par introduction de mutations aléatoires sur un état initial donné. Il permet de planifier rapidement une requête complexe, et de fournir un plan d’exécution acceptable.
Le code source de PostgreSQL décrit le principe, résumé aussi dans ce schéma :
Ce mécanisme est configuré par des paramètres dont le nom commence par « geqo ». Exceptés ceux évoqués ci-dessous, il est déconseillé de modifier les paramètres sans une bonne connaissance des algorithmes génétiques.
geqo
, par défaut à on
, permet
d’activer/désactiver GEQO ;geqo_threshold
, par défaut à 12, est le nombre
d’éléments minimum à joindre dans un FROM
avant d’optimiser
celui-ci par GEQO au lieu du planificateur exhaustif.Malgré l’introduction de ces mutations aléatoires, le moteur arrive
tout de même à conserver un fonctionnement déterministe. Tant que le
paramètre geqo_seed
ainsi que les autres paramètres
contrôlant GEQO restent inchangés, le plan obtenu pour une requête
donnée restera inchangé. Il est donc possible de faire varier la valeur
de geqo_seed
pour chercher d’autres plans (voir
la documentation officielle).
Ces paramètres dissuadent le moteur d’utiliser un type de nœud d’exécution (en augmentant énormément son coût). Ils permettent de vérifier ou d’invalider une erreur de l’optimiseur. Par exemple :
-- création de la table de test
CREATE TABLE test2(a integer, b integer);
-- insertion des données de tests
INSERT INTO test2 SELECT 1, i FROM generate_series(1, 500000) i;
-- analyse des données
ANALYZE test2;
-- désactivation de la parallélisation (pour faciliter la lecture du plan)
SET max_parallel_workers_per_gather TO 0;
-- récupération du plan d'exécution
EXPLAIN ANALYZE SELECT * FROM test2 WHERE a<3;
QUERY PLAN
------------------------------------------------------------
Seq Scan on test2 (cost=0.00..8463.00 rows=500000 width=8)
(actual time=0.031..63.194 rows=500000 loops=1)
Filter: (a < 3)
Planning Time: 0.411 ms Execution Time: 86.824 ms
Le moteur a choisi un parcours séquentiel de table. Si l’on veut
vérifier qu’un parcours par l’index sur la colonne a
n’est
pas plus rentable :
-- désactivation des parcours SeqScan, IndexOnlyScan et BitmapScan
SET enable_seqscan TO off;
SET enable_indexonlyscan TO off;
SET enable_bitmapscan TO off;
-- création de l'index
CREATE INDEX ON test2(a);
-- récupération du plan d'exécution
EXPLAIN ANALYZE SELECT * FROM test2 WHERE a<3;
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using test2_a_idx on test2 (cost=0.42..16462.42 rows=500000 width=8)
(actual time=0.183..90.926 rows=500000 loops=1)
Index Cond: (a < 3)
Planning Time: 0.517 ms Execution Time: 111.609 ms
Non seulement le plan est plus coûteux, mais il est aussi (et surtout) plus lent.
Attention aux effets du cache : le parcours par index est ici relativement performant à la deuxième exécution parce que les données ont été trouvées dans le cache disque. La requête, sinon, aurait été bien plus lente. La requête initiale est donc non seulement plus rapide, mais aussi plus s