Cette formation (diapositives, manuels et travaux pratiques) est sous licence CC-BY-NC-SA.

Vous êtes libres de redistribuer et/ou modifier cette création selon les conditions suivantes :

• Paternité

• Pas d'utilisation commerciale

• Partage des conditions initiales à l'identique

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

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.

À 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 à cette adresse: http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Les bases de données sont des systèmes très complexes. Afin d'en tirer toutes les performances possibles, l'optimisation doit porter sur un très grand nombre de composants différents : le serveur qui héberge la base de données, les processus faisant fonctionner la base de données, les fichiers et disques servant à son stockage, mais aussi, et surtout, l'application elle-même. C'est sur ce dernier point que les gains sont habituellement les plus importants.

Ce module se focalise sur ce dernier point. Il n'aborde pas les plans d'exécution à proprement parler, ceux-ci étant traités par un autre module de formation.

Le principe de Pareto et la loi de Pareto sont des constats empiriques. On peut définir mathématiquement une distribution vérifiant la loi de Pareto. De nombreux phénomènes suivent cette distribution, comme par exemple les files d'attente (trafic internet par exemple).

Ces outils permettent rapidement d'identifier les requêtes les plus consommatrices sur un serveur. Les outils pour PostgreSQL ont le fonctionnement suivant :

• pgBadger est un analyseur de log. On trace donc dans les journaux applicatifs de PostgreSQL toutes les requêtes, leur durée. L'outil les analyse et retourne les requêtes les plus fréquemment exécutées, les plus gourmandes unitairement, les plus gourmandes en temps cumulé (somme des temps unitaires) ;
• pg_stat_statements est une vue de PostgreSQL qui trace pour chaque ordre exécuté sur l'instance son nombre d'exécution, sa durée cumulée, et un certain nombre d'autres statistiques très utiles.
• pg_stat_plans est une évolution de pg_stat_statements stockant en plus le plan de ces requêtes. En effet, entre le moment de l'exécution de la requête et celui de la consultation de son plan par l'utilisateur souhaitant travailler à son optimisation, le plan peut avoir changé. Elle n'est par contre pas fournie avec PostgreSQL et doit donc être installée séparément.
• PoWA est similaire à Oracle AWR. Il s'appuie sur pg_stat_statements pour permettre d'historiser l'activité du serveur. Une interface web permet ensuite de visualiser l'activité ainsi historisée et repérer les requêtes problématiques avec les fonctionnalités de drill-down de l'interface.

Contrairement à une idée assez fréquemment répandue, le terme relationnel ne désigne pas le fait que les tables soient liées entre elles. Les « tables » SONT les relations. On fait référence ici à l'algèbre relationnelle, inventée en 1970 par Edgar Frank Codd.

Les bases de données dites relationnelles n'implémentent habituellement pas exactement cet algèbre, mais en sont très proches. Le langage SQL, entre autres, ne respecte pas l'algèbre relationnelle. Le sujet étant vaste et complexe, il ne sera pas abordé ici. Si vous voulez approfondir le sujet, le livre Introduction aux bases de données de Chris J. Date, est un des meilleurs ouvrages sur l'algèbre relationnelle et les déficiences du langage SQL à ce sujet.

Le modèle relationnel est apparu suite à un constat : les bases de données de l'époque (hiérarchiques) reposaient sur la notion de pointeur. Une mise à jour pouvait donc facilement casser le modèle : doublons simples, données pointant sur du « vide », doublons incohérents entre eux, etc.

Le modèle relationnel a donc été proposé pour remédier à tous ces problèmes. Un système relationnel repose sur le concept de relation (table en SQL). Une relation est un ensemble de faits. Chaque fait est identifié par un identifiant (clé naturelle). Le fait lie cet identifiant à un certain nombre d'attributs. Une relation ne peut donc pas avoir de doublon.

La modélisation relationnelle étant un vaste sujet en soi, nous n'allons pas tout détailler ici, mais plutôt rappeler les points les plus importants.

Une relation (table) est en troisième forme normale si tous les attributs (colonnes) dépendent de la clé (primaire), de toute la clé (pas d'un sous-ensemble de ses colonnes), et de rien d'autre que de la clé (une colonne supplémentaire).

Si vos tables vérifient déjà ces trois points, votre modélisation est probablement assez bonne.

Voir l'article wikipedia présentant l'ensemble des formes normales.

On rencontre parfois des tables de cette forme :

Cette modélisation viole la première forme normale (atomicité des attributs). Si on recherche toutes les voitures vertes, on va devoir utiliser une clause WHERE de ce type :

WHERE caracteristiques LIKE '%peinture verte%'

ce qui sera évidemment très inefficace.

Par ailleurs, on n'a évidemment aucun contrôle sur ce qui est mis dans le champ caractéristiques, ce qui est la garantie de données incohérentes au bout de quelques jours (heures ?) d'utilisation.

Il aurait certainement fallu modéliser cela en rajoutant des colonnes boolean quatre_roues_motrices, boolean abs, varchar couleur (ou même, encore mieux, une table des couleurs, ou un enum de la liste des couleurs autorisées, etc.)

Dans ce cas, rien n'empêche d'ajouter une ligne avec des caractéritiques similaires mais définie autrement : * ABS / antiblocage des roues * Le moteur retournera le véhicule "AD-057-GD si on veut rechercher un véhicule de couleur "bleu" LIKE '%peinture verte%'

Ce modèle ne permet pas d'assurer la cohérence des données.

Ce modèle facilite les recherches et assure la cohérence.

Une table qui contient majoritairement des valeurs NULL contient bien peu de faits utilisables. La plupart du temps, c'est une table dans laquelle on stocke beaucoup de choses n'ayant que peu de rapport entre elles, les champs étant renseignés suivant le type de chaque « chose ». C'est donc le plus souvent un signe de mauvaise modélisation. Cette table aurait certainement dû être éclatée en plusieurs tables, chacune représentant une des relations qu'on veut modéliser.

Il est donc recommandé que tous les attributs d'une table portent une contrainte NOT NULL. Quelques colonnes peuvent ne pas porter ce type de contraintes, mais elles doivent être une exception. En effet, le comportement de la base de données est souvent source de problèmes lorsqu'une valeur NULL entre en jeu, par exemple la concaténation d'une chaîne de caractères avec une valeur retourne une valeur NULL, car elle est propagée dans les calculs. D'autres types de problèmes apparaissent également pour les prédicats.

Il faut avoir à l'esprit cette citation de Chris Date :

« La valeur NULL telle qu'elle est implémentée dans SQL peut poser plus de problèmes qu'elle n'en résout. Son comportement est parfois étrange et est source de nombreuses erreurs et de confusions. »

On rencontre parfois ce genre de choses :

avec bien sûr une table tournée décrivant la tournée elle-même, avec une clé technique numérique.

On a un gros problème de modélisation : la colonne a un type de contenu qui dépend de l'information qu'elle contient. On aurait dû avoir une colonne supplémentaire (un booléen tournee_ok par exemple). On va aussi avoir un problème de performance en joignant ce varchar à la clé numérique de la table tournée. Le moteur n'aura que deux choix : convertir le varchar en numérique, avec une exception à la clé en essayant de convertir « ANNULÉE », ou bien (ce qu'il fera) convertir le numérique de la table tournee en chaîne. Cette dernière méthode rendra l'accès à l'id de tournée par index impossible. D'où un parcours complet (seq scan) de la table tournée à chaque accès.

Le modèle relationnel a été critiqué depuis sa création pour son manque de souplesse pour ajouter de nouveaux attributs ou pour proposer plusieurs attributs sans pour autant nécessiter de redévelopper l'application.

La solution souvent retenue est d'utiliser une table « à tout faire » entité-attribut-valeur qui est associée à une autre table de la base de données. Techniquement, une telle table comporte trois colonnes. La première est un identifiant généré qui permet de référencer la table mère. Les deux autres colonnes stockent le nom de l'attribut représenté et la valeur représentée.

Ainsi, pour reprendre l'exemple des informations de contacts pour un individu, une table personnes permet de stocker un identifiant de personne. Une table personne_attributs permet d'associer des données à un identifiant de personne. Le type de données de la colonne est souvent prévu largement pour faire tenir tout type d'informations, mais sous forme textuelle. Les données ne peuvent donc pas être validées.

CREATE TABLE personnes (id SERIAL PRIMARY KEY);

CREATE TABLE personne_attributs (
  id_pers INTEGER NOT NULL,
  nom_attr varchar(20) NOT NULL,
  val_attr varchar(100) NOT NULL
);

INSERT INTO personnes (id) VALUES (nextval('personnes_id_seq')) RETURNING id;
 id
----
  1

INSERT INTO personne_attributs (id_pers, nom_attr, val_attr)
  VALUES (1, 'nom', 'Prunelle'),
         (1, 'prenom', 'Léon');
(...)

Un tel modèle peut sembler souple mais pose plusieurs problèmes. Le premier concerne l'intégrité des données. Il n'est pas possible de garantir la présence d'un attribut comme on le ferait avec une contrainte NOT NULL. Si l'on souhaite stocker des données dans un autre format qu'une chaîne de caractère, pour bénéficier des contrôles de la base de données sur ce type, la seule solution est de créer autant de colonnes d'attributs qu'il y a de types de données à représenter. Ces colonnes ne permettront pas d'utiliser des contraintes CHECK pour garantir la cohérence des valeurs stockées avec ce qui est attendu, car les attributs peuvent stocker n'importe quelle donnée.

Les requêtes SQL qui permettent de récupérer les données requises dans l'application sont également particulièrement lourdes à écrire et à maintenir, à moins de récupérer les données attribut par attribut.

Des problèmes de performances vont donc très rapidement se poser. Cette représentation des données entraîne souvent l'effondrement des performances d'une base de données relationnelle. Les requêtes sont difficilement optimisables et nécessitent de réaliser beaucoup d'entrées-sorties disque, car les données sont éparpillées un peu partout dans la table.

Lorsque de telles solutions sont déployées pour stocker des données transactionnelles, il vaut mieux revenir à un modèle de données traditionnel qui permet de typer correctement les données, de mettre en place les contraintes d'intégrité adéquates et d'écrire des requêtes SQL efficaces.

Dans d'autres cas, il vaut mieux utiliser un type de données de PostgreSQL qui est approprié, comme hstore qui permet de stocker des données sous la forme clé->valeur. Ce type de données peut être indexé pour garantir de bons temps de réponses des requêtes qui nécessitent des recherches sur certaines clés ou certaines valeurs.

Voici l'exemple précédent revu avec l'extension hstore :

CREATE EXTENSION hstore;
CREATE TABLE personnes (id SERIAL PRIMARY KEY, attributs hstore);

INSERT INTO personnes (attributs) VALUES ('nom=>Prunelle, prenom=>Léon');
INSERT INTO personnes (attributs) VALUES ('prenom=>Gaston,nom=>Lagaffe');
INSERT INTO personnes (attributs) VALUES ('nom=>DeMaesmaker');

SELECT * FROM personnes;
 id |              attributs
----+--------------------------------------
  1 | "nom"=>"Prunelle", "prenom"=>"Léon"
  2 | "nom"=>"Lagaffe", "prenom"=>"Gaston"
  3 | "nom"=>"DeMaesmaker"
(3 rows)

SELECT id, attributs->'prenom' FROM personnes;
 id | ?column?
----+----------
  1 | Léon
  2 | Gaston
  3 |
(3 rows)

SELECT id, attributs->'nom' FROM personnes;
 id |  ?column?
----+-------------
  1 | Prunelle
  2 | Lagaffe
  3 | DeMaesmaker
(3 rows)

