Extensions PostgreSQL pour la performance

17 avril 2024

Dalibo SCOP

Sur ce document

Formation Module X2
Titre Extensions PostgreSQL pour la performance
Révision 24.04
PDF https://dali.bo/x2_pdf
EPUB https://dali.bo/x2_epub
HTML https://dali.bo/x2_html
Slides https://dali.bo/x2_slides
TP https://dali.bo/x2_tp
TP (solutions) https://dali.bo/x2_solutions

Vous trouverez en ligne les différentes versions complètes de ce document.


Chers lectrices & lecteurs,

Nos formations PostgreSQL sont issues de nombreuses années d’études, d’expérience de terrain et de passion pour les logiciels libres. Pour Dalibo, l’utilisation de PostgreSQL n’est pas une marque d’opportunisme commercial, mais l’expression d’un engagement de longue date. Le choix de l’Open Source est aussi le choix de l’implication dans la communauté du logiciel.

Au‑delà du contenu technique en lui‑même, notre intention est de transmettre les valeurs qui animent et unissent les développeurs de PostgreSQL depuis toujours : partage, ouverture, transparence, créativité, dynamisme… Le but premier de nos formations est de vous aider à mieux exploiter toute la puissance de PostgreSQL mais nous espérons également qu’elles vous inciteront à devenir un membre actif de la communauté en partageant à votre tour le savoir‑faire que vous aurez acquis avec nous.

Nous mettons un point d’honneur à maintenir nos manuels à jour, avec des informations précises et des exemples détaillés. Toutefois malgré nos efforts et nos multiples relectures, il est probable que ce document contienne des oublis, des coquilles, des imprécisions ou des erreurs. Si vous constatez un souci, n’hésitez pas à le signaler via l’adresse !

À propos de DALIBO

DALIBO est le spécialiste français de PostgreSQL. Nous proposons du support, de la formation et du conseil depuis 2005.

Retrouvez toutes nos formations sur https://dalibo.com/formations

Remerciements

Ce manuel de formation est une aventure collective qui se transmet au sein de notre société depuis des années. Nous remercions chaleureusement ici toutes les personnes qui ont contribué directement ou indirectement à cet ouvrage, notamment :

Jean‑Paul Argudo, Alexandre Anriot, Carole Arnaud, Alexandre Baron, David Bidoc, Sharon Bonan, Franck Boudehen, Arnaud Bruniquel, Pierrick Chovelon, Damien Clochard, Christophe Courtois, Marc Cousin, Gilles Darold, Jehan‑Guillaume de Rorthais, Ronan Dunklau, Vik Fearing, Stefan Fercot, Pierre Giraud, Nicolas Gollet, Dimitri Fontaine, Florent Jardin, Virginie Jourdan, Luc Lamarle, Denis Laxalde, Guillaume Lelarge, Alain Lesage, Benoit Lobréau, Jean‑Louis Louër, Thibaut Madelaine, Adrien Nayrat, Alexandre Pereira, Flavie Perette, Robin Portigliatti, Thomas Reiss, Maël Rimbault, Julien Rouhaud, Stéphane Schildknecht, Julien Tachoires, Nicolas Thauvin, Be Hai Tran, Christophe Truffier, Cédric Villemain, Thibaud Walkowiak, Frédéric Yhuel.

Forme de ce manuel

Les versions PDF, EPUB ou HTML de ce document sont structurées autour des slides de nos formations. Le texte suivant chaque slide contient le cours et de nombreux détails qui ne peuvent être données à l’oral.

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

  • Paternité
  • Pas d’utilisation commerciale
  • Partage des conditions initiales à l’identique

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.

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

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.

Extensions PostgreSQL pour la performance

PostgreSQL

Préambule

Ce module présente des extensions plus spécifiquement destinées à améliorer les performances.


pg_trgm

  • Indexation des recherches LIKE '%critère%'

  • Similarité basée sur des trigrammes

