Optimiseur / réécriture des requêtes et EXPLAIN

[scheu]

Bonjour

J'utilise Postgresql depuis peu. J'ai constaté sur ma base récemment installée un problème : l'optimiseur ne sait apparemment pas, quand c'est possible, simplifier une requête et la réécrire avant de l'exécuter. Exemple simple et reproductible :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
ma_bdd=# create table test (n numeric);
CREATE
ma_bdd=# insert into test values (1); --> lancé 10 fois
INSERT
ma_bdd=# insert into test values (0); --> lancé 10 fois
INSERT
ma_bdd=# select count(*) from test;
 count
-------
    20
(1 row)
ma_bdd=# vacuum full analyze test; 
VACUUM
ma_bdd=# explain select * from test where n = 1;
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on test  (cost=0.00..1.25 rows=10 width=9)
   Filter: (n = 1::numeric)
(2 rows)

ma_bdd=# explain select * from test where n = 1 and n = 1;
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on test  (cost=0.00..1.30 rows=5 width=9)
   Filter: ((n = 1::numeric) AND (n = 1::numeric))
(2 rows)

En mettant "where n=1", l'optimiseur m'estime bien à 10 le nombre de lignes renvoyées, alors qu'en mettant "where n=1 and n=1", il m'estime à 5 (au lieu de 10 !), il a donc sous-estimé le nb de lignes renvoyées
L'optimiseur sait-il simplifier et réécrire une requête avant de l'exécuter ? Des paramétrages influent-ils au niveau du fichier postgresql.conf ou ailleurs ?
Ce problème m'embête grandement car à grande échelle des requêtes générées via BO peuvent contenir plusieurs fois la même condition (where n=1 and n=1 par exemple), et le fait que l'optimiseur sous-estime le nombre de lignes renvoyées me donne de mauvais plans d'exécutions lors de jointures complexes (utilisation de nested loop au lieu de hash join par exemple)

Quelqu'un aurait-il des explications sur ce comportement, et éventuellement une solution ?

[GrandFather]

Bonjour,

ce nombre de lignes est effectivement assez sommairement estimé... Maintenant, il n'est pas certain que cela soit à l'origine de tes problèmes de performances. Il y a plusieurs choses à prendre en considération :

• Malgré cette erreur dans le nombre de lignes renvoyés, le coût estimé n'est passé que de 1,25 à 1,30 ; ces 5% de coût supplémentaire ne sont dûs qu'à l'évaluation du deuxième membre de la clause WHERE, le nombre de lignes estimé n'entrant pas en ligne de compte (il faudra toujours un parcours séquentiel de tous les enregistrements de la table)
• L'optimisation n'étant pas linéaire, il n'est généralement pas opportun d'extrapoler le comportement d'une requête complexe en environnement réel à partir de celui d'une requête simple en "labo"
• Le planificateur se repose sur les statistiques établies lors du dernier VACUUM ANALYZE pour établir son plan ; l'échantillonnage utilisé pour les stats étant défini aléatoirement, il est possible qu'un nouveau VACUUM ANALYZE suivi d'autres EXPLAIN t'affiche un nombre de lignes différent de celui-ci.

Pour toutes ces raisons, j'aurai tendance à penser que ces problèmes d'optimisation de tes requêtes n'ont pas de rapport direct avec ce décompte approximatif. Maintenant, c'est un domaine complexe que je ne maîtrise pas encore complètement, donc je n'y mettrais pas ma main à couper.

[scheu]

Merci pour ta réponse
En fait j'ai voulu illustrer l'exmple simplement mais j'ai bel et bien rencontré ce problème dans une requête qui fait une jointure entre une table T1 de 11 millions et une table T2 de 5000 lignes.
Quand sur ma table T2 je mets une série de filtres dans la requête, dont certaines conditions sont identiques et répétées (ex : where col1=valeur1 and col2=valeur2 and col2=valeur2), l'optimiseur m'estime à 1 le nb de lignes renvoyées (alors qu'en réalité il y en a 160 et il estime bien à 160 lorsque je ne répète pas 2 fois les mêmes conditions), donc pour le plan d'exécution il fait un nested loop entre T1 (11 millions de lignes) et T2 filtré (forcément il croit récupérer 1 seule ligne au lieu de 160, donc à cause de ça il me parcourt 160 la table T1 ...) alors qu'en réalité si l'optimiseur avait bien estimé à 160 le nb de lignes renvoyées par "T2 filtré", il aurait fait un hash join (c'est ce qu'il fait quand je supprime les conditions répétées dans le filtrage de T2)
Apparemment pour choisir "nested loop" au lieu de "hash join", il se baserait sur le nb estimé de lignes renvoyées et non sur le coût, mais je n'en suis pas certain

Je m'en vais poster sur le site officiel pour essayer d'avoir des explications à ce comportement lugubre

Merci quand-même

[GrandFather]

Il semblerait effectivement que dans ce cas le planificateur se base sur des stats erronées pour établir son plan... Tu peux visualiser ces statistiques en consultant la vue pg_stats, et vérifier les postulats sur lesquels le planificateur se base.

Si dans cette vue il apparaît que la liste des valeurs les plus couramment utilisées pour le champ concerné est trop imprécise, tu peux influer sur le nombre d'éléments dans cette liste et dans celle des valeurs "pivots" (valeurs divisant en portions égales les valeurs rencontrées dans la colonne) en modifiant les paramètres most_common_vals et histogram_bounds.

[scheu]

Pour la curiosité :

Code :

1
2
3
4
5
ma_bdd=# select * from pg_stats where tablename = 'test';
 schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
------------+-----------+---------+-----------+-----------+------------+------------------+-------------------+------------------+-------------
 public     | test      | n       |         0 |         9 |          2 | {0,1}            | {0.5,0.5}         |                  |   -0.503759
(1 row)

Ca a l'air normal. Je soupçonne que ce soit l'optimiseur Postgresql qui soit incapable de simplifier et de réécrire les requêtes avant de les exécuter.
En fait je viens de migrer depuis Oracle, où là, l'optimiseur le faisait lui, c'est quand-même une mauvaise surprise ...

Pour ma curiosité : la colonne histogram_bounds est vide, sais-tu comment peut-on créer un histogramme sur la colonne ?

[GrandFather]

Envoyé par scheu

Pour ma curiosité : la colonne histogram_bounds est vide, sais-tu comment peut-on créer un histogramme sur la colonne ?

Il est normal que la colonne histogram_bounds soit vide, puisque la colonne most_common_vals contient toutes les valeurs représentées dans la table (les contenus de ces deux colonnes s'excluent mutuellement). Avec ALTER TABLE SET STATISTICS tu peux influer sur le nombre d'entrées dans cette colonne.

Que donnent les stats sur ta table en exploitation ? Sont-ils conformes eux ?