Il arrive régulièrement de rencontrer des tables ayant énormément de colonnes (souvent à NULL d'ailleurs). Cela signifie qu'on modélise une entité ayant tous ces attributs (centaines d'attributs). Il est très possible que cette entité soit en fait composée de « sous-entités », qu'on pourrait modéliser séparément. On peut évidemment trouver des cas particuliers contraires, mais une table de ce type reste un bon indice.

Surtout si vous trouvez dans les dernières colonnes des attributs comme attribut_supplementaire_1…

Les contraintes d'intégrité et notamment les clés étrangères sont parfois absentes des modèles de données. Les problématiques de performance et de flexibilité sont souvent mises en avant alors que les contraintes sont justement une aide pour l'optimisation de requêtes par le planificateur.

De plus, l'absence de contraintes va également entraîner des problèmes d'intégrité des données. Il est par exemple très compliqué de se prémunir efficacement contre une race condition¹ en l'absence de clé étrangère. Lorsque ces problèmes d'intégrité seront détectés, il s'en suivra également la création de procédures de vérification de cohérence des données qui vont aussi alourdir les développements, entraînant ainsi un travail supplémentaire pour trouver et corriger les incohérences. Ce qui a été gagné d'un côté est perdu de l'autre, mais sous une forme différente.

Les contraintes d'intégrité sont des informations qui garantissent non seulement la cohérence des données mais qui vont également influencer l'optimiseur dans ses choix de plans d'exécution.

Parmi les informations utilisées par l'optimiseur, les contraintes d'unicité permettent de déterminer sans difficulté la répartition des valeurs stockées dans une colonne : chaque valeur est simplement unique. L'utilisation des index sur ces colonnes sera donc probablement favorisée. Les contraintes d'intégrité permettent également à l'optimiseur de pouvoir éliminer des jointures inutiles avec un LEFT JOIN. Enfin, les contraintes CHECK sur des tables partitionnées permettent de cibler les lectures sur certaines partitions seulement, et donc d'exclure les partitions inutiles.

Il est toujours tentant d'augmenter la quantité de ressources matérielles pour résoudre un problème de performance. Il ne faut surtout pas négliger tous les coûts de cette solution : non seulement l'achat de matériel, mais aussi les coûts humains : procédures d'exploitation, de maintenance, complexité accrue de développement, etc.

Performance et robustesse peuvent être des objectifs contradictoires.

Le langage SQL a été normalisé par l'ANSI en 1986 et est devenu une norme ISO internationale en 1987. Il s'agit de la norme ISO 9075. Elle a subi plusieurs évolutions dans le but d'ajouter des fonctionnalités correspondant aux attentes de l'industrie logicielle. Parmi ces améliorations, notons l'intégration de quelques fonctionnalités objet pour le modèle relationnel-objet. La dernière version stable de la norme est SQL:2011.

Tous ces opérateurs sont optimisables : il y a 40 ans de théorie mathématique développée afin de permettre l'optimisation de ces traitements. L'optimiseur fera un excellent travail sur ces opérations, et les organisera de façon efficace.

Par exemple : a JOIN b JOIN c WHERE c.col=constante sera très probablement réordonné en c JOIN b JOIN a WHERE c.col=constante ou c JOIN a JOIN b WHERE c.col=constante. Le moteur se débrouillera aussi pour choisir le meilleur algorithme de jointure pour chacune, suivant les volumétries ramenées.

Ceux-ci sont plus difficilement optimisables : ils introduisent par exemple des contraintes d'ordre dans l'exécution :

SELECT * FROM table1
WHERE montant > (
 SELECT avg(montant) FROM table1 WHERE departement='44'
);

On doit exécuter la sous-requête avant la requête.

Les jointures externes sont relationnelles, mais posent tout de même des problèmes et doivent être traitées prudemment.

SELECT * FROM t1 LEFT JOIN t2 on (t1.t2id=t2.id) JOIN t3 on (t1.t3id=t3.id) ;

Il faut faire les jointures dans l'ordre indiqué : joindre t1 à t3 puis le résultat à t2 pourrait ne pas amener le même résultat (un LEFT JOIN peut produire des NULL). Il est donc préférable de toujours mettre les jointures externes en fin de requête, sauf besoin précis : on laisse bien plus de liberté à l'optimiseur.

Le mot clé DISTINCT ne doit être utilisé qu'en dernière extrémité. On le rencontre très fréquemment dans des requêtes mal écrites qui produisent des doublons, afin de maquiller le résultat. C'est bien sûr extrêmement simple de produire des doublons pour ensuite dédoublonner (au moyen d'un tri de l'ensemble du résultat, bien sûr).

Éviter donc les SELECT * : c'est une bonne pratique de toute façon, car la requête peut changer de résultat suite à un changement de schéma, ce qui risque d'entraîner des conséquences sur le reste du code.

Ne récupérer que les colonnes utilisées. Certains moteurs suppriment d'eux-même les colonnes qui ne sont pas retournées à l'appelant, comme par exemple dans le cas de :

SELECT col1, col2 FROM (SELECT * FROM t1 JOIN t2 USING (t2id) ) ;

PostgreSQL ne le fait pas pour le moment.

Éviter les jointures sur des tables inutiles : il n'y a que peu de cas où l'optimiseur peut supprimer de lui-même l'accès à une table inutile.

PostgreSQL le fait dans le cas d'un LEFT JOIN sur une table inutilisée dans le SELECT, au travers d'une clé étrangère, car on peut garantir que cette table est effectivement inutile.

Les bases de données relationnelles sont conçues pour manipuler des relations, pas des enregistrements unitaires.

Le langage SQL (et même les autres langages relationnels qui ont existé comme QUEL, SEQUEL) est conçu pour permettre la manipulation d'un gros volume de données, et la mise en correspondance (jointure) d'informations. Une base de données relationnelle n'est pas une simple couche de persistance.

Le fait de récupérer en une seule opération l'ensemble des informations pertinentes est aussi, indépendamment du langage, un gain de performance énorme, car il permet de s'affranchir en grande partie des latences de communication entre la base et l'application.

Préparons un jeu de test :

psql> CREATE TABLE test (a int, b varchar);
psql> INSERT INTO test SELECT i,i FROM generate_series (1,1000000) g(i);
psql> ALTER TABLE test ADD PRIMARY KEY (a);

Récupérons 10 000 enregistrements un par un (par un script perl par exemple, avec une requête préparée pour être dans le cas le plus efficace) :

#!/usr/bin/perl -w
print "PREPARE ps (int) AS SELECT * FROM test WHERE a=\$1;\n";
for (my $i=0;$i<=10000;$i++)
{
        print "EXECUTE ps($i);\n";
}

Exécutons ce code :

time perl demo.pl  | psql > /dev/null

real    0m1.025s
user    0m0.213s
sys     0m0.057s

Voici maintenant la même chose, en un seul ordre SQL :

time psql -c "select * from test where a >=0 and a <= 10000" > /dev/null

real    0m0.052s
user    0m0.030s
sys     0m0.003s

La plupart des ORM fournissent un moyen de traverser des liens entre objets. Par exemple, si une commande est liée à plusieurs articles, un ORM permettra d'écrire un code similaire à celui-ci (exemple en Java avec Hibernate) :

List commandes = sess.createCriteria(Commande.class);

for(Commande cmd : commandes)
{
  // Un traitement utilisant les produits
  // Génère une requête par commande !!
  System.out.println(cmd.getProduits());
}

Tel quel, ce code générera N+1 requête, N étant le nombre de commandes. En effet, pour chaque accès à l'attribut "produits", l'ORM générera une requête pour récupérer les produits correspondants à la commande.

Le SQL généré sera alors similaire à celui-ci :

SELECT * FROM commande;
SELECT * from produits where commande_id = 1;
SELECT * from produits where commande_id = 2;
SELECT * from produits where commande_id = 3;
SELECT * from produits where commande_id = 4;
SELECT * from produits where commande_id = 5;
SELECT * from produits where commande_id = 6;
...

La plupart des ORM proposent des options pour personnaliser la stratégie d'accès aux collections. Il est extrêmement important de connaître celles-ci afin de permettre à l'ORM de générer des requêtes optimales.

Par exemple, dans le cas précédent, nous savons que tout les produits de toutes les commandes seront utilisés. Nous pouvons donc informer l'ORM de ce fait :

List commandes = sess.createCriteria(Commande.class)
  .setFetchMode('produits', FetchMode.EAGER);

for(Commande cmd : commandes)
{
  // Un traitement utilisant les produits
  System.out.println(cmd.getProduits());
}

Ceci générera une seule et unique requête du type :

SELECT * FROM commandes
LEFT JOIN produits ON commandes.id = produits.commande_id;

Les vues sont très pratiques en SQL et en théorie permettent de séparer le modèle physique (les tables) de ce que voient les développeurs, et donc de faire évoluer le modèle physique sans impact pour le développement. En pratique, elles vont souvent être source de ralentissement : elles masquent la complexité, et peuvent rapidement conduire à l'écriture implicite de requêtes très complexes, mettant en jeu des dizaines de tables (voire des dizaines de fois les MÊMES tables).

Il faut donc se méfier des vues. En particulier, des vues contenant des opérations non-relationnelles, qui peuvent empêcher de nombreuses optimisations. En voici un exemple simple. La vue a été remplacée par une sous-requête équivalente :

EXPLAIN SELECT id,valeur FROM
 (SELECT DISTINCT ON (id) id,valeur FROM test ) AS tmp
 WHERE valeur='b' ;

QUERY PLAN
Subquery Scan on tmp (cost=76.43..85.08 rows=1 width=36)
Filter: (tmp.valeur = 'b'::text)
-> Unique (cost=76.43..82.58 rows=200 width=36)
-> Sort (cost=76.43..79.50 rows=1230 width=36)
Sort Key: test.id
-> Seq Scan on test (cost=0.00..13.30 rows=1230 width=36)

On constate que la condition de filtrage sur b n'est appliquée qu'à la fin. C'est normal, à cause du DISTINCT ON, l'optimiseur ne peut pas savoir si l'enregistrement qui sera retenu dans la sous-requête vérifiera valeur='b' ou pas, et doit donc attendre l'étape suivante pour filtrer. Le coût en performances, même avec un volume de données raisonnable, peut être astronomique.

Un exemple (sous Oracle) :

SELECT Article.datem                                 AS Article_1_9,
     Article.degre_alcool                          AS Article_1_10,
     Article.id                                    AS Article_1_19,
     Article.iddf_categor                          AS Article_1_20,
     Article.iddp_clsvtel                          AS Article_1_21,
     Article.iddp_cdelist                          AS Article_1_22,
     Article.iddf_cd_prix                          AS Article_1_23,
     Article.iddp_agreage                          AS Article_1_24,
     Article.iddp_codelec                          AS Article_1_25,
     Article.idda_compo                            AS Article_1_26,
     Article.iddp_comptex                          AS Article_1_27,
     Article.iddp_cmptmat                          AS Article_1_28,
     Article.idda_articleparent                    AS Article_1_29,
     Article.iddp_danger                           AS Article_1_30,
     Article.iddf_fabric                           AS Article_1_33,
     Article.iddp_marqcom                          AS Article_1_34,
     Article.iddp_nomdoua                          AS Article_1_35,
     Article.iddp_pays                             AS Article_1_37,
     Article.iddp_recept                           AS Article_1_40,
     Article.idda_unalvte                          AS Article_1_42,
     Article.iddb_sitecl                          AS Article_1_43,
     Article.lib_caisse                            AS Article_1_49,
     Article.lib_com                               AS Article_1_50,
     Article.maj_en_attente                        AS Article_1_61,
     Article.qte_stk                               AS Article_1_63,
     Article.ref_tech                              AS Article_1_64,
     1                                             AS Article_1_70,
     CASE
       WHEN (SELECT COUNT(MA.id)
             FROM   da_majart MA
                    join da_majmas MM
                      ON MM.id = MA.idda_majmas
                    join gt_tmtprg TMT
                      ON TMT.id = MM.idgt_tmtprg
                    join gt_prog PROG
                      ON PROG.id = TMT.idgt_prog
             WHERE  idda_article = Article.id
                AND TO_DATE(TO_CHAR(PROG.date_lancement, 'DDMMYYYY')
                          || TO_CHAR(PROG.heure_lancement, ' HH24:MI:SS'),
                          'DDMMYYYY HH24:MI:SS') >= SYSDATE) >= 1 THEN 1
       ELSE 0
     END                                           AS Article_1_74,
     Article.iddp_compnat                          AS Article_2_0,
     Article.iddp_modven                           AS Article_2_1,
     Article.iddp_nature                           AS Article_2_2,
     Article.iddp_preclin                          AS Article_2_3,
     Article.iddp_raybala                          AS Article_2_4,
     Article.iddp_sensgrt                          AS Article_2_5,
     Article.iddp_tcdtfl                           AS Article_2_6,
     Article.iddp_unite                            AS Article_2_8,
     Article.idda_untgrat                          AS Article_2_9,
     Article.idda_unpoids                          AS Article_2_10,
     Article.iddp_unilogi                          AS Article_2_11,
     ArticleComplement.datem                       AS ArticleComplement_5_6,
     ArticleComplement.extgar_depl                 AS ArticleComplement_5_9,
     ArticleComplement.extgar_mo                   AS ArticleComplement_5_10,
     ArticleComplement.extgar_piece                AS ArticleComplement_5_11,
     ArticleComplement.id                          AS ArticleComplement_5_20,
     ArticleComplement.iddf_collect                AS ArticleComplement_5_22,
     ArticleComplement.iddp_gpdtcul                AS ArticleComplement_5_23,
     ArticleComplement.iddp_support                AS ArticleComplement_5_25,
     ArticleComplement.iddp_typcarb                AS ArticleComplement_5_27,
     ArticleComplement.mt_ext_gar                  AS ArticleComplement_5_36,
     ArticleComplement.pres_cpt                    AS ArticleComplement_5_44,
     GenreProduitCulturel.code                     AS GenreProduitCulturel_6_0,
     Collection.libelle                            AS Collection_8_1,
     Gtin.date_dern_vte                            AS Gtin_10_0,
     Gtin.gtin                                     AS Gtin_10_1,
     Gtin.id                                       AS Gtin_10_3,
     Fabricant.code                                AS Fabricant_14_0,
     Fabricant.nom                                 AS Fabricant_14_2,
     ClassificationVenteLocale.niveau1             AS ClassificationVenteL_16_2,
     ClassificationVenteLocale.niveau2             AS ClassificationVenteL_16_3,
     ClassificationVenteLocale.niveau3             AS ClassificationVenteL_16_4,
     ClassificationVenteLocale.niveau4             AS ClassificationVenteL_16_5,
     MarqueCommerciale.code                        AS MarqueCommerciale_18_0,
     MarqueCommerciale.libellelong                 AS MarqueCommerciale_18_4,
     Composition.code                              AS Composition_20_0,
     CompositionTextile.code                       AS CompositionTextile_21_0,
     AssoArticleInterfaceBalance.datem             AS AssoArticleInterface_23_0,
     AssoArticleInterfaceBalance.lib_envoi         AS AssoArticleInterface_23_3,
     AssoArticleInterfaceCaisse.datem              AS AssoArticleInterface_24_0,
     AssoArticleInterfaceCaisse.lib_envoi          AS AssoArticleInterface_24_3,
     NULL                                          AS TypeTraitement_25_0,
     NULL                                          AS TypeTraitement_25_1,
     RayonBalance.code                             AS RayonBalance_31_0,
     RayonBalance.max_cde_article                  AS RayonBalance_31_5,
     RayonBalance.min_cde_article                  AS RayonBalance_31_6,
     TypeTare.code                                 AS TypeTare_32_0,
     GrilleDePrix.datem                            AS GrilleDePrix_34_1,
     GrilleDePrix.libelle                          AS GrilleDePrix_34_3,
     FicheAgreage.code                             AS FicheAgreage_38_0,
     Codelec.iddp_periact                          AS Codelec_40_1,
     Codelec.libelle                               AS Codelec_40_2,
     Codelec.niveau1                               AS Codelec_40_3,
     Codelec.niveau2                               AS Codelec_40_4,
     Codelec.niveau3                               AS Codelec_40_5,
     Codelec.niveau4                               AS Codelec_40_6,
     PerimetreActivite.code                        AS PerimetreActivite_41_0,
     DonneesPersonnalisablesCodelec.gestionreftech AS DonneesPersonnalisab_42_0,
     ClassificationArticleInterne.id               AS ClassificationArticl_43_0,
     ClassificationArticleInterne.niveau1          AS ClassificationArticl_43_2,
     DossierCommercial.id                          AS DossierCommercial_52_0,
     DossierCommercial.codefourndc                 AS DossierCommercial_52_1,
     DossierCommercial.anneedc                     AS DossierCommercial_52_3,
     DossierCommercial.codeclassdc                 AS DossierCommercial_52_4,
     DossierCommercial.numversiondc                AS DossierCommercial_52_5,
     DossierCommercial.indice                      AS DossierCommercial_52_6,
     DossierCommercial.code_ss_classement          AS DossierCommercial_52_7,
     OrigineNegociation.code                       AS OrigineNegociation_53_0,
     MotifBlocageInformation.libellelong           AS MotifBlocageInformat_54_3,
     ArbreLogistique.id                            AS ArbreLogistique_63_1,
     ArbreLogistique.codesap                       AS ArbreLogistique_63_5,
     Fournisseur.code                              AS Fournisseur_66_0,
     Fournisseur.nom                               AS Fournisseur_66_2,
     Filiere.code                                  AS Filiere_67_0,
     Filiere.nom                                   AS Filiere_67_2,
     ValorisationAchat.val_ach_patc                AS Valorisation_74_3,
     LienPrixVente.code                            AS LienPrixVente_76_0,
     LienPrixVente.datem                           AS LienPrixVente_76_1,
     LienGratuite.code                             AS LienGratuite_78_0,
     LienGratuite.datem                            AS LienGratuite_78_1,
     LienCoordonnable.code                         AS LienCoordonnable_79_0,
     LienCoordonnable.datem                        AS LienCoordonnable_79_1,
     LienStatistique.code                          AS LienStatistique_81_0,
     LienStatistique.datem                         AS LienStatistique_81_1
FROM   da_article Article
       join (SELECT idarticle,
                    poids,
                    ROW_NUMBER()
                      over (
                        PARTITION BY RNA.id
                        ORDER BY INNERSEARCH.poids) RN,
                    titre,
                    nom,
                    prenom
             FROM   da_article RNA
                    join (SELECT idarticle,
                       pkg_db_indexation.CALCULPOIDSMOTS(chaine, 'foire vins%')
                        AS POIDS,
                       DECODE(index_clerecherche, 'Piste.titre', chaine,
                                                        '')        AS TITRE,
                       DECODE(index_clerecherche, 'Artiste.nom_prenom',
                        SUBSTR(chaine, 0, INSTR(chaine, '_') - 1),
                                                        '')        AS NOM,
                       DECODE(index_clerecherche, 'Artiste.nom_prenom',
                        SUBSTR(chaine, INSTR(chaine, '_') + 1),
                                                        '')        AS PRENOM
                      FROM   ((SELECT index_idenreg AS IDARTICLE,
                                      C.cde_art     AS CHAINE,
                                      index_clerecherche
                               FROM   cstd_mots M
                                      join cstd_index I
                                        ON I.mots_id = M.mots_id
                                           AND index_clerecherche =
                                             'Article.codeArticle'
                                      join da_article C
                                        ON id = index_idenreg
                               WHERE  mots_mot = 'foire'
                               INTERSECT
                               SELECT index_idenreg AS IDARTICLE,
                                      C.cde_art     AS CHAINE,
                                      index_clerecherche
                               FROM   cstd_mots M
                                      join cstd_index I
                                        ON I.mots_id = M.mots_id
                                           AND index_clerecherche =
                                            'Article.codeArticle'
                                      join da_article C
                                        ON id = index_idenreg
                               WHERE  mots_mot LIKE 'vins%'
                                  AND 1 = 1)
                              UNION ALL
                              (SELECT index_idenreg AS IDARTICLE,
                                      C.cde_art_bal AS CHAINE,
                                      index_clerecherche
                               FROM   cstd_mots M
                                      join cstd_index I
                                        ON I.mots_id = M.mots_id
                                           AND index_clerecherche =
                                            'Article.codeArticleBalance'
                                      join da_article C
                                        ON id = index_idenreg
                               WHERE  mots_mot = 'foire'
                               INTERSECT
                               SELECT index_idenreg AS IDARTICLE,
                                      C.cde_art_bal AS CHAINE,
                                      index_clerecherche
                               FROM   cstd_mots M
                                      join cstd_index I
                                        ON I.mots_id = M.mots_id
                                           AND index_clerecherche =
                                            'Article.codeArticleBalance'
                                      join da_article C
                                        ON id = index_idenreg
                               WHERE  mots_mot LIKE 'vins%'
                                  AND 1 = 1)
                              UNION ALL
                              (SELECT index_idenreg AS IDARTICLE,
                                      C.lib_com     AS CHAINE,
                                      index_clerecherche
                               FROM   cstd_mots M
                                      join cstd_index I
                                        ON I.mots_id = M.mots_id
                                           AND index_clerecherche =
                                            'Article.libelleCommercial'
                                      join da_article C
                                        ON id = index_idenreg
                               WHERE  mots_mot = 'foire'
                               INTERSECT
                               SELECT index_idenreg AS IDARTICLE,
                                      C.lib_com     AS CHAINE,
                                      index_clerecherche
                               FROM   cstd_mots M
                                      join cstd_index I
                                        ON I.mots_id = M.mots_id
                                           AND index_clerecherche =
                                            'Article.libelleCommercial'
                                      join da_article C
                                        ON id = index_idenreg
                               WHERE  mots_mot LIKE 'vins%'
                                  AND 1 = 1)
                              UNION ALL
                              (SELECT idda_article AS IDARTICLE,
                                      C.gtin       AS CHAINE,
                                      index_clerecherche
                               FROM   cstd_mots M
                                      join cstd_index I
                                        ON I.mots_id = M.mots_id
                                           AND index_clerecherche = 'Gtin.gtin'
                                      join da_gtin C
                                        ON id = index_idenreg
                               WHERE  mots_mot = 'foire'
                               INTERSECT
                               SELECT idda_article AS IDARTICLE,
                                      C.gtin       AS CHAINE,
                                      index_clerecherche
                               FROM   cstd_mots M
                                      join cstd_index I
                                        ON I.mots_id = M.mots_id
                                           AND index_clerecherche = 'Gtin.gtin'
                                      join da_gtin C
                                        ON id = index_idenreg
                               WHERE  mots_mot LIKE 'vins%'
                                  AND 1 = 1)
                              UNION ALL
                              (SELECT idda_article AS IDARTICLE,
                                      C.ref_frn    AS CHAINE,
                                      index_clerecherche
                               FROM   cstd_mots M
                                      join cstd_index I
                                        ON I.mots_id = M.mots_id
                                           AND index_clerecherche =
                                          'ArbreLogistique.referenceFournisseur'
                                      join da_arblogi C
                                        ON id = index_idenreg
                               WHERE  mots_mot = 'foire'
                               INTERSECT
                               SELECT idda_article AS IDARTICLE,
                                      C.ref_frn    AS CHAINE,
                                      index_clerecherche
                               FROM   cstd_mots M
                                      join cstd_index I
                                        ON I.mots_id = M.mots_id
                                           AND index_clerecherche =
                                          'ArbreLogistique.referenceFournisseur'
                                      join da_arblogi C
                                        ON id = index_idenreg
                               WHERE  mots_mot LIKE 'vins%'
                                  AND 1 = 1))) INNERSEARCH
                      ON INNERSEARCH.idarticle = RNA.id) SEARCHMC
         ON SEARCHMC.idarticle = Article.id
            AND 1 = 1
       left join da_artcmpl ArticleComplement
              ON Article.id = ArticleComplement.idda_article
       left join dp_gpdtcul GenreProduitCulturel
              ON ArticleComplement.iddp_gpdtcul = GenreProduitCulturel.id
       left join df_collect Collection
              ON ArticleComplement.iddf_collect = Collection.id
       left join da_gtin Gtin
              ON Article.id = Gtin.idda_article
                 AND Gtin.principal = 1
                 AND Gtin.db_suplog = 0
       left join df_fabric Fabricant
              ON Article.iddf_fabric = Fabricant.id
       left join dp_clsvtel ClassificationVenteLocale
              ON Article.iddp_clsvtel = ClassificationVenteLocale.id
       left join dp_marqcom MarqueCommerciale
              ON Article.iddp_marqcom = MarqueCommerciale.id
       left join da_compo Composition
              ON Composition.id = Article.idda_compo
       left join dp_comptex CompositionTextile
              ON CompositionTextile.id = Article.iddp_comptex
       left join da_arttrai AssoArticleInterfaceBalance
              ON AssoArticleInterfaceBalance.idda_article = Article.id
                 AND AssoArticleInterfaceBalance.iddp_tinterf = 1
       left join da_arttrai AssoArticleInterfaceCaisse
              ON AssoArticleInterfaceCaisse.idda_article = Article.id
                 AND AssoArticleInterfaceCaisse.iddp_tinterf = 4
       left join dp_raybala RayonBalance
              ON Article.iddp_raybala = RayonBalance.id
       left join dp_valdico TypeTare
              ON TypeTare.id = RayonBalance.iddp_typtare
       left join df_categor Categorie
              ON Categorie.id = Article.iddf_categor
       left join df_grille GrilleDePrix
              ON GrilleDePrix.id = Categorie.iddf_grille
       left join dp_agreage FicheAgreage
              ON FicheAgreage.id = Article.iddp_agreage
       join dp_codelec Codelec
         ON Article.iddp_codelec = Codelec.id
       left join dp_periact PerimetreActivite
              ON PerimetreActivite.id = Codelec.iddp_periact
       left join dp_perscod DonneesPersonnalisablesCodelec
              ON Codelec.id = DonneesPersonnalisablesCodelec.iddp_codelec
                 AND DonneesPersonnalisablesCodelec.db_suplog = 0
                 AND DonneesPersonnalisablesCodelec.iddb_sitecl = 1012124
       left join dp_clsart ClassificationArticleInterne
              ON DonneesPersonnalisablesCodelec.iddp_clsart =
                ClassificationArticleInterne.id
       left join da_artdeno ArticleDenormalise
              ON Article.id = ArticleDenormalise.idda_article
       left join df_clasmnt ClassementFournisseur
              ON ArticleDenormalise.iddf_clasmnt = ClassementFournisseur.id
       left join tr_dosclas DossierDeClassement
              ON ClassementFournisseur.id = DossierDeClassement.iddf_clasmnt
                 AND DossierDeClassement.date_deb <= '2013-09-27'
                 AND COALESCE(DossierDeClassement.date_fin,
                    TO_DATE('31129999', 'DDMMYYYY')) >= '2013-09-27'
       left join tr_doscomm DossierCommercial
              ON DossierDeClassement.idtr_doscomm = DossierCommercial.id
       left join dp_valdico OrigineNegociation
              ON DossierCommercial.iddp_dossref = OrigineNegociation.id
       left join dp_motbloc MotifBlocageInformation
              ON MotifBlocageInformation.id = ArticleDenormalise.idda_motinf
       left join da_arblogi ArbreLogistique
              ON Article.id = ArbreLogistique.idda_article
                 AND ArbreLogistique.princ = 1
                 AND ArbreLogistique.db_suplog = 0
       left join df_filiere Filiere
              ON ArbreLogistique.iddf_filiere = Filiere.id
       left join df_fourn Fournisseur
              ON Filiere.iddf_fourn = Fournisseur.id
       left join od_dosal dossierALValo
              ON dossierALValo.idda_arblogi = ArbreLogistique.id
                 AND dossierALValo.idod_dossier IS NULL
       left join tt_val_dal valoDossier
              ON valoDossier.idod_dosal = dossierALValo.id
                 AND valoDossier.estarecalculer = 0
       left join tt_valo ValorisationAchat
              ON ValorisationAchat.idtt_val_dal = valoDossier.id
                 AND ValorisationAchat.date_modif_retro IS NULL
                 AND ValorisationAchat.date_debut_achat <= '2013-09-27'
                 AND COALESCE(ValorisationAchat.date_fin_achat,
                    TO_DATE('31129999', 'DDMMYYYY')) >= '2013-09-27'
                 AND ValorisationAchat.val_ach_pab IS NOT NULL
       left join da_lienart assoALPXVT
              ON assoALPXVT.idda_article = Article.id
                 AND assoALPXVT.iddp_typlien = 14893
       left join da_lien LienPrixVente
              ON LienPrixVente.id = assoALPXVT.idda_lien
       left join da_lienart assoALGRAT
              ON assoALGRAT.idda_article = Article.id
                 AND assoALGRAT.iddp_typlien = 14894
       left join da_lien LienGratuite
              ON LienGratuite.id = assoALGRAT.idda_lien
       left join da_lienart assoALCOOR
              ON assoALCOOR.idda_article = Article.id
                 AND assoALCOOR.iddp_typlien = 14899
       left join da_lien LienCoordonnable
              ON LienCoordonnable.id = assoALCOOR.idda_lien
       left join da_lienal assoALSTAT
              ON assoALSTAT.idda_arblogi = ArbreLogistique.id
                 AND assoALSTAT.iddp_typlien = 14897
       left join da_lien LienStatistique
              ON LienStatistique.id = assoALSTAT.idda_lien WHERE
SEARCHMC.rn = 1
   AND ( ValorisationAchat.id IS NULL
          OR ValorisationAchat.date_debut_achat = (
                     SELECT MAX(VALMAX.date_debut_achat)
                     FROM   tt_valo VALMAX
                     WHERE  VALMAX.idtt_val_dal = ValorisationAchat.idtt_val_dal
                        AND VALMAX.date_modif_retro IS NULL
                        AND VALMAX.val_ach_pab IS NOT NULL
                        AND VALMAX.date_debut_achat <= '2013-09-27') )
   AND ( Article.id IN (SELECT A.id
                        FROM   da_article A
                               join du_ucutiar AssoUcUtiAr
                                 ON AssoUcUtiAr.idda_article = A.id
                               join du_asucuti AssoUcUti
                                 ON AssoUcUti.id = AssoUcUtiAr.iddu_asucuti
                        WHERE  ( AssoUcUti.iddu_uti IN ( 90000000000022 ) )
                           AND a.iddb_sitecl = 1012124) )
   AND Article.db_suplog = 0
ORDER  BY SEARCHMC.poids ASC

Ce code a été généré initialement par Hibernate, puis édité plusieurs fois à la main.

Les seules sous-requêtes sans danger sont celles qui retournent un ensemble constant et ne sont exécutés qu'une fois, ou celles qui expriment un Semi-Join (test d'existence) ou Anti-Join (test de non-existence), qui sont presque des jointures : la seule différence est qu'elles ne récupèrent pas l'enregistrement de la table cible.

Attention toutefois à l'utilisation du prédicat NOT IN, ils peuvent générer des plans d'exécution catastrophiques :

tpc=# EXPLAIN SELECT *
  FROM commandes
 WHERE numero_commande NOT IN (SELECT numero_commande
                             FROM lignes_commandes);
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..1196748507.51 rows=84375 width=77)
   Workers Planned: 1
   ->  Parallel Seq Scan on commandes  (cost=0.00..1196739070.01 rows=49632 width=77)
         Filter: (NOT (SubPlan 1))
         SubPlan 1
           ->  Materialize  (cost=0.00..22423.15 rows=675543 width=8)
                 ->  Seq Scan on lignes_commandes  (cost=0.00..16406.43 rows=675543 width=8)
(7 rows)

Time: 0.460 ms

La requête suivante est strictement équivalente et produit un plan d'exécution largement plus intéressant :

tpc=# EXPLAIN SELECT *
  FROM commandes
 WHERE NOT EXISTS (SELECT 1
                     FROM lignes_commandes l
                    WHERE l.numero_commande = commandes.numero_commande);
                              QUERY PLAN
-----------------------------------------------------------------------
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Gather  (cost=28489.72..43299.60 rows=25860 width=77)
   Workers Planned: 1
   ->  Hash Anti Join  (cost=27489.72..39713.60 rows=15212 width=77)
         Hash Cond: (commandes.numero_commande = l.numero_commande)
         ->  Parallel Seq Scan on commandes  (cost=0.00..3403.65 rows=99265 width=77)
         ->  Hash  (cost=16406.43..16406.43 rows=675543 width=8)
               ->  Seq Scan on lignes_commandes l  (cost=0.00..16406.43 rows=675543 width=8)
(7 rows)

La raison, c'est que si un NULL est présent dans la liste du NOT IN, NOT IN vaut systématiquement FALSE. Nous, nous savons qu'il n'y aura pas de numero_commandes à NULL.

Dans les captures réseau ci-dessous, le serveur est sur le port 5932.

SELECT * FROM test, 0 enregistrement :

10:57:15.087777 IP 127.0.0.1.39508 > 127.0.0.1.5932:
    Flags [P.], seq 109:134, ack 226, win 350,
    options [nop,nop,TS val 2270307 ecr 2269578], length 25
10:57:15.088130 IP 127.0.0.1.5932 > 127.0.0.1.39508:
    Flags [P.], seq 226:273, ack 134, win 342,
    options [nop,nop,TS val 2270307 ecr 2270307], length 47
10:57:15.088144 IP 127.0.0.1.39508 > 127.0.0.1.5932:
    Flags [.], ack 273, win 350,
    options [nop,nop,TS val 2270307 ecr 2270307], length 0

SELECT * FROM test, 1000 enregistrements :

10:58:08.542660 IP 127.0.0.1.39508 > 127.0.0.1.5932:
    Flags [P.], seq 188:213, ack 298, win 350,
    options [nop,nop,TS val 2286344 ecr 2285513], length 25
10:58:08.543281 IP 127.0.0.1.5932 > 127.0.0.1.39508:
    Flags [P.], seq 298:8490, ack 213, win 342,
    options [nop,nop,TS val 2286344 ecr 2286344], length 8192
10:58:08.543299 IP 127.0.0.1.39508 > 127.0.0.1.5932:
    Flags [.], ack 8490, win 1002,
    options [nop,nop,TS val 2286344 ecr 2286344], length 0
10:58:08.543673 IP 127.0.0.1.5932 > 127.0.0.1.39508:
    Flags [P.], seq 8490:14241, ack 213, win 342,
    options [nop,nop,TS val 2286344 ecr 2286344], length 5751
10:58:08.543682 IP 127.0.0.1.39508 > 127.0.0.1.5932:
    Flags [.], ack 14241, win 1012,
    options [nop,nop,TS val 2286344 ecr 2286344], length 0

Un client JDBC va habituellement utiliser un aller/retour de plus, en raison des requêtes préparées : un dialogue pour envoyer la requête et la préparer, et un autre pour envoyer les paramètres. Le problème est la latence du réseau, habituellement : de 50 à 300μs. Cela limite à 3 000 à 20 000 le nombre d'opérations maximum par seconde par socket. On peut bien sûr paralléliser sur plusieurs sessions, mais cela complique le traitement.

En ce qui concerne le parser : comme indiqué dans ce message : gram.o, le parser fait 1Mo une fois compilé !

PostgreSQL fournit un outil de benchmark synthétique. Voici les résultats :

Option -C : se connecter à chaque requête :

$ pgbench pgbench -T 20 -c 10 -j5 -S -C
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 2
query mode: simple
number of clients: 10
number of threads: 5
duration: 20 s
number of transactions actually processed: 16972
latency average = 11.787 ms
tps = 848.383850 (including connections establishing)
tps = 1531.057609 (excluding connections establishing)

Sans se reconnecter à chaque requête :

$ pgbench pgbench -T 20 -c 10 -j5 -S
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 2
query mode: simple
number of clients: 10
number of threads: 5
duration: 20 s
number of transactions actually processed: 773963
latency average = 0.258 ms
tps = 38687.524110 (including connections establishing)
tps = 38703.239556 (excluding connections establishing)

On passe de 900 à 20 000 transactions par seconde.

Si les spécifications disent (version simplifiée bien sûr) :