CREATE EXTENSION pg_trgm;
SELECT similarity('bonjour','bnojour');

 similarity
------------
   0.333333
  • Indexation (GIN ou GiST) :
CREATE INDEX test_trgm_idx ON test_trgm
  USING gist (text_data gist_trgm_ops);

Ce module permet de décomposer en trigramme les chaînes qui lui sont proposées :

 SELECT show_trgm('hello');
             show_trgm
 ---------------------------------
  {"  h"," he",ell,hel,llo,"lo "}

Une fois les trigrammes indexés, on peut réaliser de la recherche floue, ou utiliser des clauses LIKE malgré la présence de jokers (%) n’importe où dans la chaîne. À l’inverse, les indexations simples, de type B-tree, ne permettent des recherches efficaces que dans un cas particulier : si le seul joker de la chaîne est à la fin de celle ci (LIKE 'hello%' par exemple). Contrairement à la Full Text Search, la recherche par trigrammes ne réclame aucune modification des requêtes.

CREATE EXTENSION pg_trgm;

CREATE TABLE test_trgm (text_data text);

INSERT INTO test_trgm(text_data)
VALUES ('hello'), ('hello everybody'),
('helo young man'),('hallo!'),('HELLO !');
INSERT INTO test_trgm SELECT 'hola' FROM generate_series(1,1000);

CREATE INDEX test_trgm_idx ON test_trgm
  USING gist (text_data gist_trgm_ops);

SELECT text_data FROM test_trgm
 WHERE text_data like '%hello%';
    text_data
-----------------
 hello
 hello everybody

Cette dernière requête passe par l’index test_trgm_idx, malgré le % initial :

EXPLAIN (ANALYZE)
SELECT text_data FROM test_trgm
WHERE  text_data like '%hello%' ;
                                 QUERY PLAN
----------------------------------------------------------------------------
Index Scan using test_trgm_gist_idx on test_trgm
   (cost=0.41..0.63 rows=1 width=8) (actual time=0.174..0.204 rows=2 loops=1)
   Index Cond: (text_data ~~ '%hello%'::text)
   Rows Removed by Index Recheck: 1
 Planning time: 0.202 ms
 Execution time: 0.250 ms

On peut aussi utiliser un index GIN (comme pour le Full Text Search). Les index GIN ont l’avantage d’être plus efficaces pour les recherches exhaustives. Mais l’indexation pour la recherche des k éléments les plus proches (on parle de recherche k-NN) n’est disponible qu’avec les index GiST .

SELECT text_data, text_data <-> 'hello'
 FROM test_trgm
 ORDER BY text_data <-> 'hello'
 LIMIT 4;

nous retourne par exemple les deux enregistrements les plus proches de « hello » dans la table test_trgm.


pg_stat_statements

Capture en temps réel des requêtes

  • Normalisation
  • Indicateurs :
    • nombre d’exécutions,
    • nombre d’enregistrements retournés
    • temps cumulé d’exécution et d’optimisation
    • lectures/écritures en cache, demandées au système, tris
    • temps de lecture/écriture (track_io_timing)
    • écritures dans les journaux de transactions (v13)
    • temps de planning (désactivé par défaut, v13)
    • utilisation du JIT (v15)

Cette extension est fournie avec PostgreSQL et est parmi les plus populaires et les plus utiles.

Une fois installé, pg_stat_statements capture, à chaque exécution de requête, tous les compteurs ci-dessus et d’autres associés à cette requête (champ query), ci-dessous avec PostgreSQL 16 :

postgres=# \d pg_stat_statements
                          Vue « public.pg_stat_statements »
        Colonne         |       Type       | Collationnement | NULL-able | …
