Vous n'êtes pas identifié(e).
Bonjour
suite à des problèmes qu'on a eu avec des requêtes qui ne se terminaient jamais, on a positionné random_page_cost à 1.2, en suivant des précos généraliste sur les disques SSD.
Je tombe maintenant sur un effe de bord qui est le cas inverse :
mdm=> set random_page_cost =3;
SET
mdm=> explain SELECT
count (1)
FROM
gd_coordonnee gc
WHERE
NOT EXISTS (
SELECT
(1)
FROM
gd_associe_interlo_coord_red gaicr
WHERE
gaicr.f_coordonnee = gc.coordonnee_id );
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=7594528.62..7594528.63 rows=1 width=8)
-> Gather (cost=7594528.41..7594528.62 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=7593528.41..7593528.42 rows=1 width=8)
-> Parallel Hash Anti Join (cost=6542818.61..7572026.27 rows=8600856 width=0)
Hash Cond: (gc.coordonnee_id = gaicr.f_coordonnee)
-> Parallel Index Only Scan using pkgd_coordonnee on gd_coordonnee gc (cost=0.44..667580.93 rows=8662897 width=6)
-> Parallel Hash (cost=5855503.30..5855503.30 rows=41893430 width=6)
-> Parallel Seq Scan on gd_associe_interlo_coord_red gaicr (cost=0.00..5855503.30 rows=41893430 width=6)
(9 lignes)
mdm=> set random_page_cost = 1.2;
SET
mdm=> explain SELECT
count (1)
FROM
gd_coordonnee gc
WHERE
NOT EXISTS (
SELECT
(1)
FROM
gd_associe_interlo_coord_red gaicr
WHERE
gaicr.f_coordonnee = gc.coordonnee_id );
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=6804572.15..6804572.16 rows=1 width=8)
-> Gather (cost=6804571.93..6804572.14 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=6803571.93..6803571.94 rows=1 width=8)
-> Nested Loop Anti Join (cost=1.00..6782069.79 rows=8600856 width=0)
-> Parallel Index Only Scan using pkgd_coordonnee on gd_coordonnee gc (cost=0.44..382229.04 rows=8662897 width=6)
-> Index Only Scan using fkgd_associe_interlocuteur_re3 on gd_associe_interlo_coord_red gaicr (cost=0.57..115.26 rows=675 width=6)
Index Cond: (f_coordonnee = gc.coordonnee_id)avec random page cost à 1.2 la requête n'arrive n'est pas terminée après presque une heure, avec 3 ça se termine en quelque minutes.
L'index utilisé dans le nested loop qui, me semble, pose problème est en cache à >99% mais la colonne sur la quelle il se base est faiblement corrélée : -0.02 ...
Quel est le mécanisme ici? Pourquoi le planner ne tient pas compte de la correlation faible et fait un nested loop ?
Ou je suis à coté ?
Merci d'avance
Hors ligne
Alors je ne vais pas vraiment (pas du tout) vous aider.
Mais quand j'étais chez Bull, en 2018 j'avais benché les serveurs x86 Bull avec SSD Nvme, et le ratio random/sequential était à 1,3 et non pas 1,2.
(et à vrai dire, j'étais surpris, je pensais que le ratio serait très proche de 1).
Hors ligne
Merci Hervé, on a été collègues alors 
Je pense que 1.2 ou 1.3 ça ne change pas grand chose, ce qui m'étonne est que le planner ne prenne pas en compte le fait que l'index n'est pas très performant... si c'est bien ça ...
Hors ligne
Merci Hervé, on a été collègues alors
Je pense que 1.2 ou 1.3 ça ne change pas grand chose, ce qui m'étonne est que le planner ne prenne pas en compte le fait que l'index n'est pas très performant... si c'est bien ça ...
Que l'index soit performant ou pas, si les données figurent dans l'index, il est plus rapide de scanner l'index plutôt que la table...
Le truc bête, après un vacuum analyse des tables, le comportement ne change pas ?
Hors ligne
Oui les stats sont à jour.
Ma compréhension est que les accès seq_scan et index_scan se traduisent dans des accès respectivement séquentiels et random. Du coup en baissant random_page_cost fatalement le planner décide d'utiliser plus souvent le indexes par rapport à un seq_scan, et ça a été résolutif pour un certain nombre de requêtes qui étaient lentissimes.
Pour cette requête et potentiellement d'autre il semble que random_page_cost = 1.2 soit trop agressif et que ça force le planner à utiliser l'indexe, avec des accès random, à tort ...
Bref, c'est de la magie noire presque ... je fais des tests supplémentaires avec des valeurs intermédiaires random_page_cost = 2.0 par exemple
Hors ligne
Pour cette requête et potentiellement d'autre il semble que random_page_cost = 1.2 soit trop agressif et que ça force le planner à utiliser l'indexe, avec des accès random, à tort ...
Elle a beaucoup de colonnes ta table gaicr ?
Parce que normalement, le index-only scan est plus performant que le Seq Scan...
Hors ligne
Oui les stats sont à jour.
Il n'y a pas que les stats à vérifier, il y a aussi l'état de la visibility-map.
Pour cette requête et potentiellement d'autre il semble que random_page_cost = 1.2 soit trop agressif et que ça force le planner à utiliser l'indexe, avec des accès random, à tort ...
Elle a beaucoup de colonnes ta table gaicr ?
Parce que normalement, le index-only scan est plus performant que le Seq Scan...
Hors ligne
Il faudrait un EXPLAIN (ANALYZE, BUFFERS) de la requete pour chacun des plans idealement (sinon seulement pour la version qui effectue un hash anti join) pour comprendre l'origine du probleme. La cause la plus probable est aue le parcours de gd_coordonnee retourne bien plus de lignes que prevu, ce qui fait exploser le temps d'execution de la requete avec un nested loop de maniere proportionnel.
Julien.
https://rjuju.github.io/
Hors ligne