• Vérifier la présence du client
  • S'il est présent, mettre à jour son adresse
  • Sinon, créer le client avec la bonne adresse

Peut être écrit (pseudo-code client) :

SELECT count(*) from clients where client_name = 'xxx' INTO compte
IF compte > 0
  UPDATE clients set adresse='yyy' WHERE client_name='xxx'
ELSE
  INSERT client SET client_name='xxx', adresse='yyy'
END IF

3 requêtes, systématiquement 2 appels à la base.

On peut très bien l'écrire comme suit :

UPDATE clients set adresse='yyy' WHERE client_name='xxx'
IF NOT FOUND
  INSERT client SET client_name='xxx', adresse='yyy'
END IF

Dans ce cas, on n'aura 2 requêtes que dans le plus mauvais cas. Bien sûr, cet exemple est simpliste.

On peut aussi, grâce aux CTE, réaliser tout cela en un seul ordre SQL, qui ressemblerait à :

WITH
  enregistrements_a_traiter AS (
    SELECT * FROM (VALUES ('toto' , 'adresse1' ),('tata','adresse2'))
    AS val(nom_client,adresse)
  ),
  mise_a_jour AS (
    UPDATE client SET adresse=enregistrements_a_traiter.adresse
    FROM enregistrements_a_traiter
    WHERE enregistrements_a_traiter.nom_client=client.nom_client
    RETURNING client.nom_client
  )
INSERT INTO client (nom_client,adresse)
  SELECT nom_client,adresse from enregistrements_a_traiter
  WHERE NOT EXISTS (
    SELECT 1 FROM mise_a_jour
    WHERE mise_a_jour.nom_client=enregistrements_a_traiter.nom_client
  );

Plus d'information sur la fusion des enregistrements dans PostgreSQL avec des CTE.

Si on reprend l'exemple précédent, il aurait pu être écrit :

=> CREATE TEMP TABLE temp_a_inserer  (nom_client text, adresse text);
=> INSERT INTO temp_a_inserer SELECT * FROM (VALUES ('toto' , 'adresse1' ),
               ('tata','adresse2')) AS tmp;
=> UPDATE client SET adresse=temp_a_inserer.adresse
   FROM temp_a_inserer
   WHERE temp_a_inserer.nom_client=client.nom_client;
=> INSERT INTO client (nom_client,adresse)
      SELECT nom_client,adresse from temp_a_inserer
      WHERE NOT EXISTS (
          SELECT 1 FROM client
          WHERE client.nom_client=temp_a_inserer.nom_client);
=> DROP TABLE temp_a_inserer;

1000 exécutions de cette méthode prennent 5 secondes, alors que la solution précédente ne dure que 500ms.

L'avantage du code SQL est, encore une fois, qu'il est déclaratif. Il aura donc de nombreux avantages sur un code procédural.

L'exécution évoluera pour prendre en compte les variations de volumétrie des différentes tables.
Les optimiseurs sont la partie la plus importante d'un moteur SQL. Ils progressent en permanence. Chaque nouvelle version va donc potentiellement améliorer vos performances.

Si vous écrivez du procédural avec des appels unitaires à la base dans des boucles, le moteur ne pourra rien optimiser

Si vous faites vos tris ou regroupements côté client, vous êtes limités aux algorithmes fournis par vos langages, voire à ceux que vous aurez écrit manuellement. Une base de données bascule automatiquement entre une dizaine d'algorithmes différents suivant le volume, le type de données à trier, ce pour quoi le tri est ensuite utilisé, etc., voire évite de trier en utilisant des tables de hachage ou des index disponibles.

Toujours coder les accès aux données pour que la base fasse le maximum de traitement, mais uniquement les traitements nécessaires : l'accès aux données est coûteux, il faut l'optimiser. Et le gros des pièges peut être évité avec les quelques règles d'« hygiène » simples qui viennent d'être énoncées.

Le site Use the index, Luke, maintenu par Markus Winand, propose une version en ligne de son livre SQL Performance Explained. Une version française est par ailleurs disponible sous le titre SQL : au cœur des performances.

Les index ne sont pas des objets qui font partie de la théorie relationnelle. Ils sont des objets physiques qui permettent d'accélérer l'accès aux données. Et comme ils ne sont que des moyens d'optimisation des accès, les index ne font pas non plus partie de la norme SQL. C'est d'ailleurs pour cette raison que la syntaxe de création d'index est si différente d'une base de données à une autre.

PostgreSQL propose différentes formes d'index :

• index classique sur une seule colonne d'une table ;
• index composite sur plusieurs colonnes d'une table ;
• index partiel, en restreignant les données indexées avec une clause WHERE ;
• index fonctionnel, en indexant le résultat d'une fonction appliquée à une ou plusieurs colonnes d'une table.

L'index est une structure de données qui permet d'accéder rapidement à l'information recherchée. À l'image de l'index d'un livre, pour retrouver un thème rapidement, on préférera utiliser l'index du livre plutôt que lire l'intégralité du livre jusqu'à trouver le passage qui nous intéresse. Dans une base de données, l'index a un rôle équivalent. Plutôt que de lire une table dans son intégralité, la base de données utilisera l'index pour ne lire qu'une faible portion de la table pour retrouver les données recherchées.

Pour la requête d'exemple (avec une table de 20 millions de lignes), on remarque que l'optimiseur n'utilise pas le même chemin selon que l'index soit présent ou non. Sans index, PostgreSQL réalise un parcours séquentiel de la table :

postgres=# EXPLAIN SELECT * FROM t1 WHERE i = 10000;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Gather  (cost=1000.00..193661.66 rows=1 width=4)
   Workers Planned: 2
   ->  Parallel Seq Scan on t1  (cost=0.00..192661.56 rows=1 width=4)
         Filter: (i = 10000)
(4 rows)

Lorsqu'il est présent, PostgreSQL l'utilise car l'optimiseur estime que son parcours ne récupèrera qu'une seule ligne sur les 10 millions que compte la table :

postgres=# EXPLAIN SELECT * FROM t1 WHERE i = 10000;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Index Only Scan using t1_i_idx on t1  (cost=0.44..8.46 rows=1 width=4)
   Index Cond: (i = 10000)
(2 rows)

Mais l'index n'accélère pas seulement la simple lecture de données, il permet également d'accélérer les tris et les agrégations, comme le montre l'exemple suivant sur un tri :

postgres=# EXPLAIN SELECT * FROM t1
WHERE i BETWEEN 1000 AND 1200 ORDER BY i DESC;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Index Only Scan Backward using t1_i_idx on t1  (cost=0.44..12.26 rows=191 width=4)
   Index Cond: ((i >= 1000) AND (i <= 1200))
(2 rows)

La présence d'un index ralentit les écritures sur une table. En effet, il faut non seulement ajouter ou modifier les données dans la table, mais il faut également maintenir le ou les index de cette table.

Les index dégradent surtout les temps de réponse des insertions. Les mises à jour et les suppressions (UPDATE et DELETE) tirent en général parti des index pour retrouver les lignes concernées par les modifications. Le coût de maintenance de l'index est secondaire par rapport au coût de l'accès aux données.

Soit une table t2 telle que :

CREATE TABLE t2 (
    id SERIAL PRIMARY KEY,
    valeur INTEGER,
    commentaire TEXT
);

La table est chargée avec pour seul index présent celui sur la clé primaire :

=# INSERT INTO t2 (valeur, commentaire)
   SELECT i, 'commentaire ' || i FROM generate_series(1, 10000000) i;
INSERT 0 10000000
Temps : 148299,717 ms

Un index supplémentaire est créé sur une colonne de type entier :

=# CREATE INDEX idx_t2_valeur ON t2 (valeur);
=# INSERT INTO t2 (valeur, commentaire)
   SELECT i, 'commentaire ' || i FROM generate_series(1, 10000000) i;
INSERT 0 10000000
Temps : 195933,515 ms

Un index supplémentaire est encore créé, mais cette fois sur une colonne de type texte :

=# CREATE INDEX idx_t2_commentaire ON t2 (commentaire);
=# INSERT INTO t2 (valeur, commentaire)
   SELECT i, 'commentaire ' || i FROM generate_series(1, 10000000) i;
INSERT 0 10000000
Temps : 422662,401 ms

On peut comparer ces temps à l'insertion dans une table similaire dépourvue d'index :

=# CREATE TABLE t3 AS SELECT * FROM t2;
=# INSERT INTO t3 (valeur, commentaire)
   SELECT i, 'commentaire ' || i FROM generate_series(1, 10000000) i;
INSERT 0 10000000
Temps : 55945,787 ms

La table t2 a été vidée préalablement pour chaque test.

Toutes les requêtes ne sont pas critiques, seul un certain nombre d'entre elles méritent une attention particulière.

Il y a deux façon de déterminer les requêtes qui nécessitent d'être travaillées. La première dépend du ressenti utilisateur, il faudra en priorité traiter les requêtes interactives. Certaines auront déjà d'excellents temps de réponse, d'autres pourront être améliorées encore. Il faudra déterminer non seulement le temps de réponse maximal attendu pour une requête, mais vérifier aussi le temps total de réponse de l'application.