------------------------+------------------+-----------------+-----------+--
 userid                 | oid              |          |    | 
 dbid                   | oid              |          |    | 
 toplevel               | boolean          |          |    | 
 queryid                | bigint           |          |    | 
 query                  | text             |          |    | 
 plans                  | bigint           |          |    | 
 total_plan_time        | double precision |          |    | 
 min_plan_time          | double precision |          |    | 
 max_plan_time          | double precision |          |    | 
 mean_plan_time         | double precision |          |    | 
 stddev_plan_time       | double precision |          |    | 
 calls                  | bigint           |          |    | 
 total_exec_time        | double precision |          |    | 
 min_exec_time          | double precision |          |    | 
 max_exec_time          | double precision |          |    | 
 mean_exec_time         | double precision |          |    | 
 stddev_exec_time       | double precision |          |    | 
 rows                   | bigint           |          |    | 
 shared_blks_hit        | bigint           |          |    | 
 shared_blks_read       | bigint           |          |    | 
 shared_blks_dirtied    | bigint           |          |    | 
 shared_blks_written    | bigint           |          |    | 
 local_blks_hit         | bigint           |          |    | 
 local_blks_read        | bigint           |          |    | 
 local_blks_dirtied     | bigint           |          |    | 
 local_blks_written     | bigint           |          |    | 
 temp_blks_read         | bigint           |          |    | 
 temp_blks_written      | bigint           |          |    | 
 blk_read_time          | double precision |          |    | 
 blk_write_time         | double precision |          |    | 
 temp_blk_read_time     | double precision |          |    | 
 temp_blk_write_time    | double precision |          |    | 
 wal_records            | bigint           |          |    | 
 wal_fpi                | bigint           |          |    | 
 wal_bytes              | numeric          |          |    | 
 jit_functions          | bigint           |          |    | 
 jit_generation_time    | double precision |          |    | 
 jit_inlining_count     | bigint           |          |    | 
 jit_inlining_time      | double precision |          |    | 
 jit_optimization_count | bigint           |          |    | 
 jit_optimization_time  | double precision |          |    | 
 jit_emission_count     | bigint           |          |    | 
 jit_emission_time      | double precision |          |    | 

Quelques champs peuvent manquer ou porter un autre nom dans les versions précédentes.

Les requêtes d’une même base et d’un même utilisateur sont normalisées (reconnues comme identiques même avec des paramètres différents).

Les champs sont détaillés dans https://dali.bo/h2_html#pg_stat_statements.


pg_stat_statements : mise en place

shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION pg_stat_statements ; -- dans 1 ou plusieurs bases
SELECT * FROM pg_stat_statements ;
  • Vue en mémoire partagée (volumétrie contrôlée)
  • Pas d’échantillonnage, seulement des compteurs cumulés
    • pg_stat_statements_reset() ou PoWA

Installation et réinitialisation

Ce module nécessite un espace en mémoire partagée. Pour l’installer, il faut donc renseigner le paramètre suivant avant de redémarrer l’instance :

shared_preload_libraries = 'pg_stat_statements'

Il faut installer l’extension dans au moins une base (dont une à laquelle les développeurs auront aussi accès, car l’information les concerne au premier chef) :

CREATE EXTENSION IF NOT EXISTS pg_stat_statements ;

La vue pg_stat_statements retourne un instantané des compteurs au moment de l’interrogation depuis l’installation, depuis le dernier arrêt brutal, ou depuis le dernier appel à la fonction pg_stat_statements_reset(). Cette dernière fonction permet de réinitialiser les compteurs pour une base, un utilisateur, une requête, ou tout.

Deux méthodes d’utilisation sont donc possibles :

  • effectuer un reset au début d’une période, puis interroger la vue pg_stat_statements à la fin de cette période ;
  • capturer à intervalles réguliers le contenu de pg_stat_statements et visualiser les changements dans les compteurs : le projet PoWA a été développé à cet effet.

La requête étant déjà analysée, cette opération supplémentaire n’ajoute qu’un faible surcoût (de l’ordre de 5 % sur une requête extrêmement courte), fixe, pour chaque requête.

