Forums PostgreSQL.fr

MichaelT

Membre

Requete réticente à optim

Bonjour à toutes et tous,

Ayant pourtant quelques années de vol sur différent RDBMS, je me retrouve collé depuis 2 jours par un update sur Postgresql.
Votre aide, ou suggestions, please, seront les bienvenues !

Query :
update navigation
set relance = current_date
from navigation a
inner join relances b
on a.id_client = b.id_client and lower(a.famille) = b.famille

Explications :
Je veux updater la table Navigation (champs relance) afin d'y inscrire la current date, pour tous ses enregistrements qui se retrouvent dans la table relances. La jointure se faisant sur un id_client (integer) et une famille (varchar). Pour le lower, pas beau, j'y suis néanmoins obligé ...
Mes deux tables sont triées par l'id_client et la famille.

Volumétries :
navigation : 1 174 000 enr
relance : 22 000 enr

Contexte :
Essayé avec synchronous_commit off.
Essayé avec nested_loop off.
Serveur 17 go ram, machine dédiée.
pg_conf qui tient la route je pense (personnalisé pour utiliser au max les ressources dédiées de la machine) car jamais eu de pb de ce genre en 1 an d'exec. Mais je peux le transmettre ...

Explain plan :
Nested Loop  (cost=58286.14..58171735.78 rows=1935733862 width=89)
  ->  Seq Scan on animation  (cost=0.00..30082.78 rows=1170678 width=89)
  ->  Materialize  (cost=58286.14..58302.68 rows=1654 width=0)
        ->  Merge Join  (cost=0.07..58284.48 rows=1654 width=0)
              Merge Cond: (b.id_client = a.id_client)
              Join Filter: (lower((a.famille)::text) = b.famille)
              ->  Index Scan using "IDX_RELANCE_ID_copy" on relances b  (cost=0.00..672.57 rows=20288 width=22)
              ->  Index Scan using "IDX_ID_copy" on animation a  (cost=0.00..48979.55 rows=1170678 width=22)
Time: 0.141s

Comme vous le voyez, un gros nested loop vient m'exploser les temps.
Je suis bloqué car les temps d'exécution sont infinis.

Je suis preneur d'un conseil, d'une astuce ou d'une fessée si je la mérite.

Merci

MT

Marc Cousin

Membre

Re : Requete réticente à optim

En fait, n'ayant pas les temps, difficile de voir que c'est le nested loop qui explose les temps. On peut avoir la même avec explain analyze au lieu d'explain simple ?

Et tant qu'on y est un \d des deux tables.

---

Marc.

MichaelT

Membre

Re : Requete réticente à optim

yes avec plaisir.
Merci

L'explain analyse complet est lui aussi sans fin ... donc impossible de l'avoir.
Je me suis focalisé sur le nested loop sans forcément avoir tous les métriques, j'en conviens ...

Voici les ddl des deux tables impliquées (hors indexes).

Table principale (plus forte volumétrie)

CREATE TABLE animation
(
  id_client bigint,
  id_produit character varying(255) DEFAULT NULL::character varying,
  famille character varying(255) DEFAULT NULL::character varying,
  date_vue timestamp(6) without time zone DEFAULT NULL::timestamp without time zone,
  relance timestamp(6) without time zone DEFAULT NULL::timestamp without time zone,
  animation_produit character varying(255) DEFAULT NULL::character varying,
  nb_commande integer,
  nb_produit_vu integer,
  revenue double precision,
  prix_achat_ht double precision
)
WITH (
  OIDS=FALSE
)

Table générée par un soft d'analyse (20000 rows).

CREATE TABLE relances
(
  id_client bigint,
  famille text
)
WITH (
  OIDS=FALSE
)

Marc Cousin

Membre

Re : Requete réticente à optim

Ok. Alors, je parie plutôt pour l'erreur de sélectivité ici :
        ->  Merge Join  (cost=0.07..58284.48 rows=1654 width=0)
              Merge Cond: (b.id_client = a.id_client)
              Join Filter: (lower((a.famille)::text) = b.famille)
              ->  Index Scan using "IDX_RELANCE_ID_copy" on relances b  (cost=0.00..672.57 rows=20288 width=22)
              ->  Index Scan using "IDX_ID_copy" on animation a  (cost=0.00..48979.55 rows=1170678 width=22)

À mon avis ça ramène largement plus de 1654 enregistrements, et donc le nested loop est dans ce cas un très mauvais choix.

Ça ne change pas grand chose, d'un autre côté.

Bon, quitte à tester, autant lui donner le moyen de faire correctement sa jointure :

=> create index testidx on navigation (id_client,lower(famille)), puis réessayer. Mais ça risque de prendre un petit moment.