L'autre méthode pour déterminer les requêtes à optimiser consiste à utiliser des outils de profiling habituels (pgBadger, pg_stat_statements, pg_ stat_plans). Ces outils permettront de déterminer les requêtes les plus fréquemment exécutées et permettront d'établir un classement des requêtes qui ont nécessité le plus de temps cumulé à leur exécution (voir onglet Time consuming queries (N) d'un rapport pgBadger). Les requêtes les plus fréquemment exécutées méritent également qu'on leur porte attention, leur optimisation peut permettre d'économiser quelques ressources du serveur.

En utilisant l'extension pg_stat_statements, la requête suivante permet de déterminer les requêtes dont les temps d'exécution cumulés sont les plus importants :

SELECT r.rolname, d.datname, s.calls, s.total_time,
       s.calls / s.total_time 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_time DESC
 LIMIT 10;

Toujours avec pg_stat_statements, la requête suivante permet de déterminer les requêtes les plus fréquemment appelées :

SELECT r.rolname, d.datname, s.calls, s.total_time,
       s.calls / s.total_time 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;

PostgreSQL propose plusieurs types d'index :

• les classiques B-Tree
• les index hash
• les index spécialisés GIN et GiST
• les index BRIN

Les index B-Tree sont les index les plus couramment utilisés, pour assurer des contraintes d'unicité ou simplement pour assurer les recherches avec les opérateurs de comparaisons habituels.

Les index hash sont au contraire très peu utilisés. Ils n'étaient pas journalisées jusqu'en version 10. Avant cette version, ils étaient souvent corrompus à chaque arrêt brutal du serveur, et jamais répliqués.

Ces deux types d'index permettent d'indexer des valeurs scalaires.

Les index de type GiST sont des structures d'index généralisés. Ce sont des arbres B+Tree, qui autorisent un développeur, connaissant bien un type de données particulier, à créer ses propres méthodes d'accès aux données. Les index de type GIN sont de la famille de index inversés.

On retrouve ces deux types d'index associés à différentes fonctionnalités de PostgreSQL. La recherche plein texte (full text search) est la plus connue. Grâce à ces types d'index, il est aussi possible d'indexer les types de données range.

Ainsi, l'extension pg_trgm s'appuie sur des trigrammes pour répondre aux requêtes de type LIKE '%motif%', qui ne peuvent bénéficier d'optimisation avec des index traditionnels :

base=# CREATE INDEX idx2 ON mots USING gist (mot gist_trgm_ops);
CREATE INDEX
base=# EXPLAIN ANALYZE SELECT * FROM mots WHERE mot LIKE '%jour%';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on mots  (cost=7.41..277.32 rows=75 width=10)
                           (actual time=50.362..50.677 rows=352 loops=1)
   Recheck Cond: (mot ~~ '%jour%'::text)
   ->  Bitmap Index Scan on idx2  (cost=0.00..7.39 rows=75 width=0)
                                  (actual time=50.322..50.322 rows=352 loops=1)
         Index Cond: (mot ~~ '%jour%'::text)
 Total runtime: 51.226 ms

Plus d'information sur :

• les index GiST
• les index GIN

Un index BRIN stocke un « résumé » d'un ensemble de blocs. Cela réduit la taille de l'index et permet d'exclure un ensemble de blocs lors d'une recherche.

Cible :

• fortes volumétries, big data ;
• index classiques volumineux.

Plus d'information sur les index BRIN.

Lorsqu'une fonction est appliquée à la valeur d'une colonne dans un prédicat, l'optimiseur ne sait pas utiliser un index normal pour répondre rapidement à une telle requête. Il utilisera donc un parcours séquentiel de la table, pour appliquer la fonction à toutes les valeurs de la colonne à laquelle elle s'applique, afin de vérifier le prédicat. Cela aura des conséquences désastreuses sur les temps de réponse : non seulement le parcours séquentiel peut être long, mais la fonction sera exécutée autant de fois qu'il y a de lignes dans la table, ajoutant encore un délai important au temps de réponse, ainsi qu'une énorme consommation de ressources CPU.

Ce problème apparaît souvent sur des manipulations de dates. En général, il est résolu en plaçant la transformation du côté de la constante. Par exemple, la requête suivante retourne toutes les commandes de l'année 2011, mais la fonction extract est appliquée à la colonne date_commande. L'optimiseur ne peut donc utiliser un index :

tpc=# EXPLAIN SELECT * FROM commandes
WHERE extract('year' from date_commande) = 2011;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Seq Scan on commandes  (cost=0.00..5364.12 rows=844 width=77)
   Filter: (date_part('year'::text,
     (date_commande)::timestamp without time zone) = 2011::double precision)

En réécrivant le prédicat, l'index est bien utilisé :

tpc=# EXPLAIN SELECT * FROM commandes
WHERE date_commande BETWEEN '01-01-2011'::date AND '31-12-2011'::date;
                              QUERY PLAN
--------------------------------------------------------------------------
 Bitmap Heap Scan on commandes  (cost=523.85..3302.80 rows=24530 width=77)
   Recheck Cond: ((date_commande >= '2011-01-01'::date)
                  AND (date_commande <= '2011-12-31'::date))
   ->  Bitmap Index Scan on idx_commandes_date_commande
            (cost=0.00..517.72 rows=24530 width=0)
         Index Cond: ((date_commande >= '2011-01-01'::date)
                      AND (date_commande <= '2011-12-31'::date))

Mais dans d'autres cas, une telle réécriture de la requête sera impossible. PostgreSQL permet d'indexer le résultat d'une fonction appliquée à une ou plusieurs colonnes d'une même table. Au lieu d'indexer une simple colonne, on indexera le résultat de la fonction, par exemple :

CREATE INDEX index ON table(f(colonne))

La seule contrainte est que la fonction indexée doit être catégorisée IMMUTABLE, indiquant que la fonction retournera toujours le même résultat quand elle est appelée avec les mêmes arguments (elle ne dépend pas du contenu de la base, ni n'a de comportement non-déterministe comme random ou clock_timestamp() ).

Réaliser des transactions permet de garantir l'atomicité des opérations : toutes les modifications sont validées (COMMIT), ou tout est annulé (ROLLBACK). Il n'y a pas d'état intermédiaire. Le COMMIT garantit aussi la durabilité des opérations : une fois que le COMMIT a réussi, la base de données garantit que les opérations ont bien été stockées, et ne seront pas perdues… sauf perte du matériel (disque) sur lequel ont été écrites ces opérations bien sûr.

L'opération COMMIT a donc bien sûr un coût : il faut garantir que les données sont bien écrites sur le disque, il faut les écrire sur le disque (évidemment), mais aussi attendre la confirmation du disque. Que les disques soient mécaniques ou SSD ne change pas grand chose, cette opération est coûteuse :

• Un disque dur doit se positionner au bon endroit (journal de transaction), écrire la donnée, et confirmer au système que c'est fait. Il faudra donc compter le temps de déplacement de la tête et de rotation du disque pour se positionner, et le temps d'écriture (celui-ci sera plus court). Compter 1 à 5 millisecondes.
• Un disque SSD doit écrire réellement le bloc demandé. Il faudra donc faire un ERASE du bloc puis une nouvelle écriture de celui-ci. C'est l'ERASE qui est lent (de l'ordre de 2 millisecondes, donc à peine plus rapide qu'un disque dur mécanique).

On peut limiter l'impact des écritures synchrone en utilisant un cache en écriture comme en proposent les serveurs haut de gamme.

Les transactions devant garantir l'unicité des opérations, il est nécessaire qu'elles prennent des verrous : sur les enregistrements modifiés, sur les tables accédées (pour éviter les changements de structure pendant leur manipulation), sur des prédicats (dans certains cas compliqués comme le niveau d'isolation serializable)… tout ceci a un impact :

• Le temps d'acquisition des verrous, bien sûr
• Mais aussi les sources de contention entre chaque session

Il est donc très difficile de déterminer la bonne durée d'une transaction. Trop courte : on génère beaucoup d'opérations synchrones. Trop longue : on risque de bloquer d'autres sessions. Le mieux est de coller au besoin fonctionnel.

Afin de garantir une isolation correcte entre les différentes sessions, le SGBD a besoin de protéger certaines opérations. On ne peut par exemple pas autoriser une session à modifier le même enregistrement qu'une autre, tant qu'on ne sait pas si cette dernière a validé ou annulé sa modification. On a donc un verrouillage des enregistrements modifiés.

Certains SGBD verrouillent totalement l'enregistrement modifié. Celui-ci n'est plus accessible même en lecture tant que la modification n'a pas été validée ou annulée. Cela a l'avantage d'éviter aux sessions en attente de voir une ancienne version de l'enregistrement, mais le défaut de les bloquer, et donc de fortement dégrader les performances.

PostgreSQL, comme Oracle, utilise un modèle dit MVCC (Multi-Version Concurrency Control), qui permet à chaque enregistrement de cohabiter en plusieurs versions simultanées en base. Cela permet d'éviter que les écrivains ne bloquent les lecteurs ou les lecteurs ne bloquent les écrivains. Cela permet aussi de garantir un instantané de la base à une requête, sur toute sa durée, voire sur toute la durée de sa transaction si la session le demande (BEGIN ISOLATION LEVEL REPEATABLE READ).

Dans le cas où il est réellement nécessaire de verrouiller un enregistrement sans le mettre à jour immédiatement (pour éviter une mise à jour concurrente), il faut utiliser l'ordre SQL SELECT FOR UPDATE.

Les deadlocks se produisent quand plusieurs sessions acquièrent simultanément des verrous et s'interbloquent. Par exemple :

Bien sûr, la situation ne reste pas en l'état. Une session qui attend un verrou appelle au bout d'un temps court (une seconde par défaut sous PostgreSQL) le gestionnaire de deadlock, qui finira par tuer une des deux sessions. Dans cet exemple, il sera appelé par la session 2, ce qui débloquera la situation.

Une application qui a beaucoup de deadlocks a plusieurs problèmes :

• Les transactions attendent beaucoup (utilisation de toutes les ressources machine difficile)
• Certaines finissent annulées et doivent donc être rejouées (travail supplémentaire)

Dans notre exemple, on aurait pu éviter le problème, en définissant une règle simple : toujours verrouiller par valeurs de a croissante. Dans la pratique, sur des cas complexes, c'est bien sûr bien plus difficile à faire. Par ailleurs, un deadlock peut impliquer plus de deux transactions. Mais simplement réduire le volume de deadlocks aura toujours un impact très positif sur les performances.

On peut aussi déclencher plus rapidement le gestionnaire de deadlock. 1 seconde, c'est quelquefois une éternité dans la vie d'une application. Sous PostgreSQL, il suffit de modifier le paramètre deadlock_timeout. Plus cette variable sera basse, plus le traitement de détection de deadlock sera déclenché souvent. Et celui-ci peut être assez gourmand si de nombreux verrous sont présents, puisqu'il s'agit de détecter des cycles dans les dépendances de verrous.

Bibliographie :

• The World and the Machine, Michael Jackson (version en ligne)
• The Art of SQL, Stéphane Faroult, ISBN-13: 978-0596008949
• Refactoring SQL Applications, Stéphane Faroult, ISBN-13: 978-0596514976
• SQL Performance Explained, Markus Winand :
  • site internet (fr)
  • FR: ISBN-13: 978-3950307832
  • EN: ISBN-13: 978-3950307825
• Introduction aux bases de données, Chris Date
  • FR: ISBN-13: 978-2711748389 (8e édition)
  • EN: ISBN-13: 978-0321197849
• Vidéos de Stéphane Faroult, roughsealtd sous Youtube :
  • Vidéo 1
  • Vidéo 2
  • Vidéo 3

Enoncé

• Charger le fichier de TP fourni par le formateur :

   createdb tp
   pg_restore -d tp tp.dmp

(Note pour le formateur : utiliser genere_nf_1.sh et genere_pagination.sh, sur une instance ayant une base « tp ».)

Exécuter aussi un VACUUM VERBOSE ANALYZE sur la base, afin d'avoir les statistiques à jour !

Normalisation de base

La table voitures viole la première forme normale (attribut répétitif, non atomique). De plus elle n'a pas de clé primaire.

• Renommer la table en voitures_orig.
• Écrire des requêtes permettant d'éclater cette table en trois tables: voitures, caracteristiques et caracteristiques_voitures. Ne pas supprimer la table voitures_orig (nous en aurons besoin plus tard). Mettre en place les contraintes d'intégrité : clé primaire sur chaque table, et clés étrangères.
  • Attention : la table de départ contient des immatriculations en doublon !
• Comparer les performances d'une recherche de voiture ayant un toit ouvrant avec l'ancien et le nouveau modèle.

Entité-clé-valeur

• La table voiture existe aussi dans cette base au format « entité/clé/valeur » : table voitures_ecv. Trouvez toutes les caractéristiques de toutes les voitures ayant un toit ouvrant dans cette table.

• Convertir cette table pour qu'elle utilise un hstore, créer un index sur la colonne de type hstore, réécrire la requête et comparer. Il y a de nombreuses solutions pour écrire cette conversion. Se reporter à la documentation de l'extension hstore.

• Afficher toutes les voitures ayant un ABS et un toit ouvrant, avec les deux modèles.

Sans toucher au schéma

Il est possible, si on peut réécrire la requête, d'obtenir de bonnes performances avec la première table voitures : PostgreSQL sait indexer des tableaux et des fonctions. Il saurait donc indexer un tableau résultat d'une fonction sur le champ caracteristiques.

• Trouver cette fonction (chercher dans les fonctions de découpage de chaîne de caractères, dans la documentation de PostgreSQL).
• Définir l'index (c'est un index sur un type tableau).
• Écrire la requête et son plan.

Pagination et index

La pagination est une fonctionnalité que l'on retrouve de plus en plus souvent, surtout depuis que les applications web ont pris une place prépondérant.

Dans la base TP existe une table posts. C'est une version simplifiée d'une table de forum. Nous voulons afficher très rapidement les messages (posts) d'un article : les 10 premiers, puis du 11 au 20, etc. le plus rapidement possible. Nous allons examiner les différentes stratégies possibles.

• Écrire une requête permettant de récupérer les 10 premiers posts de l'article 12. La table a été créée sans index, la requête va être très lente. Utiliser id_post, pas le timestamp (il servira dans le prochain TP).
• Créer un index permettant d'améliorer cette requête.
• Écrire la même requête permettant de récupérer les 10 posts suivants. Puis du post 901 au 921. Que constate-t-on sur le plan d'exécution ?
• Trouver une réécriture de la requête pour trouver directement les posts 901 à 911 une fois connu le post 900 récupéré au travers de la pagination.

Clauses WHERE et pièges

Nous allons maintenant manipuler le champ ts (de type timestamp) de la table posts.

• La requête select * from posts where to_char(ts,'YYYYMM')='201302' retourne tous les enregistrements de février 2013. Examiner son plan d'exécution. Où est le problème ?
• Réécrire la clause WHERE.
• Plus compliqué : retourner tous les posts ayant eu lieu un dimanche, en 2013, en passant par un index et en une seule requête.
  • Indice : il est possible de générer la liste de tous les dimanches de l'année 2013 avec generate_series().

• Pourquoi la requête suivante est-elle lente ?

  select * from posts where id_article = (
   select round(random()*(select max(id_article) from posts))
  )

Solutions

Normalisation de base

Renommer la table en voitures_orig

  alter table voitures rename TO voitures_orig;

Écrire des requêtes permettant d'éclater cette table en trois tables: voitures, caracteristiques et caracteristiques_voitures, et mettre les contraintes d'intégrité en place.

  create table voitures as
    select distinct on (immatriculation) immatriculation, modele
    from voitures_orig ;

  alter table voitures add primary key (immatriculation);

  create table caracteristiques
    as select *
       from (
               select distinct
                regexp_split_to_table(caracteristiques,',') caracteristique
               from voitures_orig)
            as tmp
       where caracteristique <> '' ;

  alter table caracteristiques add primary key (caracteristique);

  create table caracteristiques_voitures
    as select distinct *
    from (
         select
           immatriculation,
           regexp_split_to_table(caracteristiques,',') caracteristique
         from voitures_orig
        )
      as tmp
     where caracteristique <> '';

  alter table caracteristiques_voitures
  add primary key (immatriculation,caracteristique);

  alter table caracteristiques_voitures
  add foreign key (immatriculation)
  references voitures(immatriculation);

  alter table caracteristiques_voitures
  add foreign key (caracteristique)
  references caracteristiques(caracteristique);

Comparer les performances d'une recherche de voiture ayant un toit ouvrant avec l'ancien et le nouveau modèle.

# EXPLAIN ANALYZE SELECT * FROM voitures_orig
  WHERE caracteristiques ~ E'[[:<:]]toit ouvrant[[:>:]]';
                              QUERY PLAN
--------------------------------------------------------------------------
 Seq Scan on voitures_orig  (cost=0.00..639.90 rows=5 width=96)
                            (actual time=0.050..254.655 rows=8343 loops=1)
   Filter: (caracteristiques ~ '[[:<:]]toit ouvrant[[:>:]]'::text)
   Rows Removed by Filter: 91657
 Total runtime: 255.168 ms

ou plus simplement (mais moins sûr) :

# EXPLAIN ANALYZE SELECT * FROM voitures_orig
  WHERE caracteristiques like '%toit ouvrant%';
                                   QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on voitures_orig  (cost=0.00..1321.10 rows=8469 width=25)
                            (actual time=0.023..47.563 rows=8343 loops=1)
   Filter: (caracteristiques ~~ '%toit ouvrant%'::text)
   Rows Removed by Filter: 91657
 Total runtime: 48.155 ms

Avec le nouveau schéma :

# EXPLAIN ANALYZE  SELECT * FROM voitures
  WHERE EXISTS (
    SELECT 1 FROM caracteristiques_voitures
    WHERE caracteristiques_voitures.immatriculation=voitures.immatriculation
    AND caracteristique = 'toit ouvrant'
  );
                            QUERY PLAN
----------------------------------------------------------------------
 Merge Semi Join  (cost=0.89..2501.47 rows=8263 width=16)
                  (actual time=0.095..128.244 rows=8343 loops=1)
   Merge Cond: (voitures.immatriculation =
                caracteristiques_voitures.immatriculation)
   ->  Index Scan using voitures_pkey on voitures
            (cost=0.42..1600.50 rows=99992 width=16)
            (actual time=0.023..39.480 rows=99992 loops=1)
   ->  Index Only Scan using caracteristiques_voitures_pkey
                       on caracteristiques_voitures
                          (cost=0.41..547.70 rows=8263 width=10)
                          (actual time=0.039..9.677 rows=8343 loops=1)
         Index Cond: (caracteristique = 'toit ouvrant'::text)
         Heap Fetches: 0
 Total runtime: 128.872 ms

On garde sensiblement le même temps d'exécution (seule la syntaxe en expression régulière est sûre, et encore cette version est-elle encore trop simple). On serait par contre beaucoup plus rapides si cette option était rare. Ici, 10% des voitures ont l'option, le filtre n'est donc pas très discriminant.

Ce qu'on gagne réellement, c'est la garantie que les caractéristiques ne seront que celles existant dans la table caractéristique, ce qui évite d'avoir à réparer la base plus tard.

Si on cherche une option rare ou n'existant pas :

EXPLAIN ANALYZE  SELECT * FROM voitures
WHERE EXISTS (
    SELECT 1
    FROM caracteristiques_voitures
    WHERE caracteristiques_voitures.immatriculation=voitures.immatriculation
    AND caracteristique = 'toit'
);
                              QUERY PLAN
---------------------------------------------------------------------
 Nested Loop  (cost=0.84..1.19 rows=1 width=16)
              (actual time=0.017..0.017 rows=0 loops=1)
   ->  Index Only Scan using idx_caracteristique_immat
                       on caracteristiques_voitures
                            (cost=0.42..0.54 rows=1 width=10)
                            (actual time=0.016..0.016 rows=0 loops=1)
         Index Cond: (caracteristique = 'toit'::text)
         Heap Fetches: 0
   ->  Index Scan using voitures_new_pkey on voitures
            (cost=0.42..0.64 rows=1 width=16)
            (never executed)
         Index Cond: (immatriculation =
                      caracteristiques_voitures.immatriculation)
 Total runtime: 0.049 ms
(7 lignes)

Avec l'ancien schéma, on devait lire la table voiture en entier.

Si on recherche plusieurs options en même temps, l'optimiseur peut améliorer les choses en prenant en compte la fréquence de chaque option pour restreindre plus efficacement les recherches :

# EXPLAIN ANALYZE SELECT *
  FROM voitures
  JOIN caracteristiques_voitures AS cr1 USING (immatriculation)
  JOIN caracteristiques_voitures AS cr2 USING (immatriculation)
  JOIN caracteristiques_voitures AS cr3 USING (immatriculation)
  WHERE cr1.caracteristique = 'toit ouvrant'
  AND cr2.caracteristique = 'abs'
  AND cr3.caracteristique='4 roues motrices';
                                 QUERY PLAN
--------------------------------------------------------------------------------
 Nested Loop  (cost=1.66..2017.28 rows=57 width=58)
              (actual time=0.174..57.544 rows=478 loops=1)
   ->  Merge Join  (cost=1.24..1740.13 rows=603 width=72)
                   (actual time=0.144..50.162 rows=478 loops=1)
         Merge Cond: (cr1.immatriculation = cr2.immatriculation)
         ->  Merge Join  (cost=0.83..1157.84 rows=2198 width=48)
                         (actual time=0.083..33.680 rows=1529 loops=1)
               Merge Cond: (cr1.immatriculation = cr3.immatriculation)
               ->  Index Only Scan using caracteristiques_voitures_pkey
                                   on caracteristiques_voitures cr1
                                    (cost=0.41..547.70 rows=8263 width=24)
                                    (actual time=0.034..9.725 rows=8343 loops=1)
                     Index Cond: (caracteristique = 'toit ouvrant'::text)
                     Heap Fetches: 0
               ->  Index Only Scan using caracteristiques_voitures_pkey
                                         on caracteristiques_voitures cr3
                                  (cost=0.41..547.01 rows=8194 width=24)
                                  (actual time=0.029..10.297 rows=8216 loops=1)
                     Index Cond: (caracteristique = '4 roues motrices'::text)
                     Heap Fetches: 0
         ->  Index Only Scan using caracteristiques_voitures_pkey
                                   on caracteristiques_voitures cr2
                            (cost=0.41..549.62 rows=8455 width=24)
                            (actual time=0.028..9.818 rows=8300 loops=1)
               Index Cond: (caracteristique = 'abs'::text)
               Heap Fetches: 0
   ->  Index Scan using voitures_pkey on voitures
            (cost=0.42..0.45 rows=1 width=16)
            (actual time=0.013..0.014 rows=1 loops=478)
         Index Cond: (immatriculation = cr1.immatriculation)
 Total runtime:
Total runtime: 57.710 ms

Entité-clé-valeur

La table voiture existe aussi dans cette base au format « entité/clé/valeur » : table voitures_ecv. Trouvez toutes les caractéristiques de toutes les voitures ayant un toit ouvrant dans cette table.

# EXPLAIN ANALYZE SELECT * FROM voitures_ecv
  WHERE EXISTS (
    SELECT 1 FROM voitures_ecv test
    WHERE test.entite=voitures_ecv.entite
    AND cle = 'toit ouvrant' and valeur = true
  );
                              QUERY PLAN
---------------------------------------------------------------------
 Merge Semi Join  (cost=0.83..1881.36 rows=15136 width=25)
                  (actual time=0.052..94.150 rows=17485 loops=1)
   Merge Cond: (voitures_ecv.entite = test.entite)
   ->  Index Scan using voitures_ecv_pkey on voitures_ecv
            (cost=0.41..939.03 rows=57728 width=25)
            (actual time=0.013..20.755 rows=57728 loops=1)
   ->  Materialize  (cost=0.41..588.92 rows=8207 width=10)
                    (actual time=0.028..14.011 rows=8343 loops=1)
         ->  Index Scan using voitures_ecv_pkey on voitures_ecv test
                    (cost=0.41..568.40 rows=8207 width=10)
                    (actual time=0.025..11.508 rows=8343 loops=1)
               Index Cond: (cle = 'toit ouvrant'::text)
               Filter: valeur
 Total runtime: 95.398 ms

Convertir cette table pour qu'elle utilise un hstore, créer un index sur la colonne de type hstore, réécrire la requête et comparer.

CREATE EXTENSION HSTORE;

CREATE TABLE voitures_hstore AS
    SELECT entite immatriculation,
           hstore(array_agg(cle),array_agg(valeur)::text[]) caracteristiques
    FROM voitures_ecv group by entite;

ALTER TABLE voitures_hstore ADD PRIMARY KEY (immatriculation);

CREATE INDEX voitures_hstore_caracteristiques ON voitures_hstore
    USING gist (caracteristiques);

EXPLAIN (ANALYZE,BUFFERS)
    SELECT *
    FROM voitures_hstore
    WHERE caracteristiques @> '"toit ouvrant" => true';
                               QUERY PLAN
---------------------------------------------------------------------
 Index Scan using voitures_hstore_caracteristiques on voitures_hstore
        (cost=0.28..4.62 rows=37 width=64)
        (actual time=0.093..18.026 rows=8343 loops=1)
   Index Cond: (caracteristiques @> '"toit ouvrant"=>"true"'::hstore)
   Buffers: shared hit=6731
 Total runtime: 19.195 ms

Afficher toutes les voitures ayant un ABS et un toit ouvrant, avec les deux modèles.

Avec voitures_ecv :

# EXPLAIN (ANALYZE,BUFFERS)
    SELECT * FROM voitures_ecv
    WHERE EXISTS (
        SELECT 1
        FROM voitures_ecv test
        WHERE test.entite=voitures_ecv.entite
        AND cle = 'toit ouvrant' AND valeur = true
    )
    AND EXISTS (
        SELECT 1 FROM voitures_ecv test
        WHERE test.entite=voitures_ecv.entite
        AND cle = 'abs' AND valeur = true
    );
                                     QUERY PLAN
--------------------------------------------------------------------------
 Merge Semi Join  (cost=1.24..2549.89 rows=4012 width=25)
                  (actual time=0.121..152.364 rows=5416 loops=1)
   Merge Cond: (test.entite = test_1.entite)
   Buffers: shared hit=2172
   ->  Merge Semi Join  (cost=0.83..1881.36 rows=15136 width=35)
                        (actual time=0.078..116.027 rows=17485 loops=1)
         Merge Cond: (voitures_ecv.entite = test.entite)
         Buffers: shared hit=1448
         ->  Index Scan using voitures_ecv_pkey on voitures_ecv
                    (cost=0.41..939.03 rows=57728 width=25)
                    (actual time=0.018..24.430 rows=57728 loops=1)
               Buffers: shared hit=724
         ->  Materialize  (cost=0.41..588.92 rows=8207 width=10)
                          (actual time=0.042..15.858 rows=8343 loops=1)
               Buffers: shared hit=724
               ->  Index Scan using voitures_ecv_pkey on voitures_ecv test
                        (cost=0.41..568.40 rows=8207 width=10)
                        (actual time=0.038..12.204 rows=8343 loops=1)
                     Index Cond: (cle = 'toit ouvrant'::text)
                     Filter: valeur
                     Buffers: shared hit=724
   ->  Index Scan using voitures_ecv_pkey on voitures_ecv test_1
            (cost=0.41..569.34 rows=8301 width=10)
            (actual time=0.036..11.779 rows=8300 loops=1)
         Index Cond: (cle = 'abs'::text)
         Filter: valeur
         Buffers: shared hit=724
 Total runtime: 153.151 ms

Avec hstore :

# EXPLAIN (ANALYZE,BUFFERS)
    SELECT * FROM voitures_hstore
    WHERE caracteristiques @> '"toit ouvrant" => true, "abs" => true';
                              QUERY PLAN
---------------------------------------------------------------------
 Index Scan using voitures_hstore_caracteristiques on voitures_hstore
        (cost=0.28..4.62 rows=37 width=55)
        (actual time=0.102..4.759 rows=1538 loops=1)
   Index Cond: (caracteristiques @> '"abs"=>"true",
                "toit ouvrant"=>"true"'::hstore)
   Buffers: shared hit=1415
 Total runtime: 5.063 ms

On voit que la solution entité/clé/valeur n'arrive pas à être optimisée correctement. Le hstore par contre est de plus en plus rapide au fur et à mesure que la sélectivité augmente.

Sans toucher au schéma

Il est possible, si on peut réécrire la requête, d'obtenir de bonnes performances avec la première table voitures : PostgreSQL sait indexer des tableaux, et des fonctions. Il saurait donc indexer un tableau résultat d'une fonction sur le champ caracteristiques.

Trouver cette fonction (chercher dans les fonctions de découpage de chaîne de caractères, dans la documentation de PostgreSQL)

La fonction est regexp_split_to_array :

SELECT immatriculation, modele,
       regexp_split_to_array(caracteristiques,',')
FROM voitures_orig LIMIT 10;

 immatriculation | modele |  regexp_split_to_array
-----------------+--------+------------------------------------------------------------------------------------------------------------------
 XZ-971-EA       | twingo | {climatisation,abs,"jantes aluminium"}
 JC-269-WE       | twingo | {"jantes aluminium","4 roues motrices",
                             climatisation,"regulateur de vitesse",
                             "boite automatique","toit ouvrant"}
 KU-380-XU       | kangoo | {"jantes aluminium",
                            "boite automatique",abs}
 VW-418-JM       | kangoo | {abs,"jantes aluminium","boite automatique"}
 ZK-505-FO       | clio   | {"toit ouvrant"}
 PX-156-TH       | kangoo | {"jantes aluminium","regulateur de vitesse",
                             climatisation}
 LB-711-JC       | clio   | {"regulateur de vitesse",abs,"boite automatique",
                             "jantes aluminium","4 roues motrices",
                             climatisation}
 BK-870-IB       | clio   | {"boite automatique","4 roues motrices",
                             abs,"jantes aluminium"}
 VQ-123-HK       | twingo | {""}
 OR-647-BK       | clio   | {""}
(10 lignes)

La syntaye {} est la représentation texte d'un tableau.

• Définir l'index :

CREATE INDEX idx_voitures_array ON voitures_orig
    USING gin (regexp_split_to_array(caracteristiques,','));

• Écrire la requête et son plan :

# EXPLAIN ANALYZE
    SELECT * FROM voitures_orig
    WHERE regexp_split_to_array(caracteristiques,',') @> '{"toit ouvrant"}';
                                     QUERY PLAN
--------------------------------------------------------------------------------
 Bitmap Heap Scan on voitures_orig (cost=5.28..49.78 rows=500 width=25)
                                  (actual time=4.159..9.357 rows=8343 loops=1)
   Recheck Cond: (regexp_split_to_array(caracteristiques, ','::text) @>
                  '{"toit ouvrant"}'::text[])
   ->  Bitmap Index Scan on idx_voitures_array
            (cost=0.00..5.15 rows=500 width=0)
            (actual time=3.915..3.915 rows=8343 loops=1)
         Index Cond: (regexp_split_to_array(caracteristiques, ','::text) @>
                      '{"toit ouvrant"}'::text[])
 Total runtime: 10.139 ms

Pagination et index

La pagination est une fonctionnalité que l'on retrouve de plus en plus souvent, surtout depuis que les applications Web ont pris une place prépondérant.

Dans la base TP existe une table posts. C'est une version simplifiée d'une table de forum. Nous voulons afficher très rapidement les messages (posts) d'un article : les 10 premiers, puis du 11 au 20, etc. le plus rapidement possible. Nous allons examiner les différentes stratégies possibles.

Écrire une requête permettant de récupérer les 10 premiers posts de l'article 12. La table a été créée sans index, la requête va être très lente. Utiliser id_post, pas le timestamp (il servira dans le prochain TP).

EXPLAIN ANALYZE
    SELECT * FROM posts
   WHERE id_article =12
   ORDER BY id_post
   LIMIT 10;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Limit  (cost=161809.55..161809.57 rows=10 width=261)
        (actual time=2697.841..2697.844 rows=10 loops=1)
   ->  Sort  (cost=161809.55..161812.00 rows=982 width=261)
             (actual time=2697.839..2697.841 rows=10 loops=1)
         Sort Key: id_post
         Sort Method: top-N heapsort  Memory: 29kB
         ->  Seq Scan on posts  (cost=0.00..161788.33 rows=982 width=261)
                                (actual time=2.274..2695.722 rows=964 loops=1)
               Filter: (id_article = 12)
               Rows Removed by Filter: 9999036
 Total runtime: 2697.924 ms

Créer un index permettant d'améliorer cette requête.

CREATE INDEX posts_id_article_id_post ON posts (id_article , id_post);

EXPLAIN ANALYZE
    SELECT *
    FROM posts
    WHERE id_article =12
    ORDER BY  id_post
    LIMIT 10;
                      QUERY PLAN
---------------------------------------------------------
 Limit  (cost=0.43..1.61 rows=10 width=260)
        (actual time=0.045..0.092 rows=10 loops=1)
   ->  Index Scan using posts_id_article_id_post on posts
            (cost=0.43..115.90 rows=981 width=260)
            (actual time=0.043..0.090 rows=10 loops=1)
         Index Cond: (id_article = 12)
 Total runtime: 0.115 ms
(4 lignes)

C'est bien plus rapide : l'index retourne les enregistrements directement triés par id_article, id_post. On peut donc trouver le premier enregistrement ayant id_article = 12, puis récupérer de ce point tous les enregistrements par id_post croissant.

Écrire la même requête permettant de récupérer les 10 posts suivants. Puis du post 901 au 921. Que constate-t-on sur le plan d'exécution ?

EXPLAIN ANALYZE
    SELECT *
    FROM posts
    WHERE id_article = 12
    ORDER BY id_post
    LIMIT 10
    OFFSET 10;
                          QUERY PLAN
---------------------------------------------------------
 Limit  (cost=1.61..2.79 rows=10 width=260)
        (actual time=0.070..0.136 rows=10 loops=1)
   ->  Index Scan using posts_id_article_id_post on posts
            (cost=0.43..115.90 rows=981 width=260)
            (actual time=0.034..0.133 rows=20 loops=1)
         Index Cond: (id_article = 12)
 Total runtime: 0.161 ms
(4 lignes)

Tout va bien. La requête est à peine plus coûteuse.

À partir du post 900 :

EXPLAIN ANALYZE
    SELECT *
    FROM posts
    WHERE id_article = 12
    ORDER BY id_post
    LIMIT 10
    OFFSET 900;
                          QUERY PLAN
---------------------------------------------------------
 Limit  (cost=106.37..107.55 rows=10 width=260)
        (actual time=5.310..5.362 rows=10 loops=1)
   ->  Index Scan using posts_id_article_id_post on posts
            (cost=0.43..115.90 rows=981 width=260)
            (actual time=0.038..5.288 rows=910 loops=1)
         Index Cond: (id_article = 12)
 Total runtime: 5.397 ms
(4 lignes)

Cette requête est 50 fois plus lente. Il serait intéressant de trouver plus rapide.

Trouver une réécriture de la requête pour trouver directement les posts 901 à 911 une fois connu le post 900 récupéré au travers de la pagination.

Pour se mettre dans la condition du test, récupérons l'enregistrement 900 :

SELECT id_article, id_post
FROM posts
WHERE id_article = 12
ORDER BY id_post
LIMIT 1
OFFSET 899;

 id_article | id_post
------------+---------
         12 | 9245182
(1 ligne)

Il suffit donc de récupérer les 10 articles pour lesquels id_article = 12 et id_post > 9245182. (Ces valeurs peuvent être différentes pour votre TP, suivant le contenu de la table posts).

EXPLAIN ANALYZE
    SELECT *
    FROM posts
    WHERE id_article = 12
    AND id_post> 9245182
    ORDER BY id_post
    LIMIT 10;
                            QUERY PLAN
----------------------------------------------------------------
 Limit  (cost=0.43..1.65 rows=10 width=260)
        (actual time=0.037..0.056 rows=10 loops=1)
   ->  Index Scan using posts_id_article_id_post on posts
            (cost=0.43..9.41 rows=74 width=260)
            (actual time=0.035..0.050 rows=10 loops=1)
         Index Cond: ((id_article = 12) AND (id_post > 9245182))
 Total runtime: 0.094 ms
(4 lignes)

Nous sommes de retour à des temps d'exécution de 0,1 ms.

Attention : dans une base de données réaliste, le critère de sélection peut être plus compliqué. On peut avoir par exemple besoin d'un filtre sur plus d'une colonne. Ou on peut par exemple imaginer (ici ça n'a pas de sens) qu'on veut continuer sur l'article 13 si on arrive à la fin de l'article 12. Dans ce cas, on peut utiliser cette syntaxe (qui n'est pas supportée par tous les SGBD, alors que cela fait partie du standard SQL-92) :

EXPLAIN ANALYZE
    SELECT *
    FROM posts
    WHERE (id_article, id_post) > (12, 9245182)
    ORDER BY id_article, id_post
    LIMIT 10;
                            QUERY PLAN
------------------------------------------------------------------
 Limit  (cost=0.44..1.57 rows=10 width=260)
        (actual time=0.039..0.057 rows=10 loops=1)
   ->  Index Scan using posts_id_article_id_post on posts
            (cost=0.44..1135139.20 rows=9990333 width=260)
            (actual time=0.036..0.052 rows=10 loops=1)
         Index Cond: (ROW(id_article, id_post) > ROW(12, 9245182))
 Total runtime: 0.096 ms
(4 lignes)

On compare donc le n-uplet (id_article, id_post) à (12, 9245182) (l'ordre de tri étant l'ordre logique : on compare les id_article, puis si égalité les id_post).

Clauses WHERE et pièges

Nous allons maintenant manipuler le champ ts (de type timestamp) de la table posts.

La requête select * from posts where to_char(ts,'YYYYMM')='201302' retourne tous les enregistrements de février 2013. Examiner son plan d'exécution. Où est le problème ?

EXPLAIN ANALYZE
    SELECT *
    FROM posts
    WHERE to_char(ts,'YYYYMM')='201302';
                                QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on posts  (cost=0.00..187728.49 rows=50000 width=269)
                    (actual time=0.380..14163.371 rows=18234 loops=1)
   Filter: (to_char(ts, 'YYYYMM'::text) = '201302'::text)
   Rows Removed by Filter: 9981766
 Total runtime: 14166.265 ms
(4 lignes)

On a un scan complet de la table (seq scan). C'est normal : PostgreSQL ne peut pas deviner que to_char(ts,'YYYYMM')='201302' veut dire « toutes les dates du mois de février 2013 ». Une fonction est pour lui une boîte noire. Cela équivaut à rechercher tous les mots anglais se traduisant par « Bonjour » dans un dictionnaire Anglais => Français : la seule solution est de lire tout le dictionnaire.

Ceci est une des causes les plus habituelles de ralentissement de requêtes : une fonction est appliquée à une colonne, ce qui rend le filtre incompatible avec l'utilisation d'un index.

NB : on trouve parfois le terme « sargable » dans la littérature pour définir les clauses WHERE pouvant être résolues par parcours d'index.

Réécrire la clause WHERE.

C'est à nous d'indiquer une clause WHERE au moteur qu'il puisse directement appliquer sur notre timestamp :

EXPLAIN ANALYZE
    SELECT *
    FROM posts
    WHERE ts >= '2013-02-01'
    AND ts < '2013-03-01';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using idx_posts_ts on posts
        (cost=0.43..2314.75 rows=19641 width=269)
        (actual time=0.054..104.416 rows=18234 loops=1)
   Index Cond: ((ts >= '2013-02-01 00:00:00+01'::timestamp with time zone)
                AND (ts < '2013-03-01 00:00:00+01'::timestamp with time zone))
 Total runtime: 105.712 ms
(3 lignes)

• Plus compliqué : retourner tous les posts ayant eu lieu un dimanche, en 2013, en passant par un index et en une seule requête.
• Indice : il est possible de générer la liste de tous les dimanches de l'année 2013 avec generate_series().

Construisons cette requête morceau par morceau :

SELECT generate_series(
    '2013-01-06 00:00:00',
    '2014-01-01 00:00:00',
    interval '7 days'
);

produit la liste de tous les dimanches de 2013 (le premier dimanche est le 6 janvier).

On aurait aussi pu calculer ce premier dimance ainsi, par exemple :

select '2013-01-01'::timestamp
       + interval '1 day'
       * (7-extract (dow from timestamp '2013-01-01'))

Ensuite :

SELECT i debut,
       i + interval '1 day' fin
FROM generate_series(
    '2013-01-06 00:00:00',
    '2014-01-01 00:00:00',
    interval '7 days'
) g(i);

produit la liste de tous les intervalles correspondant à ces dimanches.

Il ne nous reste plus qu'à joindre ces deux ensembles :

explain analyze
SELECT posts.*
FROM posts
JOIN (
    SELECT i debut,
            i+interval '1 day' fin
    FROM generate_series(
       '2013-01-06 00:00:00',
       '2014-01-01 00:00:00',
       interval '7 days'
    ) g(i)
) interval ON (posts.ts >= interval.debut AND posts.ts <= interval.fin) ;
                                QUERY PLAN
------------------------------------------------------------------------------
 Nested Loop  (cost=11680.98..79960740.50 rows=1111042000 width=268)
              (actual time=0.816..93.620 rows=33705 loops=1)
   ->  Function Scan on generate_series g
            (cost=0.00..10.00 rows=1000 width=8)
            (actual time=0.086..0.110 rows=52 loops=1)
   ->  Bitmap Heap Scan on posts
            (cost=11680.98..68850.31 rows=1111042 width=268)
            (actual time=0.482..1.547 rows=648 loops=52)
         Recheck Cond: ((ts >= g.i) AND (ts <= (g.i + '1 day'::interval)))
         ->  Bitmap Index Scan on idx_posts_ts
                (cost=0.00..11403.22 rows=1111042 width=0)
                (actual time=0.299..0.299 rows=648 loops=52)
               Index Cond: ((ts >= g.i) AND (ts <= (g.i + '1 day'::interval)))
 Total runtime: 96.472 ms

Attention : les inéqui-jointures entraînent forcément des nested loops. Ici tout va bien parce que la liste des dimanches est raisonnablement courte. De plus, pour avoir un tel plan, il faut que shared_buffers et/ou effective_cache_size soient suffisamment élevés (pour que le moteur estime qu'il peut passer par les index).

Pourquoi la requête suivante est-elle lente ?

select * from posts where id_article = (
 select round(random()*(select max(id_article) from posts))
)

EXPLAIN ANALYZE
    SELECT *
    FROM posts
    WHERE id_article = (
        SELECT round(random()*(select max(id_article) FROM posts))
    );
                                     QUERY PLAN
--------------------------------------------------------------------------------
 Seq Scan on posts  (cost=0.58..187730.18 rows=50000 width=269)
                    (actual time=11.143..3713.194 rows=990 loops=1)
   Filter: ((id_article)::double precision = $2)
   Rows Removed by Filter: 9999010
   InitPlan 3 (returns $2)
     ->  Result  (cost=0.56..0.58 rows=1 width=0)
                 (actual time=0.044..0.044 rows=1 loops=1)
           InitPlan 2 (returns $1)
             ->  Result  (cost=0.55..0.56 rows=1 width=0)
                         (actual time=0.037..0.037 rows=1 loops=1)
                   InitPlan 1 (returns $0)
                     ->  Limit  (cost=0.43..0.55 rows=1 width=4)
                                (actual time=0.033..0.033 rows=1 loops=1)
                           ->  Index Only Scan Backward
                                using posts_id_article_id_post
                                on posts posts_1
                                   (cost=0.43..1137275.29 rows=10000000 width=4)
                                   (actual time=0.033..0.033 rows=1 loops=1)
                                 Index Cond: (id_article IS NOT NULL)
                                 Heap Fetches: 1
 Total runtime: 3713.666 ms
(13 lignes)

Le seul nœud de cette requête à être lent est le Seq Scan on posts. Il prend l'essentiel de la durée de la requête. Pourquoi ? On compare pourtant id_article à une constante.

Certes, mais cette constante n'est pas du bon type !

Voici le prototype de le fonction round :

FUNCTION round (double precision) RETURNS double precision

Le problème est visible dans le plan :

Filter: ((id_article)::double precision = $2)

(id_article)::double precision signifie que tous les id_article sont convertis en double precision pour ensuite être comparés au résultat du round. Or une fonction de conversion est une fonction, ce qui rend l'index inutilisable. Il faut que nous forcions PostgreSQL à convertir le résultat du round en entier :

EXPLAIN ANALYZE
    SELECT *
    FROM posts
    WHERE id_article = (
        SELECT round(random()*(select max(id_article) from posts))
    )::int;
                                      QUERY PLAN
--------------------------------------------------------------------------------
 Index Scan using posts_id_article_id_post on posts
        (cost=1.02..118.83 rows=1001 width=269)
        (actual time=0.094..5.158 rows=970 loops=1)
   Index Cond: (id_article = ($2)::integer)
   InitPlan 3 (returns $2)
     ->  Result  (cost=0.56..0.58 rows=1 width=0)
                 (actual time=0.037..0.037 rows=1 loops=1)
           InitPlan 2 (returns $1)
             ->  Result  (cost=0.55..0.56 rows=1 width=0)
                         (actual time=0.031..0.032 rows=1 loops=1)
                   InitPlan 1 (returns $0)
                     ->  Limit  (cost=0.43..0.55 rows=1 width=4)
                                (actual time=0.029..0.030 rows=1 loops=1)
                           ->  Index Only Scan Backward
                               using posts_id_article_id_post on posts posts_1
                                   (cost=0.43..1137275.29 rows=10000000 width=4)
                                   (actual time=0.027..0.027 rows=1 loops=1)
                                 Index Cond: (id_article IS NOT NULL)
                                 Heap Fetches: 1
 Total runtime: 5.293 ms
(12 lignes)

Face à un problème de performances, l'administrateur se retrouve assez rapidement face à une (ou plusieurs) requête(s). Une requête en soi représente très peu d'informations. Suivant la requête, des dizaines de plans peuvent être sélectionnés pour l'exécuter. Il est donc nécessaire de pouvoir trouver le plan d'exécution et de comprendre ce plan. Cela permet de mieux appréhender la requête et de mieux comprendre les pistes envisageables pour la corriger.

Avant de détailler le fonctionnement du planificateur, nous allons regarder la façon dont une requête s'exécute globalement. Ensuite, nous aborderons le planificateur : en quoi est-il utile, comment fonctionne-t-il, et comment le configurer. Nous verrons aussi l'ensemble des opérations utilisables par le planificateur. Enfin, nous expliquerons comment utiliser EXPLAIN ainsi que les outils essentiels pour faciliter la compréhension d'un plan de requête.

Tous les exemples proposés ici viennent d'une version 9.1.

L'exécution d'une requête peut se voir sur deux niveaux :

• ce que le système perçoit ;
• ce que le SGBD fait.

Dans les deux cas, cela va nous permettre de trouver les possibilités de lenteurs dans l'exécution d'une requête par un utilisateur.

PostgreSQL est un système client-serveur. L'utilisateur se connecte via un outil (le client) à une base d'une instance PostgreSQL (le serveur). L'outil peut envoyer une requête au serveur, celui-ci l'exécute et finit par renvoyer les données résultant de la requête ou le statut de la requête.

Généralement, l'envoi de la requête est rapide. Par contre, la récupération des données peut poser problème si une grosse volumétrie est demandée sur un réseau à faible débit.

Lorsque le serveur récupère la requête, un ensemble de traitements est réalisé :

• le parser va réaliser une analyse syntaxique de la requête
• le rewriter va réécrire, si nécessaire la requête
• pour cela, il prend en compte les règles et vues
• si une règle demande de changer la requête, la requête envoyée est remplacée par la nouvelle
• si une vue est utilisée, la requête qu'elle contient est intégrée dans la requête envoyée
• le planner va générer l'ensemble des plans d'exécutions
• il calcule le coût de chaque plan
• puis il choisit le plan le moins coûteux, donc le plus intéressant
• l' executer exécute la requête
• pour cela, il doit commencer par récupérer les verrous nécessaires sur les objets ciblés
• une fois les verrous récupérés, il exécute la requête
• une fois la requête exécutée, il envoie les résultats à l'utilisateur

Plusieurs goulets d'étranglement sont visibles ici. Les plus importants sont :

• la planification (à tel point qu'il est parfois préférable de ne générer qu'un sous-ensemble de plans, pour passer plus rapidement à la phase d'exécution) ;
• la récupération des verrous (une requête peut attendre plusieurs secondes, minutes, voire heures avant de récupérer les verrous et exécuter réellement la requête) ;
• l'exécution de la requête ;
• l'envoi des résultats à l'utilisateur.

Il est possible de tracer l'exécution des différentes étapes grâce aux options log_parser_stats, log_planner_stats et log_executor_stats. Voici un exemple complet :

• Mise en place de la configuration sur la session :

b1=# SET log_parser_stats TO on;
b1=# SET log_planner_stats TO on;
b1=# SET log_executor_stats TO on;
b1=# SET client_min_messages TO log;

• Exécution de la requête :

b1=# SELECT * FROM t1 WHERE id=10;

• Trace du parser

LOG:  PARSER STATISTICS
DETAIL:  ! system usage stats:
! 0.000051 elapsed 0.000000 user 0.000000 system sec
! [0.017997 user 0.021996 sys total]
! 0/0 [13040/248] filesystem blocks in/out
! 0/0 [40/1589] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [167/6] voluntary/involuntary context switches
LOG:  PARSE ANALYSIS STATISTICS
DETAIL:  ! system usage stats:
! 0.000197 elapsed 0.001000 user 0.000000 system sec
! [0.018997 user 0.021996 sys total]
! 0/0 [13040/248] filesystem blocks in/out
! 0/1 [40/1590] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [167/6] voluntary/involuntary context switches

• Trace du rewriter

LOG:  REWRITER STATISTICS
DETAIL:  ! system usage stats:
! 0.000007 elapsed 0.000000 user 0.000000 system sec
! [0.018997 user 0.021996 sys total]
! 0/0 [13040/248] filesystem blocks in/out
! 0/0 [40/1590] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [167/6] voluntary/involuntary context switches

• Trace du planner

LOG:  PLANNER STATISTICS
DETAIL:  ! system usage stats:
! 0.000703 elapsed 0.000000 user 0.000000 system sec
! [0.018997 user 0.021996 sys total]
! 0/0 [13040/248] filesystem blocks in/out
! 0/6 [40/1596] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [167/6] voluntary/involuntary context switches

• Trace du executer

LOG:  EXECUTOR STATISTICS
DETAIL:  ! system usage stats:
! 0.078548 elapsed 0.000000 user 0.000000 system sec
! [0.019996 user 0.021996 sys total]
! 16/0 [13056/248] filesystem blocks in/out
! 0/2 [40/1599] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 1/0 [168/6] voluntary/involuntary context switches

Il existe quelques requêtes qui échappent à la séquence d'opérations présentées précédemment. Toutes les opérations DDL (modification de la structure de la base), les instructions TRUNCATE et COPY (en partie) sont vérifiées syntaxiquement, puis directement exécutées. Les étapes de réécriture et de planification ne sont pas réalisées.

Le principal souci pour les performances sur ce type d'instructions est donc l'obtention des verrous et l'exécution réelle.

Un prédicat est une condition de filtrage présente dans la clause WHERE d'une requête. Par exemple colonne = valeur.

La sélectivité est liée à l'application d'un prédicat sur une table. Elle détermine le nombre de lignes remontées par la lecture d'une relation suite à l'application d'une clause de filtrage, ou prédicat. Elle peut être vue comme un coefficient de filtrage d'un prédicat. La sélectivité est exprimée sous la forme d'un pourcentage. Pour une table de 1000 lignes, si la sélectivité d'un prédicat est de 10%, la lecture de la table en appliquant le prédicat devrait retourner 100 lignes.

La cardinalité représente le nombre de lignes d'une relation. En d'autres termes, la cardinalité représente le nombre de lignes d'une table ou du résultat d'une fonction. Elle représente aussi le nombre de lignes retourné par la lecture d'une table après application d'un ou plusieurs prédicats.

Cette requête permet de déterminer quels sont les employés basés à Nantes.

Le script suivant permet de recréer le jeu d'essai :

CREATE TABLE services (
    num_service integer primary key,
    nom_service character varying(20),
    localisation character varying(20)
);

CREATE TABLE employes (
    matricule     integer primary key,
    nom           varchar(15) not null,
    prenom        varchar(15) not null,
    fonction      varchar(20) not null,
    manager       integer,
    date_embauche date,
    num_service   integer not null references services (num_service)
);

INSERT INTO services VALUES (1, 'Comptabilité', 'Paris');
INSERT INTO services VALUES (2, 'R&D', 'Rennes');
INSERT INTO services VALUES (3, 'Commerciaux', 'Limoges');
INSERT INTO services VALUES (4, 'Consultants', 'Nantes');

INSERT INTO employes VALUES
    (33, 'Roy', 'Arthur', 'Consultant', 105, '2000-06-01', 4);
INSERT INTO employes VALUES
    (81, 'Prunelle', 'Léon', 'Commercial', 97, '2000-06-01', 3);
INSERT INTO employes VALUES
    (97, 'Lebowski', 'Dude', 'Responsable', 104, '2003-01-01', 3);
INSERT INTO employes VALUES
    (104, 'Cruchot', 'Ludovic', 'Directeur Général', NULL, '2005-03-06', 3);
INSERT INTO employes VALUES
    (105, 'Vacuum', 'Anne-Lise', 'Responsable', 104, '2005-03-06', 4);
INSERT INTO employes VALUES
    (119, 'Thierrie', 'Armand', 'Consultant', 105, '2006-01-01', 4);
INSERT INTO employes VALUES
    (120, 'Tricard', 'Gaston', 'Développeur', 125, '2006-01-01', 2);
INSERT INTO employes VALUES
    (125, 'Berlicot', 'Jules', 'Responsable', 104, '2006-03-01', 2);
INSERT INTO employes VALUES
    (126, 'Fougasse', 'Lucien', 'Comptable', 128, '2006-03-01', 1);
INSERT INTO employes VALUES
    (128, 'Cruchot', 'Josépha', 'Responsable', 105, '2006-03-01', 1);
INSERT INTO employes VALUES
    (131, 'Lareine-Leroy', 'Émilie', 'Développeur', 125, '2006-06-01', 2);
INSERT INTO employes VALUES
    (135, 'Brisebard', 'Sylvie', 'Commercial', 97, '2006-09-01', 3);
INSERT INTO employes VALUES
    (136, 'Barnier', 'Germaine', 'Consultant', 105, '2006-09-01', 4);
INSERT INTO employes VALUES
    (137, 'Pivert', 'Victor', 'Consultant', 105, '2006-09-01', 4);

La directive EXPLAIN permet de connaître le plan d'exécution d'une requête. Elle permet de savoir par quelles étapes va passer le SGBD pour répondre à la requête.

Le but du planificateur est assez simple. Pour une requête, il existe de nombreux plans d'exécution possibles. Il va donc énumérer tous les plans d'exécution possibles (sauf si cela représente vraiment trop de plans auquel cas, il ne prendra en compte qu'une partie des plans possibles). Il calcule ensuite le coût de chaque plan. Pour cela, il dispose d'informations sur les données (des statistiques), d'une configuration (réalisée par l'administrateur de bases de données) et d'un ensemble de règles inscrites en dur. Une fois tous les coûts calculés, il ne lui reste plus qu'à sélectionner le plan qui a le plus petit coût.

Le planificateur est un composant essentiel d'un moteur de bases de données. Les moteurs utilisent un langage SQL qui permet à l'utilisateur de décrire le résultat qu'il souhaite obtenir. Par exemple, s'il veut récupérer des informations sur tous les clients dont le nom commence par la lettre A en triant les clients par leur département, il pourrait utiliser une requête du type :

SELECT * FROM clients WHERE nom LIKE 'A%' ORDER BY departement;

Un moteur de bases de données peut récupérer les données de plusieurs façons :

• faire un parcours séquentiel de la table clients en filtrant les enregistrements d'après leur nom, puis trier les données grâce à un algorithme ;
• faire un parcours d'index sur la colonne nom pour trouver plus rapidement les enregistrements de la table clients satisfaisant le filtre 'A%', puis trier les données grâce à un algorithme ;
• faire un parcours d'index sur la colonne département pour récupérer les enregistrements déjà triés, et ne retourner que ceux vérifiant nom like 'A%'

Et ce ne sont que quelques exemples car il serait possible d'avoir un index utilisable pour le tri et le filtre par exemple.

Donc la requête décrit le résultat à obtenir, et le planificateur va chercher le meilleur moyen pour parvenir à ce résultat.

Pour ce travail, il dispose d'un certain nombre d'opérateurs. Ces opérateurs travaillent sur des ensembles de lignes, généralement un ou deux. Chaque opérateur renvoie un seul ensemble de lignes. Le planificateur peut combiner ces opérations suivant certaines règles. Un opérateur peut renvoyer l'ensemble de résultats de deux façons : d'un coup (par exemple le tri) ou petit à petit (par exemple un parcours séquentiel). Le premier cas utilise plus de mémoire, et peut nécessiter d'écrire des données temporaires sur disque. Le deuxième cas aide à accélérer des opérations comme les curseurs, les sous-requêtes IN et EXISTS, la clause LIMIT, etc.

Le planificateur suit deux règles :

• il doit récupérer le bon résultat ;
• il doit le récupérer le plus rapidement possible.

Cette deuxième règle lui impose de minimiser l'utilisation des ressources : en tout premier lieu les opérations disques vu qu'elles sont les plus coûteuses, mais aussi la charge CPU et l'utilisation de la mémoire. Dans le cas des opérations disques, s'il doit en faire, il doit absolument privilégier les opérations séquentielles aux opérations aléatoires (qui demandent un déplacement de la tête de disque, ce qui est l'opération la plus coûteuse sur les disques magnétiques).

Pour déterminer le chemin d'exécution le moins coûteux, l'optimiseur devrait connaître précisément les données mises en œuvre dans la requête, les particularités du matériel et la charge en cours sur ce matériel. Cela est impossible. Ce problème est contourné en utilisant deux mécanismes liés l'un à l'autre :

• un mécanisme de calcul de coût de chaque opération,
• des statistiques sur les données.

Pour quantifier la charge nécessaire pour répondre à une requête, PostgreSQL utilise un mécanisme de coût. Il part du principe que chaque opération a un coût plus ou moins important. Les statistiques sur les données permettent à l'optimiseur de requêtes de déterminer assez précisément la répartition des valeurs d'une colonne d'une table, sous la forme d'histogramme. Il dispose encore d'autres informations comme la répartition des valeurs les plus fréquentes, le pourcentage de NULL, le nombre de valeurs distinctes, etc… Toutes ces informations aideront l'optimiseur à déterminer la sélectivité d'un filtre (prédicat de la clause WHERE, condition de jointure) et donc quelle est la quantité de données récupérées par la lecture d'une table en utilisant le filtre évalué. Enfin, l'optimiseur s'appuie sur le schéma de la base de données afin de déterminer différents paramètres qui entrent dans le calcul du plan d'exécution : contrainte d'unicité sur une colonne, présence d'une contrainte NOT NULL, etc.

À partir du modèle de données et de la requête soumise, l'optimiseur de PostgreSQL va pouvoir déterminer si une jointure externe n'est pas utile à la production du résultat.

Suppression des jointures externes inutiles

Sous certaines conditions, PostgreSQL peut supprimer des jointures externes, à condition que le résultat ne soit pas modifié :

EXPLAIN SELECT e.matricule, e.nom, e.prenom
  FROM employes e
  LEFT JOIN services s
    ON (e.num_service = s.num_service)
 WHERE e.num_service = 4;
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on employes e  (cost=0.00..1.18 rows=5 width=23)
   Filter: (num_service = 4)

Toutefois, si le prédicat de la requête est modifié pour s'appliquer sur la table services, la jointure est tout de même réalisée, puisqu'on réalise un test d'existence sur cette table services :

EXPLAIN SELECT e.matricule, e.nom, e.prenom
  FROM employes e
  LEFT JOIN services s
    ON (e.num_service = s.num_service)
 WHERE s.num_service = 4;
                                 QUERY PLAN
------------------------------------------------------------------------------
 Nested Loop  (cost=0.15..9.39 rows=5 width=19)
   ->  Index Only Scan using services_pkey on services s  (cost=0.15..8.17...)
         Index Cond: (num_service = 4)
   ->  Seq Scan on employes e  (cost=0.00..1.18 rows=5 width=23)
         Filter: (num_service = 4)

Transformation des sous-requêtes

Certaines sous-requêtes sont transformées en jointure :

EXPLAIN SELECT *
  FROM employes emp
  JOIN (SELECT * FROM services WHERE num_service = 1) ser
    ON (emp.num_service = ser.num_service);
                               QUERY PLAN
-----------------------------------------------------------------------
 Nested Loop  (cost=0.15..9.36 rows=2 width=163)
   ->  Index Scan using services_pkey on services  (cost=0.15..8.17...)
         Index Cond: (num_service = 1)
   ->  Seq Scan on employes emp  (cost=0.00..1.18 rows=2 width=43)
         Filter: (num_service = 1)
(5 lignes)

La sous-requête ser a été remonté dans l'arbre de requête pour être intégré en jointure.

Application des prédicats au plus tôt

Lorsque cela est possible, PostgreSQL essaye d'appliquer les prédicats au plus tôt :

EXPLAIN SELECT MAX(date_embauche)
  FROM (SELECT * FROM employes WHERE num_service = 4) e
 WHERE e.date_embauche < '2006-01-01';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Aggregate  (cost=1.21..1.22 rows=1 width=4)
   ->  Seq Scan on employes  (cost=0.00..1.21 rows=2 width=4)
         Filter: ((date_embauche < '2006-01-01'::date) AND (num_service = 4))
(3 lignes)

Les deux prédicats num_service = 4 et date_embauche < '2006-01-01' ont été appliqués en même temps, réduisant ainsi le jeu de données à considéré dès le départ.

En cas de problème, il est possible d'utiliser une CTE (clause WITH) pour bloquer cette optimisation :

EXPLAIN WITH e AS (SELECT * FROM employes WHERE num_service = 4)
SELECT MAX(date_embauche)
 FROM e
WHERE e.date_embauche < '2006-01-01';
                           QUERY PLAN
-----------------------------------------------------------------
 Aggregate  (cost=1.29..1.30 rows=1 width=4)
   CTE e
     ->  Seq Scan on employes  (cost=0.00..1.18 rows=5 width=43)
           Filter: (num_service = 4)
   ->  CTE Scan on e  (cost=0.00..0.11 rows=2 width=4)
         Filter: (date_embauche < '2006-01-01'::date)

Function inlining

CREATE TABLE inline (id serial, tdate date);
INSERT INTO inline (tdate)
 SELECT generate_series('1800-01-01', '2015-12-01', interval '15 days');

CREATE OR REPLACE FUNCTION add_months_sql(mydate date, nbrmonth integer)
  RETURNS date AS
$BODY$
SELECT ( mydate + interval '1 month' * nbrmonth )::date;
$BODY$
  LANGUAGE SQL;

CREATE OR REPLACE FUNCTION add_months_plpgsql(mydate date, nbrmonth integer)
  RETURNS date AS
$BODY$
 BEGIN RETURN ( mydate + interval '1 month' * nbrmonth ); END;
$BODY$
  LANGUAGE plpgsql;

Si l'on utilise la fonction écrite en PL/pgsql, on retrouve l'appel de la fonction dans la clause Filter du plan d'exécution de la requête :

mabase=#EXPLAIN (ANALYZE, BUFFERS) SELECT *
FROM inline WHERE tdate = add_months_plpgsql(now()::date, -1);
                                             QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on inline  (cost=0.00..1430.52...) (actual time=42.102..42.102...)
   Filter: (tdate = add_months_plpgsql((now())::date, (-1)))
   Rows Removed by Filter: 5258
   Buffers: shared hit=24
 Total runtime: 42.139 ms
(5 lignes)

PostgreSQL ne sait pas intégrer le code des fonctions PL/pgsql dans ses plans d'exécution.

En revanche, en utilisant la fonction écrite en langage SQL, la définition de la fonction a été intégrée dans la clause de filtrage de la requête :

mabase=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM inline
WHERE tdate = add_months_sql(now()::date, -1);
                                            QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on inline  (cost=0.00..142.31...) (actual time=6.647..6.647...)
   Filter: (tdate = (((now())::date + '-1 mons'::interval))::date)
   Rows Removed by Filter: 5258
   Buffers: shared hit=24
 Total runtime: 6.699 ms
(5 lignes)

Le code de la fonction SQL a été correctement intégré dans le plan d'exécution. Le temps d'exécution a été divisé par 6 sur le jeu de donnée réduit, montrant l'impact de l'appel d'une fonction dans une clause de filtrage.

Pour exécuter une requête, le planificateur va utiliser des opérations. Pour lire des lignes, il peut utiliser un parcours de table, un parcours d'index ou encore d'autres types de parcours. Ce sont généralement les premières opérations utilisées. Ensuite, d'autres opérations permettent différentes actions :

• joindre deux ensembles de lignes avec des opérations de jointures (trois au total) ;
• agréger un ensemble de lignes avec une opération d'agrégation (trois là- aussi) ;
• trier un ensemble de lignes ;
• etc.

L'optimiseur statistique de PostgreSQL utilise un modèle de calcul de coût. Les coûts calculés sont des indications arbitraires sur la charge nécessaire pour répondre à une requête. Chaque facteur de coût représente une unité de travail : lecture d'un bloc, manipulation des lignes en mémoire, application d'un opérateur sur des données.

Pour quantifier la charge nécessaire pour répondre à une requête, PostgreSQL utilise un mécanisme de coût. Il part du principe que chaque opération a un coût plus ou moins important.

Sept paramètres permettent d'ajuster les coûts relatifs :

• seq_page_cost représente le coût relatif d'un accès séquentiel au disque. Ce paramètre vaut 1 et ne devrait pas être modifié.
• random_page_cost représente le coût relatif d'un accès aléatoire au disque. Ce paramètre vaut 4 par défaut, cela signifie que le temps de déplacement de la tête de lecture de façon aléatoire est estimé quatre fois plus important que le temps d'accès d'un bloc à un autre.
• cpu_tuple_cost représente le coût relatif de la manipulation d'une ligne en mémoire. Ce paramètre vaut par défaut 0,01.
• cpu_index_tuple_cost répercute le coût de traitement d'une donnée issue d'un index. Ce paramètre vaut par défaut 0,005.
• cpu_operator_cost indique le coût d'application d'un opérateur sur une donnée. Ce paramètre vaut par défaut 0,0025.
• parallel_tuple_cost indique le coût de traitement d'une ligne lors d'un parcours parallélisé. Ce paramètre vaut par défaut 0.1.
• parallel_setup_cost indique le coût de mise en place d'un parcours parallélisé. Ce paramètre vaut par défaut 1000.0.

En général, on ne modifie pas ces paramètres sans justification sérieuse. On peut être amené à diminuer random_page_cost si le serveur dispose de disques rapides et d'une carte RAID équipée d'un cache important. Mais en faisant cela, il faut veiller à ne pas déstabiliser des plans optimaux qui obtiennent des temps de réponse constant. À trop diminuer random_page_cost, on peut obtenir de meilleurs temps de réponse si les données sont en cache, mais aussi des temps de réponse dégradés si les données ne sont pas en cache. Il n'est pas recommandé de modifier les paramètres cpu_tuple_cost, cpu_index_tuple_cost et cpu_operator_cost sans réelle justification.

Pour des besoins particuliers, ces paramètres sont des paramètres de sessions. Ils peuvent être modifiés dynamiquement avec l'ordre SET au niveau de l'application en vue d'exécuter des requêtes bien particulières.

Le planificateur se base principalement sur les statistiques pour ses décisions. Le choix du parcours, le choix des jointures, le choix de l'ordre des jointures, tout cela dépend des statistiques (et un peu de la configuration). Sans statistiques à jour, le choix du planificateur a un fort risque d'être mauvais. Il est donc important que les statistiques soient mises à jour fréquemment. La mise à jour se fait avec l'instruction ANALYZE qui peut être exécuté manuellement ou automatiquement (via un cron ou l'autovacuum par exemple).

Les statistiques sur les données permettent à l'optimiseur de requêtes de déterminer assez précisément la répartition des valeurs d'une colonne d'une table, sous la forme d'un histogramme de répartition des valeurs. Il dispose encore d'autres informations comme la répartition des valeurs les plus fréquentes, le pourcentage de NULL, le nombre de valeurs distinctes, etc... Toutes ces informations aideront l'optimiseur à déterminer la sélectivité d'un filtre (prédicat de la clause WHERE, condition de jointure) et donc quelle sera la quantité de données récupérées par la lecture d'une table en utilisant le filtre évalué.

Par exemple, pour une table simple, nommée test, de 1 million de lignes dont 250000 lignes ont des valeurs uniques et les autres portent la même valeur :

CREATE TABLE test (i integer not null, t text);
INSERT INTO test SELECT CASE WHEN i > 250000 THEN 250000 ELSE i END,
md5(i::text) FROM generate_series(1, 1000000) i;
CREATE INDEX ON test (i);

Après un chargement massif de données, il est nécessaire de collecter les statistiques :

ANALYZE test;

Ensuite, grâce aux statistiques connues par PostgreSQL (voir la vue pg_stats), l'optimiseur est capable de déterminer le chemin le plus intéressant selon les valeurs recherchées.

Ainsi, avec un filtre peu sélectif, i = 250000, la requête va ramener les 3/ 4 de la table. PostgreSQL choisira donc une lecture séquentielle de la table, ou Seq Scan :

base=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM test WHERE i = 250000;
                                   QUERY PLAN
------------------------------------------------------------------------------
 Seq Scan on test  (cost=[...] rows=752400) (actual [...] rows=750001 loops=1)
   Filter: (i = 250000)
   Rows Removed by Filter: 249999
   Buffers: shared hit=8334
 Total runtime: 244.605 ms
(5 lignes)

La partie cost montre que l'optimiseur estime que la lecture va ramener 752400 lignes. En réalité, ce sont 750001 lignes qui sont ramenées. L'optimiseur se base donc sur une estimation obtenue selon la répartition des données.

Avec un filtre plus sélectif, la requête ne ramènera qu'une seule ligne. L'optimiseur préférera donc passer par l'index que l'on a créé :

base=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM test WHERE i = 250;
                                      QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on test  ([...] rows=25 width=37) ([...] rows=1 loops=1)
   Recheck Cond: (i = 250)
   Buffers: shared hit=4
   ->  Bitmap Index Scan on test_i_idx  ([...] rows=25) ([...] rows=1 loops=1)
         Index Cond: (i = 250)
         Buffers: shared hit=3
 Total runtime: 0.134 ms
(7 lignes)

Dans ce deuxième essai, l'optimiseur estime ramener 25 lignes. En réalité, il n'en ramène qu'une seule. L'estimation reste relativement précise étant donné le volume de données.

Dans le premier cas, l'optimiseur estime qu'il est moins coûteux de passer par une lecture séquentielle de la table plutôt qu'une lecture d'index. Dans le second cas, où le filtre est très sélectif, une lecture par index est plus appropriée.

L'optimiseur a besoin de deux données statistiques pour une table ou un index : sa taille physique et le nombre de lignes portées par l'objet.

Ces deux données statistiques sont stockées dans la table pg_class. La taille de la table ou de l'index est exprimée en nombre de blocs de 8 Ko et stockée dans la colonne relpages. La cardinalité de la table ou de l'index, c'est-à-dire le nombre de lignes, est stockée dans la colonne reltuples.

L'optimiseur utilisera ces deux informations pour apprécier la cardinalité de la table en fonction de sa volumétrie courante en calculant sa densité estimée puis en utilisant cette densité multipliée par le nombre de blocs actuel de la table pour estimer le nombre de lignes réel de la table :

  density = reltuples / relpages;
  tuples = density * curpages;

Au niveau d'une colonne, plusieurs données statistiques sont stockées :

• le nombre de valeurs distinctes,
• le nombre d'éléments qui n'ont pas de valeur (NULL),
• la largeur moyenne des données portées par la colonne,
• le facteur de corrélation entre l'ordre des données triées et la répartition physique des valeurs dans la table,
• la distribution des données.

La distribution des données est représentée sous deux formes qui peuvent être complémentaires. Tout d'abord, un tableau de répartition permet de connaître les valeurs les plus fréquemment rencontrées et la fréquence d'apparition de ces valeurs. Un histogramme de distribution des valeurs rencontrées permet également de connaître la répartition des valeurs pour la colonne considérée.

Le stockage des statistiques se fait dans le catalogue système pg_statistic mais les colonnes de cette table sont difficiles à interpréter. Il est préférable de passer par la vue pg_stats qui est plus facilement compréhensible par un être humain.

La collecte des statistiques va également mettre à jour la table pg_class avec deux informations importantes pour l'optimiseur. Il s'agit de la taille d'une table, exprimée en nombre de blocs de 8 Ko et stockée dans la colonne relpages. La cardinalité de la table, c'est-à-dire le nombre de lignes de la table, est stockée dans la colonne reltuples. L'optimiseur utilisera ces deux informations pour apprécier la cardinalité de la table en fonction de sa volumétrie courante.

La vue pg_stats a été créée pour faciliter la compréhension des statistiques récupérées par la commande ANALYZE.

Elle est composée de trois colonnes qui permettent d'identifier la colonne :

• schemaname : nom du schéma (jointure possible avec pg_namespace)
• tablename : nom de la table (jointure possible avec pg_class, intéressant pour récupérer reltuples et relpages)
• attname : nom de la colonne (jointure possible avec pg_attribute, intéressant pour récupérer attstatstarget, valeur d'échantillon)

Suivent ensuite les colonnes de statistiques.

inherited

Si true, les statistiques incluent les valeurs de cette colonne dans les tables filles.

Exemple

b1=# SELECT count(*) FROM ONLY parent;
-[ RECORD 1 ]
count | 0
b1=# SELECT * FROM pg_stats WHERE tablename='parent';
-[ RECORD 1 ]-----+-----------------------------------------------------
schemaname        | public
tablename         | parent
attname           | id
inherited         | t
null_frac         | 0
avg_width         | 4
n_distinct        | -0.285714
most_common_vals  | {1,2,17,18,19,20,3,4,5,15,16,6,7,8,9,10}
[...]
histogram_bounds  | {11,12,13,14}
correlation       | 0.762715

null_frac

Cette statistique correspond au pourcentage de valeurs NULL dans l'échantillon considéré. Elle est toujours calculée.

avg_width

Il s'agit de la largeur moyenne en octets des éléments de cette colonne. Elle est constante pour les colonnes dont le type est à taille fixe (integer, booléen, char, etc.). Dans le cas du type char(n), il s'agit du nombre de caractères saisissables + 1. Il est variable pour les autres (principalement text, varchar, bytea).

n_distinct

Si cette colonne contient un nombre positif, il s'agit du nombre de valeurs distinctes dans l'échantillon. Cela arrive uniquement quand le nombre de valeurs distinctes possibles semble fixe.

Si cette colonne contient un nombre négatif, il s'agit du nombre de valeurs distinctes dans l'échantillon divisé par le nombre de lignes. Cela survient uniquement quand le nombre de valeurs distinctes possibles semble variable. -1 indique donc que toutes les valeurs sont distinctes, -0,5 que chaque valeur apparaît deux fois.

Cette colonne peut être NULL si le type de données n'a pas d'opérateur =.

Il est possible de forcer cette colonne a une valeur constante en utilisant l'ordre ALTER TABLE nom_table ALTER COLUMN nom_colonne SET (parametre = valeur); où parametre vaut soit n_distinct (pour une table standard) soit n_distinct_inherited (pour une table comprenant des partitions). Pour les grosses tables contenant des valeurs distinctes, indiquer une grosse valeur ou la valeur -1 permet de favoriser l'utilisation de parcours d'index à la place de parcours de bitmap. C'est aussi utile pour des tables où les données ne sont pas réparties de façon homogène, et où la collecte de cette statistique est alors faussée.

most_common_vals

Cette colonne contient une liste triée des valeurs les plus communes. Elle peut être NULL si les valeurs semblent toujours aussi communes ou si le type de données n'a pas d'opérateur =.

most_common_freqs

Cette colonne contient une liste triée des fréquences pour les valeurs les plus communes. Cette fréquence est en fait le nombre d’occurrences de la valeur divisé par le nombre de lignes. Elle est NULL si most_common_vals est NULL.

histogram_bounds

PostgreSQL prend l'échantillon récupéré par ANALYZE. Il trie ces valeurs. Ces données triées sont partagées en x tranches, appelées classes, égales, où x dépend de la valeur du paramètre default_statistics_target ou de la configuration spécifique de la colonne. Il construit ensuite un tableau dont chaque valeur correspond à la valeur de début d'une tranche.

most_common_elems, most_common_elem_freqs, elem_count_histogram

Ces trois colonnes sont équivalentes aux trois précédentes, mais uniquement pour les données de type tableau.

correlation

Cette colonne est la corrélation statistique entre l'ordre physique et l'ordre logique des valeurs de la colonne. Si sa valeur est proche de -1 ou 1, un parcours d'index est privilégié. Si elle est proche de 0, un parcours séquentiel est mieux considéré.

Cette colonne peut être NULL si le type de données n'a pas d'opérateur <.

Par défaut, la commande ANALYZE de PostgreSQL calcule des statistiques mono-colonnes uniquement. Depuis la version 10, elle peut aussi calculer certaines statistiques multi-colonnes.

Pour cela, il est nécessaire de créer un objet statistique avec l'ordre SQL CREATE STATISTICS. Cet objet indique les colonnes concernées ainsi que le type de statistique souhaité.

Actuellement, PostgreSQL supporte deux types de statistiques pour ces objets :

• ndistinct pour le nombre de valeurs distinctes sur ces colonnes ;
• dependencies pour les dépendances fonctionnelles.

Dans les deux cas, cela peut permettre d'améliorer fortement les estimations de nombre de lignes, ce qui ne peut qu'amener de meilleurs plans d'exécution.

stxname est le nom de l'objet statistique, et stxnamespace l'OID de son schéma.

stxrelid précise l'OID de la table concernée par cette statistique. stxkeys est un tableau d'entiers correspondant aux numéros des colonnes.

stxkind peut avoir une ou plusieurs valeurs parmi d pour le nombre de valeurs distinctes et f pour les dépendances fonctionnelles.

Créons une table avec deux colonnes et peuplons-la avec les mêmes données :

postgres=# CREATE TABLE t (a INT, b INT);
CREATE TABLE
postgres=# INSERT INTO t SELECT i % 100, i % 100 FROM generate_series(1, 10000) s(i);
INSERT 0 10000
postgres=# ANALYZE t;
ANALYZE

Après une analyse des données de la table, les statistiques sont à jour comme le montrent ces deux requêtes :

postgres=# EXPLAIN (ANALYZE) SELECT * FROM t WHERE a = 1;
                    QUERY PLAN
-----------------------------------------------------
 Seq Scan on t  (cost=0.00..170.00 rows=100 width=8)
         (actual time=0.037..1.704 rows=100 loops=1)
   Filter: (a = 1)
   Rows Removed by Filter: 9900
 Planning time: 0.097 ms
 Execution time: 1.731 ms
(5 rows)

postgres=# EXPLAIN (ANALYZE) SELECT * FROM t WHERE b = 1;
                    QUERY PLAN
-----------------------------------------------------
 Seq Scan on t  (cost=0.00..170.00 rows=100 width=8)
         (actual time=0.010..1.590 rows=100 loops=1)
   Filter: (b = 1)
   Rows Removed by Filter: 9900
 Planning time: 0.029 ms
 Execution time: 1.609 ms
(5 rows)

Cela fonctionne bien (i.e. l'estimation du nombre de lignes est très proche de la réalité) dans le cas spécifique où le filtre se fait sur une seule colonne. Par contre, si le filtre se fait sur les deux colonnes, l'estimation diffère d'un facteur d'échelle :

postgres=# EXPLAIN (ANALYZE) SELECT * FROM t WHERE a = 1 AND b = 1;
                    QUERY PLAN
---------------------------------------------------
 Seq Scan on t  (cost=0.00..195.00 rows=1 width=8)
       (actual time=0.009..1.554 rows=100 loops=1)
   Filter: ((a = 1) AND (b = 1))
   Rows Removed by Filter: 9900
 Planning time: 0.044 ms
 Execution time: 1.573 ms
(5 rows)

En fait, il y a une dépendance fonctionnelle entre ces deux colonnes mais PostgreSQL ne le sait pas car ses statistiques sont mono-colonnes par défaut. Pour avoir des statistiques sur les deux colonnes, il faut créer un objet statistique pour ces deux colonnes :

postgres=# CREATE STATISTICS stts (dependencies) ON a, b FROM t;
CREATE STATISTICS
postgres=# ANALYZE t;
ANALYZE
postgres=# EXPLAIN (ANALYZE) SELECT * FROM t WHERE a = 1 AND b = 1;
                    QUERY PLAN
-----------------------------------------------------
 Seq Scan on t  (cost=0.00..195.00 rows=100 width=8)
         (actual time=0.007..0.668 rows=100 loops=1)
   Filter: ((a = 1) AND (b = 1))
   Rows Removed by Filter: 9900
 Planning time: 0.093 ms
 Execution time: 0.683 ms
(5 rows)

Cette fois, l'estimation est beaucoup plus proche de la réalité.

Maintenant, prenons le cas d'un regroupement :

postgres=# EXPLAIN (ANALYZE) SELECT COUNT(*) FROM t GROUP BY a;
                          QUERY PLAN
-------------------------------------------------------------
 HashAggregate  (cost=195.00..196.00 rows=100 width=12)
            (actual time=2.346..2.358 rows=100 loops=1)
   Group Key: a
   ->  Seq Scan on t  (cost=0.00..145.00 rows=10000 width=4)
               (actual time=0.006..0.640 rows=10000 loops=1)
 Planning time: 0.024 ms
 Execution time: 2.381 ms
(5 rows)

L'estimation du nombre de lignes pour un regroupement sur une colonne est très bonne. Par contre, sur deux colonnes :

postgres=# EXPLAIN (ANALYZE) SELECT COUNT(*) FROM t GROUP BY a, b;
                           QUERY PLAN
-------------------------------------------------------------
 HashAggregate  (cost=220.00..230.00 rows=1000 width=16)
             (actual time=2.321..2.339 rows=100 loops=1)
   Group Key: a, b
   ->  Seq Scan on t  (cost=0.00..145.00 rows=10000 width=8)
               (actual time=0.004..0.596 rows=10000 loops=1)
 Planning time: 0.025 ms
 Execution time: 2.359 ms
(5 rows)

Là-aussi, on constate un facteur d'échelle important entre l'estimation et la réalité. Et là-aussi, c'est un cas où un objet statistique peut fortement aider :

postgres=# DROP STATISTICS stts;
DROP STATISTICS
postgres=# CREATE STATISTICS stts (dependencies, ndistinct) ON a, b FROM t;
CREATE STATISTICS
postgres=# ANALYZE t;
ANALYZE
postgres=# EXPLAIN (ANALYZE) SELECT COUNT(*) FROM t GROUP BY a, b;
                      QUERY PLAN
-------------------------------------------------------------
 HashAggregate  (cost=220.00..221.00 rows=100 width=16)
            (actual time=3.310..3.324 rows=100 loops=1)
   Group Key: a, b
   ->  Seq Scan on t  (cost=0.00..145.00 rows=10000 width=8)
               (actual time=0.007..0.807 rows=10000 loops=1)
 Planning time: 0.087 ms
 Execution time: 3.356 ms
(5 rows)

L'estimation est bien meilleure grâce aux statistiques spécifiques aux deux colonnes.

ANALYZE est l'ordre SQL permettant de mettre à jour les statistiques sur les données. Sans argument, l'analyse se fait sur la base complète. Si un argument est donné, il doit correspondre au nom de la table à analyser. Il est même possible d'indiquer les colonnes à traiter.

En fait, cette instruction va exécuter un calcul d'un certain nombre de statistiques. Elle ne va pas lire la table entière, mais seulement un échantillon. Sur cet échantillon, chaque colonne sera traitée pour récupérer quelques informations comme le pourcentage de valeurs NULL, les valeurs les plus fréquentes et leur fréquence, sans parler d'un histogramme des valeurs. Toutes ces informations sont stockées dans un catalogue système nommé pg_statistics.

Les statistiques doivent être mises à jour fréquemment. La fréquence exacte dépend surtout de la fréquence des requêtes d'insertion/modification/ suppression des lignes des tables. Néanmoins, un ANALYZE tous les jours semble un minimum, sauf cas spécifique.

L'exécution périodique peut se faire avec cron (ou les tâches planifiées sous Windows). Il n'existe pas d'outils PostgreSQL pour lancer un seul ANALYZE . L'outil vaccumdb se voit doté d'une option --analyze-only pour combler ce manque. Avant, il était nécessaire de passer par psql et son option -c qui permet de préciser la requête à exécuter. En voici un exemple :

psql -c "ANALYZE" b1

Cet exemple exécute la commande ANALYZE sur la base b1 locale.

Le démon autovacuum fait aussi des ANALYZE. La fréquence dépend de sa configuration. Cependant, il faut connaître deux particularités de cet outil :

• Ce démon a sa propre connexion à la base. Il ne peut donc pas voir les tables temporaires appartenant aux autres sessions. Il ne sera donc pas capable de mettre à jour leurs statistiques.
• Après une insertion ou une mise à jour massive, autovacuum ne va pas forcément lancer un ANALYZE immédiat. En effet, autovacuum ne cherche les tables à traiter que toutes les minutes (par défaut). Si, après la mise à jour massive, une requête est immédiatement exécutée, il y a de fortes chances qu'elle s'exécute avec des statistiques obsolètes. Il est préférable dans ce cas de lancer un ANALYZE manuel sur la ou les tables ayant subi l'insertion ou la mise à jour massive.

Par défaut, un ANALYZE récupère 30000 lignes d'une table. Les statistiques générées à partir de cet échantillon sont bonnes si la table ne contient pas des millions de lignes. Si c'est le cas, il faudra augmenter la taille de l'échantillon. Pour cela, il faut augmenter la valeur du paramètre default_statistics_target. Ce dernier vaut 100 par défaut. La taille de l'échantillon est de 300 x default_statistics_target. Augmenter ce paramètre va avoir plusieurs répercussions. Les statistiques seront plus précises grâce à un échantillon plus important. Mais du coup, les statistiques seront plus longues à calculer, prendront plus de place sur le disque, et demanderont plus de travail au planificateur pour générer le plan optimal. Augmenter cette valeur n'a donc pas que des avantages.

Du coup, les développeurs de PostgreSQL ont fait en sorte qu'il soit possible de le configurer colonne par colonne avec l'instruction suivante :

ALTER TABLE nom_table ALTER [ COLUMN ] nom_colonne SET STATISTICS valeur;

Les nœuds correspondent à des unités de traitement qui réalisent des opérations simples sur un ou deux ensembles de données : lecture d'une table, jointures entre deux tables, tri d'un ensemble, etc. Si le plan d'exécution était une recette, chaque nœud serait une étape de la recette.

Les nœuds peuvent produire et consommer des données.

Un plan d'exécution est lu en partant du nœud se trouvant le plus à droite et en remontant jusqu'au nœud final. Quand le plan contient plusieurs nœuds, le premier nœud exécuté est celui qui se trouve le plus à droite. Celui qui est le plus à gauche (la première ligne) est le dernier nœud exécuté. Tous les nœuds sont exécutés simultanément, et traitent les données dès qu'elles sont transmises par le nœud parent (le ou les nœuds juste en dessous, à droite).

Chaque nœud montre les coûts estimés dans le premier groupe de parenthèses :

• cost est un couple de deux coûts
• la première valeur correspond au coût pour récupérer la première ligne (souvent nul dans le cas d'un parcours séquentiel) ;
• la deuxième valeur correspond au coût pour récupérer toutes les lignes (cette valeur dépend essentiellement de la taille de la table lue, mais aussi de l'opération de filtre ici présente) ;
• rows correspond au nombre de lignes que le planificateur pense récupérer à la sortie de ce nœud ;
• width est la largeur en octets de la ligne.

Cet exemple simple permet de voir le travail de l'optimiseur :

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

                               QUERY PLAN
-------------------------------------------------------------------------

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

Ce plan débute par la lecture de la table services. L'optimiseur estime que cette lecture ramènera une seule ligne (rows=1), que cette ligne occupera 21 octets en mémoire (width=21). Il s'agit de la sélectivité du filtre WHERE localisation = 'Nantes'. Le coût de départ de cette lecture est de 0 (cost=0.00). Le coût total de cette lecture est de 1.05, qui correspond à la lecture séquentielle d'un seul bloc (donc seq_page_cost) et à la manipulation des 4 lignes de la tables services (donc 4 * cpu_tuple_cost + 4 * cpu_operator_cost). Le résultat de cette lecture est ensuite haché par le nœud Hash, qui précède la jointure de type Hash Join.

La jointure peut maintenant commencer, avec le nœud Hash Join. Il est particulier, car il prend 2 entrées : la donnée hachée initialement, et les données issues de la lecture d'une seconde table (peu importe le type d'accès). Le nœud a un coût de démarrage de 1.06, soit le coût du hachage additionné au coût de manipulation du tuple de départ. Il s'agit du coût de production du premier tuple de résultat. Le coût total de production du résultat est de 2.29. La jointure par hachage démarre réellement lorsque la lecture de la table employes commence. Cette lecture remontera 14 lignes, sans application de filtre. La totalité de la table est donc remontée et elle est très petite donc tient sur un seul bloc de 8 Ko. Le coût d'accès total est donc facilement déduit à partir de cette information. À partir des sélectivités précédentes, l'optimiseur estime que la jointure ramènera 4 lignes au total.

Au fil des versions, EXPLAIN a gagné en options. L'une d'entre elles permet de sélectionner le format en sortie. Toutes les autres permettent d'obtenir des informations supplémentaires.

Option ANALYZE

Le but de cette option est d'obtenir les informations sur l'exécution réelle de la requête.

Voici un exemple utilisant cette option :

b1=# EXPLAIN ANALYZE SELECT * FROM t1 WHERE c1 <1000;
                               QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..17.50 rows=1000 width=8)
                (actual time=0.015..0.504 rows=999 loops=1)
   Filter: (c1 < 1000)
 Total runtime: 0.766 ms
(3 rows)

Quatre nouvelles informations apparaissent, toutes liées à l'exécution réelle de la requête :

• actual time
• la première valeur correspond à la durée en milliseconde pour récupérer la première ligne ;
• la deuxième valeur est la durée en milliseconde pour récupérer toutes les lignes ;
• rows est le nombre de lignes réellement récupérées ;
• loops est le nombre d'exécution de ce nœud.

L'intérêt de cette option est donc de trouver l'opération qui prend du temps dans l'exécution de la requête, mais aussi de voir les différences entre les estimations et la réalité (notamment au niveau du nombre de lignes).

Option BUFFERS

Cette option apparaît en version 9.1. Elle n'est utilisable qu'avec l'option ANALYZE. Elle est désactivée par défaut.

Elle indique le nombre de blocs impactés par chaque nœud du plan d'exécution, en lecture comme en écriture.

Voici un exemple de son utilisation :

b1=# EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM t1 WHERE c1 <1000;
                                QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..17.50 rows=1000 width=8)
                 (actual time=0.015..0.493 rows=999 loops=1)
   Filter: (c1 < 1000)
   Buffers: shared hit=5
 Total runtime: 0.821 ms
(4 rows)

La nouvelle ligne est la ligne Buffers. Elle peut contenir un grand nombre d'informations :

Option COSTS

L'option COSTS apparaît avec la version 9.0. Une fois activée, elle indique les estimations du planificateur.

b1=# EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE c1 <1000;
           QUERY PLAN
--------------------------------
 Seq Scan on t1
   Filter: (c1 < 1000)
(2 rows)

b1=# EXPLAIN (COSTS ON) SELECT * FROM t1 WHERE c1 <1000;
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..17.50 rows=1000 width=8)
   Filter: (c1 < 1000)
(2 rows)

Option TIMING

Cette option n'est disponible que depuis la version 9.2. Elle n'est utilisable qu'avec l'option ANALYZE.

Elle ajoute les informations sur les durées en milliseconde. Elle est activée par défaut. Sa désactivation peut être utile sur certains systèmes où le chronométrage prend beaucoup de temps et allonge inutilement la durée d'exécution de la requête.

Voici un exemple de son utilisation :

b1=# EXPLAIN (ANALYZE,TIMING ON) SELECT * FROM t1 WHERE c1 <1000;
                                 QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..17.50 rows=1000 width=8)
                 (actual time=0.017..0.520 rows=999 loops=1)
   Filter: (c1 < 1000)
   Rows Removed by Filter: 1
 Total runtime: 0.783 ms
(4 rows)

b1=# EXPLAIN (ANALYZE,TIMING OFF) SELECT * FROM t1 WHERE c1 <1000;
                                 QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..17.50 rows=1000 width=8) (actual rows=999 loops=1)
   Filter: (c1 < 1000)
   Rows Removed by Filter: 1
 Total runtime: 0.418 ms
(4 rows)

Option VERBOSE

L'option VERBOSE permet d'afficher des informations supplémentaires comme la liste des colonnes en sortie, le nom de la table qualifié du schéma, le nom de la fonction qualifié du schéma, le nom du trigger, etc. Elle est désactivée par défaut.

b1=# EXPLAIN (VERBOSE) SELECT * FROM t1 WHERE c1 <1000;
                               QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on public.t1  (cost=0.00..17.50 rows=1000 width=8)
   Output: c1, c2
   Filter: (t1.c1 < 1000)
(3 rows)

On voit dans cet exemple que le nom du schéma est ajouté au nom de la table. La nouvelle section Output indique la liste des colonnes de l'ensemble de données en sortie du nœud.

Option SUMMARY

Cette option apparaît en version 10. Elle permet d'afficher ou non le résumé final indiquant la durée de la planification et de l'exécution. Un EXPLAIN simple n'affiche pas le résumé par défaut. Par contre, un EXPLAIN ANALYZE l'affiche par défaut.

b1=# EXPLAIN SELECT * FROM t1;
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on t1  (cost=0.00..35.50 rows=2550 width=4)
(1 row)

b1=# EXPLAIN (SUMMARY on) SELECT * FROM t1;
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on t1  (cost=0.00..35.50 rows=2550 width=4)
 Planning time: 0.080 ms
(2 rows)

b1=# EXPLAIN (ANALYZE) SELECT * FROM t1;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..35.50 rows=2550 width=4)
            (actual time=0.004..0.004 rows=0 loops=1)
 Planning time: 0.069 ms
 Execution time: 0.037 ms
(3 rows)

b1=# EXPLAIN (ANALYZE, SUMMARY off) SELECT * FROM t1;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..35.50 rows=2550 width=4)
            (actual time=0.002..0.002 rows=0 loops=1)
(1 row)

Option FORMAT

L'option FORMAT apparaît en version 9.0. Elle permet de préciser le format du texte en sortie. Par défaut, il s'agit du texte habituel, mais il est possible de choisir un format balisé parmi XML, JSON et YAML. Voici ce que donne la commande EXPLAIN avec le format XML :

b1=# EXPLAIN (FORMAT XML) SELECT * FROM t1 WHERE c1 <1000;
                        QUERY PLAN
----------------------------------------------------------
 <explain xmlns="http://www.postgresql.org/2009/explain">+
   <Query>                                               +
     <Plan>                                              +
       <Node-Type>Seq Scan</Node-Type>                   +
       <Relation-Name>t1</Relation-Name>                 +
       <Alias>t1</Alias>                                 +
       <Startup-Cost>0.00</Startup-Cost>                 +
       <Total-Cost>17.50</Total-Cost>                    +
       <Plan-Rows>1000</Plan-Rows>                       +
       <Plan-Width>8</Plan-Width>                        +
       <Filter>(c1 &lt; 1000)</Filter>                   +
     </Plan>                                             +
   </Query>                                              +
 </explain>
(1 row)

Lorsqu'une requête s'exécute lentement, cela peut être un problème dans le plan. La sortie de EXPLAIN peut apporter quelques informations qu'il faut savoir décoder. Une différence importante entre le nombre de lignes estimé et le nombre de lignes réel laisse un doute sur les statistiques présentes. Soit elles n'ont pas été réactualisées récemment, soit l'échantillon n'est pas suffisamment important pour que les statistiques donnent une vue proche du réel du contenu de la table.

L'option BUFFERS d'EXPLAIN permet également de mettre en valeur les opérations d'entrées/sorties lourdes. Cette option affiche notamment le nombre de blocs lus en/hors cache de PostgreSQL, sachant qu'un bloc fait généralement 8 Ko, il est aisé de déterminer le volume de données manipulé par une requête.

Afin de comparer les différents plans d'exécution possibles pour une requête et choisir le meilleur, l'optimiseur a besoin d'estimer un coût pour chaque nœud du plan.

L'estimation la plus cruciale est celle liée aux nœuds de parcours de données, car c'est d'eux que découlera la suite du plan. Pour estimer le coût de ces nœuds, l'optimiseur s'appuie sur les informations statistiques collectées, ainsi que sur la valeur de paramètres de configuration.

Les deux notions principales de ce calcul sont la cardinalité (nombre de lignes estimées en sortie d'un nœud) et la sélectivité (fraction des lignes conservées après l'application d'un filtre).

Voici ci-dessous un exemple de calcul de cardinalité et de détermination du coût associé.

Calcul de cardinalité

Pour chaque prédicat et chaque jointure, PostgreSQL va calculer sa sélectivité et sa cardinalité. Pour un prédicat, cela permet de déterminer le nombre de lignes retournées par le prédicat par rapport au nombre total de lignes de la table. Pour une jointure, cela permet de déterminer le nombre de lignes retournées par la jointure entre deux tables.

L'optimiseur dispose de plusieurs façons de calculer la cardinalité d'un filtre ou d'une jointure selon que la valeur recherchée est une valeur unique, que la valeur se trouve dans le tableau des valeurs les plus fréquentes ou dans l'histogramme. L'exemple ci-dessous montre comment calculer la cardinalité d'un filtre simple sur une table pays de 25 lignes. La valeur recherchée se trouve dans le tableau des valeurs les plus fréquentes, la cardinalité peut être calculée directement. Si ce n'était pas le cas, il aurait fallu passer par l'histogramme des valeurs pour calculer d'abord la sélectivité du filtre pour en déduire ensuite la cardinalité.

Dans l'exemple qui suit, une table pays contient 25 entrées

La requête suivante permet de récupérer la fréquence d'apparition de la valeur recherchée dans le prédicat WHERE region_id = 1 :

SELECT tablename, attname, value, freq
  FROM (SELECT tablename, attname, mcv.value, mcv.freq FROM pg_stats,
              LATERAL ROWS FROM (unnest(most_common_vals::text::int[]),
                                 unnest(most_common_freqs)) AS mcv(value, freq)
         WHERE tablename = 'pays'
           AND attname = 'region_id') get_mcv
 WHERE value = 1;
 tablename |  attname  | value | freq
-----------+-----------+-------+------
 pays      | region_id |     1 |  0.2
(1 row)

L'optimiseur calcule la cardinalité du prédicat WHERE region_id = 1 en multipliant cette fréquence de la valeur recherchée avec le nombre total de lignes de la table :

SELECT 0.2 * reltuples AS cardinalite_predicat
  FROM pg_class
 WHERE relname = 'pays';
 cardinalite_predicat
----------------------
                    5
(1 row)

On peut vérifier que le calcul est bon en obtenant le plan d'exécution de la requête impliquant la lecture de pays sur laquelle on applique le prédicat évoqué plus haut :

EXPLAIN SELECT * FROM pays WHERE region_id = 1;
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on pays  (cost=0.00..1.31 rows=5 width=49)
   Filter: (region_id = 1)
(2 rows)

Calcul de coût

Une table pays peuplée de 25 lignes va permettre de montrer le calcul des coûts réalisés par l'optimiseur. L'exemple présenté ci-dessous est simplifié. En réalité, les calculs sont plus complexes car ils tiennent également compte de la volumétrie réelle de la table.

Le coût de la lecture séquentielle de la table pays est calculé à partir de deux composantes. Toute d'abord, le nombre de pages (ou blocs) de la table permet de déduire le nombre de blocs à accéder pour lire la table intégralement. Le paramètre seq_page_cost sera appliqué ensuite pour indiquer le coût de l'opération :

SELECT relname, relpages * current_setting('seq_page_cost')::float AS cout_acces
  FROM pg_class
 WHERE relname = 'pays';
 relname | cout_acces
---------+------------
 pays    |          1

Cependant, le coût d'accès seul ne représente pas le coût de la lecture des données. Une fois que le bloc est monté en mémoire, PostgreSQL doit décoder chaque ligne individuellement. L'optimiseur utilise cpu_tuple_cost pour estimer le coût de manipulation des lignes :

SELECT relname,
       relpages * current_setting('seq_page_cost')::float
       + reltuples * current_setting('cpu_tuple_cost')::float AS cout
  FROM pg_class
 WHERE relname = 'pays';
 relname | cout
---------+------
 pays    | 1.25

On peut vérifier que le calcul est bon :

EXPLAIN SELECT * FROM pays;
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on pays  (cost=0.00..1.25 rows=25 width=53)
(1 ligne)

Si l'on applique un filtre à la requête, les traitements seront plus lourds. Par exemple, en ajoutant le prédicat WHERE pays = 'FR'.

Il faut non seulement extraire les lignes les unes après les autres, mais il faut également appliquer l'opérateur de comparaison utilisé. L'optimiseur utilise le paramètre cpu_operator_cost pour déterminer le coût d'application d'un filtre :

SELECT relname,
       relpages * current_setting('seq_page_cost')::float
       + reltuples * current_setting('cpu_tuple_cost')::float
       + reltuples * current_setting('cpu_operator_cost')::float AS cost
  FROM pg_class
 WHERE relname = 'pays';
 relname |  cost
---------+--------
 pays    | 1.3125

En récupérant le plan d'exécution de la requête à laquelle est appliqué le filtre WHERE pays = 'FR', on s'aperçoit que le calcul est juste, à l'arrondi près :

EXPLAIN SELECT * FROM pays WHERE code_pays = 'FR';
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on pays  (cost=0.00..1.31 rows=1 width=53)
   Filter: (code_pays = 'FR'::text)
(2 lignes)

Pour aller plus loin dans le calcul de sélectivité, de cardinalité et de coût, la documentation de PostgreSQL montre un exemple complet de calcul de sélectivité et indique les références des fichiers sources dans lesquels fouiller pour en savoir plus :Comment le planificateur utilise les statistiques.

Les parcours sont les seules opérations qui lisent les données des tables (normales, temporaires ou non journalisées). Elles ne prennent donc rien en entrée et fournissent un ensemble de données en sortie. Cet ensemble peut être trié ou non, filtré ou non.

Il existe plusieurs types de parcours possibles. Parmi les plus fréquents, on retrouve :

• le parcours de table ;
• le parcours de fonction ;
• les parcours d'index.

Depuis la version 9.6, les parcours de table sont parallélisables.

Les parcours d'index sont documentés par la suite.

L'opération Seq Scan correspond à une lecture séquentielle d'une table, aussi appelée Full Table Scan sur d'autres SGBD. Il consiste à lire l'intégralité de la table, du premier bloc au dernier bloc. Une clause de filtrage peut être appliquée.

On retrouve ce noeud lorsque la requête nécessite de lire l'intégralité de la table :

cave=# EXPLAIN SELECT * FROM region;
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on region  (cost=0.00..1.19 rows=19 width=15)

Ce noeud peut également filtrer directement les données, la présence de la clause Filter montre le filtre appliqué à la lecture des données :

cave=# EXPLAIN SELECT * FROM region WHERE id=5;
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on region  (cost=0.00..1.24 rows=1 width=15)
   Filter: (id = 5)

Le coût d'accès pour ce type de noeud sera dépendant du nombre de blocs à parcourir et du paramètre seq_page_cost.

Il est possible d'avoir un parcours parallélisé d'une table sous certaines conditions (la première étant qu'il faut avoir au minimum une version 9.6). Pour que ce type de parcours soit valable, il faut que l'optimiseur soit persuadé que le problème sera le temps CPU et non la bande passante disque. Autrement dit, dans la majorité des cas, il faut un filtre pour que la parallélisation se déclenche et il faut que la table soit suffisamment volumineuse.

postgres=# CREATE TABLE t20 AS SELECT id FROM generate_series(1, 1000000) g(id);
postgres=# SET max_parallel_workers_per_gather TO 6;
postgres=# EXPLAIN SELECT * FROM t20 WHERE id<10000;
                               QUERY PLAN
------------------------------------------------------------------------
 Gather  (cost=1000.00..11676.13 rows=10428 width=4)
   Workers Planned: 2
   ->  Parallel Seq Scan on t20  (cost=0.00..9633.33 rows=4345 width=4)
         Filter: (id < 10000)
(4 rows)

Ici, deux processus supplémentaires seront exécutés pour réaliser la requête. Dans le cas de ce type de parcours, chaque processus traite toutes les lignes d'un bloc. Enfin quand un processus a terminé de traiter son bloc, il regarde quel est le prochain bloc à traiter et le traite.

On retrouve le noeud Function Scan lorsqu'une requête utilise directement le résultat d'une fonction. C'est un noeud que l'on rencontre lorsqu'on utilise les fonctions d'informations systèmes de PostgreSQL :

postgres=# EXPLAIN SELECT * from pg_get_keywords();
                              QUERY PLAN
-----------------------------------------------------------------------
 Function Scan on pg_get_keywords  (cost=0.03..4.03 rows=400 width=65)
(1 ligne)

En dehors des différents parcours d'index, on retrouve également d'autres types de parcours, mais PostgreSQL les utilise rarement. Ils sont néanmoins détaillés en annexe.

PostgreSQL dispose de trois moyens d'accéder aux données à travers les index.

Le noeud Index Scan est le premier qui a été disponible. Il consiste à parcourir les blocs d'index jusqu'à trouver les pointeurs vers les blocs contenant les données. PostgreSQL lit ensuite les données de la table qui sont pointées par l'index.

tpc=# EXPLAIN SELECT * FROM clients WHERE client_id = 10000;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using clients_pkey on clients  (cost=0.42..8.44 rows=1 width=52)
   Index Cond: (client_id = 10000)
(2 lignes)

Ce type de noeud ne permet pas d'extraire directement les données à retourner depuis l'index, sans passer par la lecture des blocs correspondants de la table. Le noeud Index Only Scan permet cette optimisation, à condition que les colonnes retournées fassent partie de l'index :

tpc=# EXPLAIN SELECT client_id FROM clients WHERE client_id = 10000;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Only Scan using clients_pkey on clients  (cost=0.42..8.44 rows=1 width=8)
   Index Cond: (client_id = 10000)
(2 lignes)

Enfin, on retrouve le dernier parcours sur des opérations de type range scan, c'est-à-dire où PostgreSQL doit retourner une plage de valeurs. On le retrouve également lorsque PostgreSQL doit combiner le résultat de la lecture de plusieurs index.

Contrairement à d'autres SGBD, un index bitmap n'a aucune existence sur disque. Il est créé en mémoire lorsque son utilisation a un intérêt. Le but est de diminuer les déplacements de la tête de lecture en découplant le parcours de l'index du parcours de la table :

• Lecture en un bloc de l'index ;
• Lecture en un bloc de la partie intéressante de la table (dans l'ordre physique de la table, pas dans l'ordre logique de l'index).

tpc=# EXPLAIN SELECT * FROM clients WHERE client_id BETWEEN 10000 AND 12000;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Bitmap Heap Scan on clients  (cost=44.99..1201.32 rows=2007 width=52)
   Recheck Cond: ((client_id >= 10000) AND (client_id <= 12000))
   ->  Bitmap Index Scan on clients_pkey  (cost=0.00..44.49 rows=2007 width=0)
         Index Cond: ((client_id >= 10000) AND (client_id <= 12000))
(4 lignes)

On retrouve aussi des Bitmap Index Scan lorsqu'il s'agit de combiner le résultat de la lecture de plusieurs index :

tpc=# EXPLAIN SELECT * FROM clients WHERE client_id
tpc=# BETWEEN 10000 AND 12000 AND segment_marche = 'AUTOMOBILE';
                                    QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on clients  (cost=478.25..1079.74 rows=251 width=8)
   Recheck Cond: ((client_id >= 10000) AND (client_id <= 12000)
                 AND (segment_marche = 'AUTOMOBILE'::bpchar))
   ->  BitmapAnd  (cost=478.25..478.25 rows=251 width=0)
     ->  Bitmap Index Scan on clients_pkey  (cost=0.00..44.49 rows=2007 width=0)
           Index Cond: ((client_id >= 10000) AND (client_id <= 12000))
     ->  Bitmap Index Scan on idx_clients_segmarche
                                        (cost=0.00..433.38 rows=18795 width=0)
           Index Cond: (segment_marche = 'AUTOMOBILE'::bpchar)
(7 lignes)

À partir de la version 10, une infrastructure a été mise en place pour permettre un parcours parallélisé d'un index. Cela donne donc les noeuds Parallel Index Scan, Parallel Index Only Scan et Parallel Bitmap Heap Scan. Cette infrastructure est actuellement uniquement utilisé pour les index Btree. Par contre, pour le bitmap scan, seul le parcours de la table est parallélisé, ce qui fait que tous les types d'index sont concernés.

Le choix du type de jointure dépend non seulement des données mises en oeuvre, mais elle dépend également beaucoup du paramétrage de PostgreSQL, notamment des paramètres work_mem, seq_page_cost et random_page_cost.

La Nested Loop se retrouve principalement quand on joint de petits ensembles de données :

sql1=# EXPLAIN SELECT * FROM commandes
sql1=# JOIN lignes_commandes USING (numero_commande)
sql1=# WHERE numero_commande < 1000;
                                       QUERY PLAN
------------------------------------------------------------------------------
 Nested Loop  (cost=0.84..4161.14 rows=1121 width=154)
   ->  Index Scan using orders_pkey on commandes
                                            (cost=0.42..29.64 rows=280 width=80)
         Index Cond: (numero_commande < 1000)
   ->  Index Scan using lignes_commandes_pkey on lignes_commandes
                                            (cost=0.42..14.71 rows=5 width=82)
         Index Cond: (numero_commande = commandes.numero_commande)

Le Hash Join se retrouve également lorsque l'ensemble de la table interne est très petit. L'optimiseur réalise alors un hachage des valeurs de la colonne de jointure sur la table externe et réalise ensuite une lecture de la table externe et compare les hachages de la clé de jointure avec le/les hachage(s) obtenus à la lecture de la table interne.

sql1=# EXPLAIN SELECT * FROM commandes
sql1=# JOIN lignes_commandes USING (numero_commande);
                                   QUERY PLAN
-------------------------------------------------------------------------------
 Hash Join  (cost=10690.31..59899.18 rows=667439 width=154)
   Hash Cond: (lignes_commandes.numero_commande = commandes.numero_commande)
   ->  Seq Scan on lignes_commandes  (cost=0.00..16325.39 rows=667439 width=82)
   ->  Hash  (cost=6489.25..6489.25 rows=166725 width=80)
         ->  Seq Scan on commandes  (cost=0.00..6489.25 rows=166725 width=80)

La jointure par tri- fusion, ou Merge Join prend deux ensembles de données triés en entrée et restitue l'ensemble de données après jointure. Cette jointure est assez lourde à initialiser si PostgreSQL ne peut pas utiliser d'index, mais elle a l'avantage de retourner les données triées directement :

sql1=# EXPLAIN SELECT * FROM commandes
sql1=# JOIN lignes_commandes USING (numero_commande)
sql1=# ORDER BY numero_commande DESC;
                                       QUERY PLAN
------------------------------------------------------------------------------
 Merge Join  (cost=1.40..64405.98 rows=667439 width=154)
   Merge Cond: (commandes.numero_commande = lignes_commandes.numero_commande)
   ->  Index Scan Backward using orders_pkey on commandes
                                    (cost=0.42..12898.63 rows=166725 width=80)
   ->  Index Scan Backward using lignes_commandes_pkey on lignes_commandes
                                    (cost=0.42..42747.64 rows=667439 width=82)

Il s'agit d'un algorithme de jointure particulièrement efficace pour traiter les volumes de données importants.

Les clauses EXISTS et NOT EXISTS mettent également en oeuvre des algorithmes dérivés de semi et anti jointures. Par exemple avec la clause EXISTS :

sql1=# EXPLAIN
 SELECT count(*)
   FROM commandes c
  WHERE EXISTS (SELECT 1
                  FROM lignes_commandes l
                 WHERE c.date_commande > l.date_expedition
                   AND c.numero_commande = l.numero_commande);
                                QUERY PLAN
------------------------------------------------------------------------------
 Aggregate  (cost=42439.18..42439.19 rows=1 width=0)
   ->  Hash Semi Join  (cost=27927.38..42321.76 rows=46967 width=0)
         Hash Cond: (c.numero_commande = l.numero_commande)
         Join Filter: (c.date_commande > l.date_expedition)
         ->  Seq Scan on commandes c (cost=0.00..6489.25 rows=166725 width=12)
         ->  Hash  (cost=16325.39..16325.39 rows=667439 width=12)
               ->  Seq Scan on lignes_commandes l
                                     (cost=0.00..16325.39 rows=667439 width=12)

On obtient un plan sensiblement identique, avec NOT EXISTS. Le noeud Hash Semi Join est remplacé par Hash Anti Join :

sql1=# EXPLAIN
SELECT *
  FROM commandes
 WHERE NOT EXISTS (SELECT 1
                     FROM lignes_commandes l
                    WHERE l.numero_commande = commandes.numero_commande);
                                      QUERY PLAN
------------------------------------------------------------------------------
 Hash Anti Join  (cost=27276.38..47110.99 rows=25824 width=80)
   Hash Cond: (commandes.numero_commande = l.numero_commande)
   ->  Seq Scan on commandes  (cost=0.00..6489.25 rows=166725 width=80)
   ->  Hash  (cost=16325.39..16325.39 rows=667439 width=8)
         ->  Seq Scan on lignes_commandes l
                              (cost=0.00..16325.39 rows=667439 width=8)

Pour réaliser un tri, PostgreSQL ne dispose que d'un seul noeud pour réaliser cela : Sort. Son efficacité va dépendre du paramètre work_mem qui va définir la quantité de mémoire que PostgreSQL pourra utiliser pour un tri.

sql1=# explain (ANALYZE) SELECT * FROM lignes_commandes
sql1=# WHERE numero_commande = 1000 ORDER BY quantite;
                                    QUERY PLAN
------------------------------------------------------------------------------
 Sort  (cost=15.57..15.58 rows=5 width=82)
       (actual time=0.096..0.097 rows=4 loops=1)
   Sort Key: quantite
   Sort Method: quicksort  Memory: 25kB
   ->  Index Scan using lignes_commandes_pkey on lignes_commande
                    (cost=0.42..15.51 rows=5 width=82)
                    (actual time=0.017..0.021 rows=4 loops=1)
         Index Cond: (numero_commande = 1000)

Si le tri ne tient pas en mémoire, l'algorithme de tri gère automatiquement le débordement sur disque :

sql1=# EXPLAIN (ANALYZE) SELECT * FROM commandes ORDER BY prix_total ;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Sort  (cost=28359.74..28776.55 rows=166725 width=80)
       (actual time=993.441..1157.935 rows=166725 loops=1)
   Sort Key: prix_total
   Sort Method: external merge  Disk: 15608kB
   ->  Seq Scan on commandes  (cost=0.00..6489.25 rows=166725 width=80)
                              (actual time=173.615..236.712 rows=166725 loops=1)

Cependant, si un index existe, PostgreSQL peut également utiliser un index pour récupérer les données triées directement :

sql1=# EXPLAIN SELECT * FROM commandes ORDER BY date_commande;
                                      QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using idx_commandes_date_commande on commandes
                            (cost=0.42..23628.15 rows=166725 width=80)

Dans n'importe quel ordre de tri :

sql1=# EXPLAIN SELECT * FROM commandes ORDER BY date_commande DESC;
                                        QUERY PLAN
------------------------------------------------------------------------------
 Index Scan Backward using idx_commandes_date_commande on commandes
                                    (cost=0.42..23628.15 rows=166725 width=80)

Le choix du type d'opération de regroupement dépend non seulement des données mises en oeuvres, mais elle dépend également beaucoup du paramétrage de PostgreSQL, notamment du paramètre work_mem.

Concernant les opérations d'agrégations, on retrouve un noeud de type Aggregate lorsque la requête réalise une opération d'agrégation simple, sans regroupement :

sql1=# EXPLAIN SELECT count(*) FROM commandes;
                                 QUERY PLAN
-------------------------------------------------------------------------------
 Aggregate  (cost=4758.11..4758.12 rows=1 width=0)
   ->  Index Only Scan using commandes_client_id_idx on commandes
                                    (cost=0.42..4341.30 rows=166725 width=0)

Si l'optimiseur estime que l'opération d'agrégation tient en mémoire (paramètre work_mem), il va utiliser un noeud de type HashAggregate :

sql1=# EXPLAIN SELECT code_pays, count(*) FROM contacts GROUP BY code_pays;
                              QUERY PLAN
----------------------------------------------------------------------
 HashAggregate  (cost=3982.02..3982.27 rows=25 width=3)
   ->  Seq Scan on contacts  (cost=0.00..3182.01 rows=160001 width=3)

L'inconvénient de ce noeud est que sa consommation mémoire n'est pas limitée par work_mem, il continuera malgré tout à allouer de la mémoire. Dans certains cas, heureusement très rares, l'optimiseur peut se tromper suffisamment pour qu'un noeud HashAggregate consomme plusieurs giga-octets de mémoire et ne sature la mémoire du serveur.

Lorsque l'optimiseur estime que le volume de données à traiter ne tient pas dans work_mem, il utilise plutôt l'algorithme GroupAggregate :

sql1=# explain select numero_commande, count(*)
sql1=# FROM lignes_commandes group by numero_commande;
                                    QUERY PLAN
-------------------------------------------------------------------------------
 GroupAggregate  (cost=0.42..47493.84 rows=140901 width=8)
   ->  Index Only Scan using lignes_commandes_pkey on lignes_commandes
                                    (cost=0.42..42747.64 rows=667439 width=8)

Le calcul d' un agrégat peut être parallélisé à partir de la version 9.6. Dans ce cas, deux noeuds sont utilisés : un pour le calcul partiel de chaque processus (Partial Aggregate), et un pour le calcul final (Finalize Aggregate). Voici un exemple de plan :

SELECT count(*), min(C1), max(C1) FROM t1;

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Finalize Aggregate (actual time=1766.820..1766.820 rows=1 loops=1)
   ->  Gather (actual time=1766.767..1766.799 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate (actual time=1765.236..1765.236 rows=1 loops=3)
               ->  Parallel Seq Scan on t1
                            (actual time=0.021..862.430 rows=6666667 loops=3)
 Planning time: 0.072 ms
 Execution time: 1769.164 ms
(8 rows)

On rencontre le noeud Limit lorsqu'on limite le résultat avec l'ordre LIMIT :

sql1=# EXPLAIN SELECT * FROM commandes LIMIT 1;
                               QUERY PLAN
------------------------------------------------------------------------
 Limit  (cost=0.00..0.04 rows=1 width=80)
   ->  Seq Scan on commandes  (cost=0.00..6489.25 rows=166725 width=80)

À noter, que le noeud Sort utilisera une méthode de tri appelée top-N heapsort qui permet d'optimiser le tri pour retourner les n premières lignes :

sql1=# EXPLAIN ANALYZE SELECT * FROM commandes ORDER BY prix_total LIMIT 5;
                                        QUERY PLAN
-------------------------------------------------------------------------------
 Limit  (cost=9258.49..9258.50 rows=5 width=80)
        (actual time=86.332..86.333 rows=5 loops=1)
   ->  Sort  (cost=9258.49..9675.30 rows=166725 width=80)
             (actual time=86.330..86.331 rows=5 loops=1)
         Sort Key: prix_total
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on commandes  (cost=0.00..6489.25 rows=166725 width=80)
                                (actual time=3.683..22.687 rows=166725 loops=1)

On retrouve le noeud Unique lorsque l'on utilise DISTINCT pour dédoublonner le résultat d'une requête :

sql1=# EXPLAIN SELECT DISTINCT numero_commande FROM lignes_commandes;
                                   QUERY PLAN
-------------------------------------------------------------------------------
 Unique  (cost=0.42..44416.23 rows=140901 width=8)
   ->  Index Only Scan using lignes_commandes_pkey on lignes_commandes
                                    (cost=0.42..42747.64 rows=667439 width=8)

À noter qu'il est souvent plus efficace d'utiliser GROUP BY pour dédoublonner les résultats d'une requête :

sql1=# EXPLAIN (ANALYZE) SELECT DISTINCT numero_commande
sql1=# FROM lignes_commandes GROUP BY numero_commande;
                                      QUERY PLAN
-------------------------------------------------------------------------------
 Unique  (cost=0.42..44768.49 rows=140901 width=8)
         (actual time=0.047..357.745 rows=166724 loops=1)
   ->  Group  (cost=0.42..44416.23 rows=140901 width=8)
              (actual time=0.045..306.550 rows=166724 loops=1)
         ->  Index Only Scan using lignes_commandes_pkey on lignes_commandes
                    (cost=0.42..42747.64 rows=667439 width=8)
                    (actual time=0.040..197.817 rows=667439 loops=1)
               Heap Fetches: 667439
 Total runtime: 365.315 ms

sql1=# EXPLAIN (ANALYZE) SELECT numero_commande
sql1=# FROM lignes_commandes GROUP BY numero_commande;
                                    QUERY PLAN
-------------------------------------------------------------------------------
 Group  (cost=0.42..44416.23 rows=140901 width=8)
        (actual time=0.053..302.875 rows=166724 loops=1)
   ->  Index Only Scan using lignes_commandes_pkey on lignes_commandes
                (cost=0.42..42747.64 rows=667439 width=8)
                (actual time=0.046..194.495 rows=667439 loops=1)
         Heap Fetches: 667439
 Total runtime: 310.506 ms

Le gain est infime, 50 millisecondes environ sur cette requête, mais laisse présager des gains sur une volumétrie plus importante.

Les noeuds Append, Except et Intersect se rencontrent lorsqu'on utilise les opérateurs ensemblistes UNION, EXCEPT et INTERSECT. Par exemple, avec UNION ALL :

sql1=# EXPLAIN
SELECT * FROM pays
 WHERE region_id = 1
 UNION ALL
SELECT * FROM pays
 WHERE region_id = 2;
                            QUERY PLAN
------------------------------------------------------------------
 Append  (cost=0.00..2.73 rows=10 width=53)
   ->  Seq Scan on pays  (cost=0.00..1.31 rows=5 width=53)
         Filter: (region_id = 1)
   ->  Seq Scan on pays pays_1  (cost=0.00..1.31 rows=5 width=53)
         Filter: (region_id = 2)

Le noeud Gather a été introduit en version 9.6 et est utilisé comme noeud de rassemblement des données pour les plans parallélisés.

Le noeud InitPlan apparaît lorsque PostgreSQL a besoin d'exécuter une première sous-requête pour ensuite exécuter le reste de la requête. Il est assez rare :

sql1=# EXPLAIN SELECT *,
sql1=# (SELECT nom_region FROM regions WHERE region_id=1)
sql1=# FROM pays WHERE region_id = 1;
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on pays  (cost=1.06..2.38 rows=5 width=53)
   Filter: (region_id = 1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on regions  (cost=0.00..1.06 rows=1 width=26)
           Filter: (region_id = 1)

Le noeud SubPlan est utilisé lorsque PostgreSQL a besoin d'exécuter une sous-requête pour filtrer les données :

sql1=# EXPLAIN
SELECT * FROM pays
 WHERE region_id NOT IN (SELECT region_id FROM regions
                          WHERE nom_region = 'Europe');
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on pays  (cost=1.06..2.38 rows=12 width=53)
   Filter: (NOT (hashed SubPlan 1))
   SubPlan 1
     ->  Seq Scan on regions  (cost=0.00..1.06 rows=1 width=4)
           Filter: (nom_region = 'Europe'::bpchar)

D'autres types de noeud peuvent également être trouvés dans les plans d'exécution. L'annexe décrit tous ces noeuds en détail.

L'optimiseur de PostgreSQL est sans doute la partie la plus complexe de PostgreSQL. Il se trompe rarement, mais certains facteurs peuvent entraîner des temps d'exécution très lents, voire catastrophiques de certaines requêtes.

PostgreSQL conserve des statistiques par colonne simple. Dans l'exemple ci- dessus, le planificateur sait que l'estimation pour c1=1 est de 20% et que l'estimation pour c2=1 est de 10%. Par contre, il n'a aucune idée de l'estimation pour c1=1 AND c2=1. En réalité, l'estimation pour cette formule va de 0 à 10% mais le planificateur doit statuer sur une seule valeur. Ce sera le résultat de la multiplication des deux estimations, soit 2% (20% * 10%).

La version 10 de PostgreSQL corrige cela en ajoutant la possibilité d'ajouter des statistiques sur plusieurs colonnes spécifiques. Ce n'est pas automatique, il faut créer un objet statistique avec l'ordre CREATE STATISTICS.

Dans un prédicat, lorsque les valeurs des colonnes sont transformées par un calcul ou par une fonction, l'optimiseur n'a aucun moyen pour connaître la sélectivité d'un prédicat. Il utilise donc une estimation codée en dur dans le code de l'optimiseur : 0,5% du nombre de lignes de la table.

Dans la requête suivante, l'optimiseur estime que la requête va ramener 834 lignes :

sql1=# EXPLAIN SELECT * FROM commandes
sql1=# WHERE extract('year' from date_commande) = 2014;
                                          QUERY PLAN
-------------------------------------------------------------------------------
 Seq Scan on commandes  (cost=0.00..7739.69 rows=834 width=80)
   Filter:
    (date_part('year'::text, (date_commande)::timestamp without time zone) =
        2014::double precision)
(2 lignes)

Ces 834 lignes correspondent à 0,5% de la table commandes :

sql1=# SELECT relname, reltuples, round(reltuples*0.005) AS estimé
  FROM pg_class
 WHERE relname = 'commandes';
  relname  | reltuples | estimé
-----------+-----------+--------
 commandes |    166725 |    834
(1 ligne)

Dans le cas d'une recherche avec préfixe, PostgreSQL peut utiliser un index sur la colonne. Il existe cependant une spécificité à PostgreSQL. Si l'encodage est autre chose que C, il faut utiliser une classe d'opérateur lors de la création de l'index. Cela donnera par exemple :

CREATE INDEX i1 ON t1 (c2 varchar_pattern_ops);

De plus, à partir de la version 9.1, il est important de faire attention au collationnement. Si le collationnement de la requête diffère du collationnement de la colonne de l'index, l'index ne pourra pas être utilisé.

Le contexte :

CREATE TABLE t1 (
    c1 integer PRIMARY KEY
);
INSERT INTO t1 SELECT generate_series(1, 1000);

CREATE TABLE t2 (
    c2 integer
);
INSERT INTO t2 SELECT generate_series(1, 1000);

ANALYZE;

Voici un problème survenant dans les versions antérieures à la 9.2.

EXPLAIN SELECT * FROM t1
WHERE c1 IN (SELECT c2 FROM t2 LIMIT 199);
                               QUERY PLAN
------------------------------------------------------------------------
 Hash Semi Join (cost=7.46..27.30 rows=199 width=4)
   Hash Cond: (t1.c1 = t2.c2)
   -> Seq Scan on t1 (cost=0.00..15.00 rows=1000 width=4)
   -> Hash (cost=4.97..4.97 rows=199 width=4)
         -> Limit (cost=0.00..2.98 rows=199 width=4)
               -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=4)
(6 rows)

Tout se passe bien. PostgreSQL fait tout d'abord un parcours séquentiel sur la table t2 et ne récupère que les 199 premières lignes grâce à la clause LIMIT. Le hachage se fait sur les 199 lignes et une comparaison est faite pour chaque ligne de t1.

Maintenant, cherchons à récupérer une ligne de plus avec un LIMIT à 200 :

EXPLAIN SELECT * FROM t1
WHERE c1 IN (SELECT c2 FROM t2 LIMIT 200);
                                  QUERY PLAN
----------------------------------------------------------------------
 Hash Join (cost=10.00..30.75 rows=500 width=4)
   Hash Cond: (t1.c1 = t2.c2)
   -> Seq Scan on t1 (cost=0.00..15.00 rows=1000 width=4)
   -> Hash (cost=7.50..7.50 rows=200 width=4)
         -> HashAggregate (cost=5.50..7.50 rows=200 width=4)
               -> Limit (cost=0.00..3.00 rows=200 width=4)
                     -> Seq Scan on t2 (cost=0.00..15.00 rows=1000
                                        width=4)
(7 rows)

La requête a légèrement changé : on passe d'un LIMIT 199 à un LIMIT 200. L'estimation explose, elle passe de 199 lignes (estimation exacte) à 500 lignes (estimation plus que doublée). En fait, le nombre de lignes est calculé très simplement : nombre de lignes de la table t1 multiplié par 0,5. C'est codé en dur. La raison, jusqu'à PostgreSQL 9.1, est que par défaut une table sans statistiques est estimée posséder 200 valeurs distinctes. Quand l'optimiseur rencontre donc 200 enregistrements distincts en estimation, il pense que la fonction d'estimation de valeurs distinctes n'a pas de statistiques et lui a retourné une valeur par défaut, et applique donc un algorithme de sélectivité par défaut, au lieu de l'algorithme plus fin utilisé en temps normal.

Sur cet exemple, cela n'a pas un gros impact vu la quantité de données impliquées et le schéma choisi. Par contre, ça fait passer une requête de 9ms à 527ms si le LIMIT 199 est passé à un LIMIT 200 pour la même requête sur une table plus conséquente.

Ce problème est réglé en version 9.2 :

EXPLAIN SELECT * FROM t1
WHERE c1 IN (SELECT c2 FROM t2 LIMIT 200);
                               QUERY PLAN
------------------------------------------------------------------------
 Hash Semi Join (cost=7.46..27.30 rows=200 width=4)
   Hash Cond: (t1.c1 = t2.c2)
   -> Seq Scan on t1 (cost=0.00..15.00 rows=1000 width=4)
   -> Hash (cost=4.97..4.97 rows=200 width=4)
         -> Limit (cost=0.00..2.98 rows=200 width=4)
               -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=4)
(6 rows)

Parfois, un DELETE peut prendre beaucoup de temps à s'exécuter. Cela peut être dû à un grand nombre de lignes à supprimer. Cela peut aussi être dû à la vérification des contraintes étrangères.

Dans l'exemple ci-dessus, le DELETE met 38 minutes à s'exécuter (2312835 ms), pour ne supprimer aucune ligne. En fait, c'est la vérification de la contrainte fk_nonbatia21descrsuf_lota30descrlot qui prend pratiquement tout le temps. C'est d'ailleurs pour cette raison qu'il est recommandé de positionner des index sur les clés étrangères, car cet index permet d'accélérer la recherche liée à la contrainte.

Attention donc aux contraintes de clés étrangères pour les instructions DML.

L'exemple ci-dessous montre une requête qui récupère les commandes qui ont des lignes de commandes et réalise le dédoublonnage avec DISTINCT. Le plan d'exécution montre une opération de tri qui a nécessité un fichier temporaire de 60Mo. Toutes ces opérations sont assez gourmandes, la requête répond en 5,9s :

tpc=# EXPLAIN (ANALYZE on, COSTS off)
tpc-# SELECT DISTINCT commandes.* FROM commandes
sql1=# JOIN lignes_commandes USING (numero_commande);
                                   QUERY PLAN
-------------------------------------------------------------------------------
 Unique (actual time=5146.904..5833.600 rows=168749 loops=1)
   ->  Sort (actual time=5146.902..5307.633 rows=675543 loops=1)
         Sort Key: commandes.numero_commande, commandes.client_id,
                   commandes.etat_commande, commandes.prix_total,
                   commandes.date_commande, commandes.priorite_commande,
                   commandes.vendeur, commandes.priorite_expedition,
                   commandes.commentaire
         Sort Method: external sort  Disk: 60760kB
         ->  Merge Join (actual time=0.061..601.674 rows=675543 loops=1)
               Merge Cond: (commandes.numero_commande =
                            lignes_commandes.numero_commande)
               ->  Index Scan using orders_pkey on commandes
                        (actual time=0.026..71.544 rows=168750 loops=1)
               ->  Index Only Scan using lignes_com_pkey on lignes_commandes
                        (actual time=0.025..175.321 rows=675543 loops=1)
                     Heap Fetches: 0
 Total runtime: 5849.996 ms