Les données de l’extension sont stockées dans le PGDATA, sous pg_stat_tmp (même pour les versions récentes de PostgreSQL qui ne l’utilisent plus pour le stats collector), et un arrêt brutal peut mener à la perte du contenu.

Paramétrage

pg_stat_statements possède quelques paramètres.

Dès lors que l’extension est chargée en mémoire, la capture des compteurs est enclenchée, sauf si le paramètre pg_stat_statements.track est positionné à none. Celui-ci permet donc d’activer cette capture à la demande, sans qu’il soit nécessaire de redémarrer l’instance, ce qui peut s’avérer utile pour une instance avec beaucoup de requêtes très courtes (de type OLTP), et dont la rapidité est un élément critique : pour une telle instance, le surcoût lié à pg_stat_statements peut être jugé trop important pour que cette capture soit activée en permanence.

Sur un serveur chargé, il est déconseillé de réduire pg_stat_statements.max (nombre de requêtes différentes suivies, à 5000 par défaut), car le coût d’une désallocation n’est pas négligeable.


pg_stat_statements : exemple 1

Requêtes les plus longues en temps cumulé :

SELECT r.rolname, d.datname, s.calls, s.total_exec_time,
       s.total_exec_time / s.calls AS avg_time, s.query
  FROM pg_stat_statements s
  JOIN pg_roles r     ON (s.userid=r.oid)
  JOIN pg_database d  ON (s.dbid = d.oid)
 ORDER BY s.total_exec_time DESC
 LIMIT 10 ;

La requête ci-dessus affiche les dix requêtes les plus longues en cumulé (même avec des paramètres différents), le nombre d’appels, le temps total, le temps moyen par appel. Les temps sont en millisecondes.

NB : pour une instance en version 12 ou antérieure, utiliser le champ total_time, qui inclut aussi le temps de planification.


pg_stat_statements : exemple 2

Requêtes les plus fréquemment appelées :

SELECT r.rolname, d.datname, s.calls, s.total_exec_time,
       s.total_exec_time / s.calls  AS avg_time, s.query
  FROM pg_stat_statements s
  JOIN pg_roles r     ON (s.userid=r.oid)
  JOIN pg_database d  ON (s.dbid = d.oid)
 ORDER BY s.calls DESC
 LIMIT 10;

Cette requête affiche les dix requêtes les plus fréquentes en nombre d’appels, et le temps moyen. Exemple de sortie, avec un peu de formatage :

\pset format wrapped
\pset columns 83

 SELECT r.rolname, d.datname,
       to_char (s.calls,'999G999FM') AS calls,
       s.total_exec_time * interval '1ms' AS total_exec_time,
       s.total_exec_time/s.calls * interval '1ms' AS avg_time,
       s.query
  FROM pg_stat_statements s
  JOIN pg_roles r     ON (s.userid=r.oid)
  JOIN pg_database d  ON (s.dbid = d.oid)
 ORDER BY s.calls DESC
 LIMIT 10  \gx
-[ RECORD 1 ]---+-----------------------------------------------------------------
rolname         | postgres
datname         | postgres
calls           | 329 021
total_exec_time | 00:00:01.617168
avg_time        | 00:00:00.000005
query           | SELECT pg_postmaster_start_time()
-[ RECORD 2 ]---+-----------------------------------------------------------------
rolname         | postgres
datname         | postgres
calls           | 316 192
total_exec_time | 24:19:01.780477
avg_time        | 00:00:00.276863
query           | SELECT                                                          +
                |             count(datid) as databases,                          +
                |             pg_size_pretty(sum(pg_database_size(                +
                |                 pg_database.datname))::bigint) as total_size,   +
                |             to_char(now(),$1) as time,                          +
                |             sum(xact_commit)::BIGINT as total_commit,           +
                |             sum(xact_rollback)::BIGINT as total_rollback        +
                |         FROM pg_database                                        +
                |         JOIN pg_stat_database ON (pg_database.oid = pg_stat_data.
                |.base.datid)                                                     +
                |         WHERE datistemplate = $2