Autre point: c'est quoi le plan avec enable_nestloop à off ?

Au passage, un truc à vérifier avant de s'engager dans une réflexion trop lourde : on est sûr que c'est pas en attente de verrou que se retrouve la session qui fait l'update, elle est bien active pendant tout ce temps ? Parce qu'avec cet update géant on verrouille tous les enregistrements de la table…

---

Marc.

MichaelT

Membre

Re : Requete réticente à optim

Merci

Je modifie l'index, en effet, et je teste.

Par contre, aie, l'attente de verrou ... pourquoi pas !
Je suis seul sur la base pour cette maintenance, ça m'étonnerai, mais je vais vérifier.

Ps : concernant le enable_nestloop = on ou off, c'est l'option qui permet d'activer ou pas l'utilisation des nested loops par l'optimiseur.
Très mauvaise idée en fait ...

T

Marc Cousin

Membre

Re : Requete réticente à optim

pour le nested_loop, c'est une mauvaise idée de l'utiliser comme solution. C'est par contre un outil de diagnostic intéressant. J'aimerais donc savoir ce qu'il choisit en le désactivant, et si la requête se termine dans un temps raisonnable.

---

Marc.

MichaelT

Membre

Re : Requete réticente à optim

Testé.
Toujours un temps indéfini, très long, sans en voir la fin.
La poisse.
Etonnant tout de même.

Concernant le enable_nestloop = off, dans mon cas, cela a fortement dégradé l'explain plan. Le cost a fait un x1000.

Merci pour votre aide.
Je pense que je vais devoir changer d'approche globale afin d'éviter cette query ...

M

Marc Cousin

Membre

Re : Requete réticente à optim

testé quoi ? la création d'index ? Avez vous refait un analyze ensuite ? Et quel est le plan ?

---

Marc.

MichaelT

Membre

Re : Requete réticente à optim

Oui, j'ai replacé l'index conseillé afin de fiabiliser la jointure.
create ALT_INDX on navigation (id_client,lower(famille))

Puis, voici l'explain (après analyse et reindex des deux tables).
Le nested loop a pris cher ... 

Nested Loop  (cost=80507.07..280402052.72 rows=9343316402 width=89)
  ->  Seq Scan on animation  (cost=0.00..30050.19 rows=1169619 width=89)
  ->  Materialize  (cost=80507.07..80586.95 rows=7988 width=0)
        ->  Hash Join  (cost=660.20..80499.08 rows=7988 width=0)
              Hash Cond: ((a.id_client = b.id_client) AND (lower((a.famille)::text) = b.famille))
              ->  Seq Scan on animation a  (cost=0.00..30050.19 rows=1169619 width=22)
              ->  Hash  (cost=355.88..355.88 rows=20288 width=22)
                    ->  Seq Scan on relances b  (cost=0.00..355.88 rows=20288 width=22)
Time: 0.135s

Par rapport à l'initial de ce matin :
Nested Loop  (cost=58286.14..58171735.78 rows=1935733862 width=89)
  ->  Seq Scan on animation  (cost=0.00..30082.78 rows=1170678 width=89)
  ->  Materialize  (cost=58286.14..58302.68 rows=1654 width=0)
        ->  Merge Join  (cost=0.07..58284.48 rows=1654 width=0)
              Merge Cond: (b.id_client = a.id_client)
              Join Filter: (lower((a.famille)::text) = b.famille)
              ->  Index Scan using "IDX_RELANCE_ID_copy" on relances b  (cost=0.00..672.57 rows=20288 width=22)
              ->  Index Scan using "IDX_ID_copy" on animation a  (cost=0.00..48979.55 rows=1170678 width=22)
Time: 0.141s

Marc Cousin

Membre

Re : Requete réticente à optim

Houla. Ma faute. J'ai mal regardé

update navigation
set relance = current_date
from navigation a
inner join relances b
on a.id_client = b.id_client and lower(a.famille) = b.famille

Ça ne s'écrit pas comme ça.

update navigation a
set relance = current_date
from relances b
on a.id_client = b.id_client and lower(a.famille) = b.famille

Sinon, vous avez un produit cartésien: vous avez déclaré 2 fois 'navigation' dans la requête.

---

Marc.

MichaelT

Membre

Re : Requete réticente à optim

MY GODNESS !!!

Quand je parlais de féssée dans mon premier mail, je l'avais bien senti que j'étais en train de contempler une belle connerie de toute ma hauteur.
Fatigue, fatigue, fatigue, déjà lundi soir je me suis endormi à un feu rouge ...

Merci Marc.

Marc Cousin

Membre

Re : Requete réticente à optim

Pas de pb. Moi aussi je suis passé au dessus, malgré le rows=1935733862 du plan initial

---

Marc.