PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 30/05/2024 12:45:49

geekone
Membre

Plan d'execution sub-optimal suite à modification de random_page_cost

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

#2 30/05/2024 18:39:59

Re : Plan d'execution sub-optimal suite à modification de random_page_cost

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

#3 30/05/2024 20:03:56

geekone
Membre

Re : Plan d'execution sub-optimal suite à modification de random_page_cost

Merci Hervé, on a été collègues alors smile

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

#4 31/05/2024 07:21:31

Re : Plan d'execution sub-optimal suite à modification de random_page_cost

geekone a écrit :

Merci Hervé, on a été collègues alors smile

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

#5 31/05/2024 10:42:49

geekone
Membre

Re : Plan d'execution sub-optimal suite à modification de random_page_cost

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

#6 31/05/2024 12:37:53

Re : Plan d'execution sub-optimal suite à modification de random_page_cost

geekone a écrit :

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

#7 31/05/2024 12:39:01

Re : Plan d'execution sub-optimal suite à modification de random_page_cost

geekone a écrit :

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.


geekone a écrit :

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

#8 01/06/2024 05:53:35

rjuju
Administrateur

Re : Plan d'execution sub-optimal suite à modification de random_page_cost

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.

Hors ligne

Pied de page des forums