-[ RECORD 3 ]---+-----------------------------------------------------------------
rolname         | postgres
datname         | postgres
calls           | 316 192
total_exec_time | 00:01:22.127931
avg_time        | 00:00:00.00026
query           | SELECT CASE sum(blks_hit+blks_read)                             +
                |           WHEN $1 THEN $2                                       +
                |           ELSE trunc(sum(blks_hit)/sum(blks_hit+blks_read)*$3)::.
                |.float                                                           +
                |         END AS hitratio                                         +
                |         FROM pg_stat_database
-[ RECORD 4 ]---+-----------------------------------------------------------------
rolname         | postgres
datname         | postgres
calls           | 316 192
total_exec_time | 00:00:02.82872
avg_time        | 00:00:00.000009
query           | SELECT buffers_alloc FROM pg_stat_bgwriter
-[ RECORD 5 ]---+-----------------------------------------------------------------
rolname         | postgres
datname         | postgres
calls           | 316 192
total_exec_time | 00:18:08.125136
avg_time        | 00:00:00.003441
query           | SELECT COUNT(*) AS nb FROM pg_stat_activity WHERE state != $1
-[ RECORD 6 ]---+-----------------------------------------------------------------
rolname         | postgres
datname         | pgbench_300_hdd
calls           | 79 534
total_exec_time | 00:03:44.82423
avg_time        | 00:00:00.002827
query           | select wait_event, wait_event_type, query from pg_stat_activity .
                |.where state =$1 and pid = $2
-[ RECORD 7 ]---+-----------------------------------------------------------------
rolname         | temboard_agent
datname         | postgres
calls           | 75 028
total_exec_time | 00:00:00.368735
avg_time        | 00:00:00.000005
query           | SELECT pg_postmaster_start_time()
-[ RECORD 8 ]---+-----------------------------------------------------------------
rolname         | temboard_agent
datname         | postgres
calls           | 72 091
total_exec_time | 00:04:02.992142
avg_time        | 00:00:00.003371
query           | SELECT COUNT(*) AS nb FROM pg_stat_activity WHERE state != $1
-[ RECORD 9 ]---+-----------------------------------------------------------------
rolname         | temboard_agent
datname         | postgres
calls           | 72 091
total_exec_time | 05:47:55.416569
avg_time        | 00:00:00.28957
query           | SELECT                                                          +
                |             count(datid) as databases,                          +
                |             pg_size_pretty(sum(pg_database_size(                +
                |                 pg_database.datname))::bigint) as total_size,   +
                |             to_char(now(),$1) as time,                          +
                |             sum(xact_commit)::BIGINT as total_commit,           +
                |             sum(xact_rollback)::BIGINT as total_rollback        +
                |         FROM pg_database                                        +
                |         JOIN pg_stat_database ON (pg_database.oid = pg_stat_data.
                |.base.datid)                                                     +
                |         WHERE datistemplate = $2
-[ RECORD 10 ]--+-----------------------------------------------------------------
rolname         | temboard_agent
datname         | postgres
calls           | 72 091
total_exec_time | 00:00:17.817369
avg_time        | 00:00:00.000247
query           | SELECT CASE sum(blks_hit+blks_read)                             +
                |           WHEN $1 THEN $2                                       +
                |           ELSE trunc(sum(blks_hit)/sum(blks_hit+blks_read)*$3)::.
                |.float                                                           +
                |         END AS hitratio                                         +
                |         FROM pg_stat_database

On voit qu’il y a beaucoup de requêtes de supervision, ce qui est logique. Il est donc conseillé de dédier un utilisateur à la supervision pour pouvoir filtrer aisément.


pg_stat_statements : exemple 3

Requêtes les plus consommatrices et hit ratio :

SELECT calls, total_exec_time, rows,
       100.0*shared_blks_hit
       /nullif(shared_blks_hit+shared_blks_read, 0) AS "hit %",
       query
FROM  pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5 ;

Cette requête calcule le hit ratio, c’est-à-dire la proportion des blocs lus depuis le cache de PostgreSQL, pour les cinq plus grosses requêtes en temps cumulé. Dans l’idéal, ce ratio serait à 100 %.


auto_explain

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

L’outil auto_explain est habituellement activé quand on a le sentiment qu’une requête devient subitement lente à certains moments, et qu’on suspecte que son plan diffère entre deux exécutions. Elle permet de tracer dans les journaux applicatifs, voire dans la console, le plan de la requête dès qu’elle dépasse une durée configurée.

C’est une « contrib » officielle de PostgreSQL (et non une extension). Tracer systématiquement le plan d’exécution d’une requête souvent répétée prend de la place, et est assez coûteux. C’est donc un outil à utiliser parcimonieusement. En général on ne trace ainsi que les requêtes dont la durée d’exécution dépasse la durée configurée avec le paramètre auto_explain.log_min_duration. Par défaut, ce paramètre vaut -1 pour ne tracer aucun plan.

Comme dans un EXPLAIN classique, on peut activer les options (par exemple ANALYZE ou TIMING avec, respectivement, un SET auto_explain.log_analyze TO true; ou un SET auto_explain.log_timing TO true;) mais l’impact en performance peut être important même pour les requêtes qui ne seront pas tracées.

D’autres options existent, qui reprennent les paramètres habituels d’EXPLAIN, notamment : auto_explain.log_buffers, auto_explain.log_settings.

Quant à auto_explain.sample_rate, il permet de ne tracer qu’un échantillon des requêtes (voir la documentation).

Pour utiliser auto_explain globalement, il faut charger la bibliothèque au démarrage dans le fichier postgresql.conf via le paramètre shared_preload_libraries.

shared_preload_libraries='auto_explain'

Après un redémarrage de l’instance, il est possible de configurer les paramètres de capture des plans d’exécution par base de données. Dans l’exemple ci-dessous, l’ensemble des requêtes sont tracées sur la base de données bench, qui est utilisée par pgbench.

ALTER DATABASE bench SET auto_explain.log_min_duration = '0';
ALTER DATABASE bench SET auto_explain.log_analyze = true;

Attention, l’activation des traces complètes sur une base de données avec un fort volume de requêtes peut être très coûteux.

Un benchmark pgbench est lancé sur la base de données bench avec 1 client qui exécute 1 transaction par seconde pendant 20 secondes :

pgbench -c1 -R1 -T20 bench

Les plans d’exécution de l’ensemble les requêtes exécutées par pgbench sont alors tracés dans les traces de l’instance.

2021-07-01 13:12:55.790 CEST [1705] LOG:  duration: 0.041 ms  plan:
    Query Text: SELECT abalance FROM pgbench_accounts WHERE aid = 416925;
    Index Scan using pgbench_accounts_pkey on pgbench_accounts
        (cost=0.42..8.44 rows=1 width=4) (actual time=0.030..0.032 rows=1 loops=1)
        Index Cond: (aid = 416925)
2021-07-01 13:12:55.791 CEST [1705] LOG:  duration: 0.123 ms  plan:
    Query Text: UPDATE pgbench_tellers SET tbalance = tbalance + -3201 WHERE tid = 19;
    Update on pgbench_tellers  (cost=0.00..2.25 rows=1 width=358)
                               (actual time=0.120..0.121 rows=0 loops=1)
        ->  Seq Scan on pgbench_tellers  (cost=0.00..2.25 rows=1 width=358)
                                         (actual time=0.040..0.058 rows=1 loops=1)
            Filter: (tid = 19)
            Rows Removed by Filter: 99
2021-07-01 13:12:55.797 CEST [1705] LOG:  duration: 0.116 ms  plan:
    Query Text: UPDATE pgbench_branches SET bbalance = bbalance + -3201 WHERE bid = 5;
    Update on pgbench_branches  (cost=0.00..1.13 rows=1 width=370)
                                (actual time=0.112..0.114 rows=0 loops=1)
        ->  Seq Scan on pgbench_branches  (cost=0.00..1.13 rows=1 width=370)
                                (actual time=0.036..0.038 rows=1 loops=1)
            Filter: (bid = 5)
            Rows Removed by Filter: 9
[...]

Pour utiliser auto_explain uniquement dans la session en cours, il faut penser à descendre au niveau de message LOG (défaut de auto_explain). On procède ainsi :

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_analyze = true;
SET client_min_messages to log;
SELECT count(*)
   FROM pg_class, pg_index
   WHERE oid = indrelid AND indisunique;
LOG:  duration: 1.273 ms  plan:
Query Text: SELECT count(*)
           FROM pg_class, pg_index
           WHERE oid = indrelid AND indisunique;
Aggregate  (cost=38.50..38.51 rows=1 width=8)
    (actual time=1.247..1.248 rows=1 loops=1)
  ->  Hash Join  (cost=29.05..38.00 rows=201 width=0)
          (actual time=0.847..1.188 rows=198 loops=1)
        Hash Cond: (pg_index.indrelid = pg_class.oid)
        ->  Seq Scan on pg_index  (cost=0.00..8.42 rows=201 width=4)
                         (actual time=0.028..0.188 rows=198 loops=1)
              Filter: indisunique
              Rows Removed by Filter: 44
        ->  Hash  (cost=21.80..21.80 rows=580 width=4)
           (actual time=0.726..0.727 rows=579 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 29kB
              ->  Seq Scan on pg_class  (cost=0.00..21.80 rows=580 width=4)
                                (actual time=0.016..0.373 rows=579 loops=1)
 count
-------
   198

auto_explain est aussi un moyen de suivre les plans au sein de fonctions. Par défaut, un plan n’indique les compteurs de blocs hit, read, temp… que de l’appel global à la fonction.

Une fonction simple en PL/pgSQL est définie pour récupérer le solde le plus élevé dans la table pgbench_accounts :

CREATE OR REPLACE function f_max_balance() RETURNS int AS $$
    DECLARE
        acct_balance int;
    BEGIN
        SELECT max(abalance)
        INTO acct_balance
        FROM pgbench_accounts;
        RETURN acct_balance;
    END;
$$ LANGUAGE plpgsql ;

Un simple EXPLAIN ANALYZE de l’appel de la fonction ne permet pas d’obtenir le plan de la requête SELECT max(abalance) FROM pgbench_accounts contenue dans la fonction :

EXPLAIN (ANALYZE,VERBOSE) SELECT f_max_balance();
                               QUERY PLAN
-------------------------------------------------------------------------------
 Result  (cost=0.00..0.26 rows=1 width=4) (actual time=49.214..49.216 rows=1 loops=1)
   Output: f_max_balance()
 Planning Time: 0.149 ms
 Execution Time: 49.326 ms

Par défaut, auto_explain ne va pas capturer plus d’information que la commande EXPLAIN ANALYZE. Le fichier log de l’instance capture le même plan lorsque la fonction est exécutée.

2021-07-01 15:39:05.967 CEST [2768] LOG:  duration: 42.937 ms  plan:
    Query Text: select f_max_balance();
    Result  (cost=0.00..0.26 rows=1 width=4)
            (actual time=42.927..42.928 rows=1 loops=1)

Il est cependant possible d’activer le paramètre log_nested_statements avant l’appel de la fonction, de préférence uniquement dans la ou les sessions concernées :

\c bench
SET auto_explain.log_nested_statements = true;
SELECT f_max_balance();

Le plan d’exécution de la requête SQL est alors visible dans les traces de l’instance :

2021-07-01 14:58:40.189 CEST [2202] LOG:  duration: 58.938 ms  plan:
Query Text: select max(abalance)
            from pgbench_accounts
Finalize Aggregate
(cost=22632.85..22632.86 rows=1 width=4)
(actual time=58.252..58.935 rows=1 loops=1)
    ->  Gather
        (cost=22632.64..22632.85 rows=2 width=4)
        (actual time=57.856..58.928 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate
                (cost=21632.64..21632.65 rows=1 width=4)
                (actual time=51.846..51.847 rows=1 loops=3)
            ->  Parallel Seq Scan on pgbench_accounts
                    (cost=0.00..20589.51 rows=417251 width=4)
                    (actual time=0.014..29.379 rows=333333 loops=3)

pgBadger est capable de lire les plans tracés par auto_explain, de les intégrer à son rapport et d’inclure un lien vers depesz.com pour une version plus lisible.


pg_buffercache

Qu’y a-t’il dans le cache de PostgreSQL ?

Fournit une vue :

  • Pour chaque page (donc pour l’ensemble de l’instance)
    • fichier (donc objet) associé
    • OID base
    • fork (0 : table, 1 : FSM, 2 : VM)
    • numéro de bloc
    • isdirty
    • usagecount

Pour chaque entrée (bloc, par défaut de 8 ko) du cache disque de PostgreSQL, cette vue nous fournit les informations suivantes : le fichier (donc la table, l’index…), le bloc dans ce fichier, si ce bloc est synchronisé avec le disque (isdirty à false) ou s’il est « sale » (modifié en mémoire mais non synchronisé sur disque), et si ce bloc a été utilisé récemment (de 0 « plus utilisé dernièrement » à 5 « récemment utilisé »).

Cela permet donc de déterminer les hot blocks de la base, ou d’avoir une idée un peu plus précise du bon dimensionnement du cache : si rien n’atteint un usagecount de 5, le cache est manifestement trop petit : il n’est pas capable de détecter les pages devant impérativement rester en cache. Inversement, si vous avez énormément d’entrées à 0 et quelques pages avec des usagecount très élevés, toutes ces pages à 0 sont égales devant le mécanisme d’éviction du cache. Elles sont donc supprimées à peu près de la même façon que du cache du système d’exploitation. Le cache de PostgreSQL dans ce cas fait « double emploi » avec lui, et pourrait être réduit.

Attention toutefois avec les expérimentations sur les caches : il existe des effets de seuils. Un cache trop petit peut de la même façon qu’un cache trop grand avoir une grande fraction d’enregistrements avec un usagecount à 0. Par ailleurs, le cache bouge extrêmement rapidement par rapport à notre capacité d’analyse. Nous ne voyons qu’un instantané, qui peut ne pas refléter toute la réalité.

isdirty indique si un buffer est synchronisé avec le disque ou pas. Il est intéressant de vérifier qu’une instance dispose en permanence d’un certain nombre de buffers pour lesquels isdirty vaut false et pour lesquels usagecount vaut 0. Si ce n’est pas le cas, c’est le signe :

  • que shared_buffers est probablement trop petit (il n’arrive pas à contenir les modifications) ;
  • que le background_writer n’est pas assez agressif.

De plus, avant la version 10, l’utilisation de cette extension est assez coûteuse car elle a besoin d’acquérir un verrou sur chaque page de cache inspectée. Chaque verrou est acquis pour une durée très courte, mais elle peut néanmoins entraîner une contention. L’impact a été diminué en version 10.

À titre d’exemple, cette requête affiche les dix plus gros objets de la base en cours en mémoire cache (dont, ici, deux index) :

SELECT c.relname,
       c.relkind,
       count(*) AS buffers,
       pg_size_pretty(count(*)*8192) as taille_mem
FROM   pg_buffercache b
INNER JOIN pg_class c
      ON b.relfilenode = pg_relation_filenode(c.oid)
        AND b.reldatabase IN (0, (SELECT oid FROM pg_database