Forums PostgreSQL.fr

stef

Membre

dégradation performances

Bonjour,
je rencontre un problème de dégradation de performances pour une requête qui est assez subtile : 

- Le problème concerne une requête sur 3 tables. par exemple  loueur (750.000 lignes), voiture (6.000.000 lignes), contrôle technique(9.500.000 lignes)

- La requête ressemble à ça :
select c.*,v.*,l.* from controle_tech c, voiture v, loueur l
where c.ident = v.ident  and c.dossier = v.dossier and c.ident = l.ident
and c.departement = '66' and c.type = 3 and date(v.d_loc) < '2013-06-07' and c.status = 'OK' and c.d_dif IS NULL
limit 100

- J'ai 10 instances du même schéma de base (répartition des données géographiquement par "régions"), chacune regroupe quelques départements français

- la requête est lancée pour chaque département indépendamment.

PROBLEME :
cette requête fonctionne correctement sur tous les départements, sauf 1 !
pour tous les départements (sur différentes instances de base) le planificateur fait un Seq Scan sur controle_tech (et une nested loop après)
pour ce département (66 dans l'exemple) le planificateur fait un Index Scan sur la pk controle_tech (et un merge join après)
et là!..... c'est la catastrophe (45mn pour 100 lignes!)

BONNE EXECUTION :
controle_tech ==> nested loop ==> nested loop ==> limit
                     /                       /
   voiture_pk /        loueur_pk /

MAUVAISE EXECUTION :
controle_tech_pk ==> merge join ==> nested loop ==> limit
                          /                       /
        voiture_pk /        loueur_pk /

INFOS :
un vaccum semble résoudre le problème temporairement (quelques jours)
version pgsql : 9.1.6

QUESTION :
quelqu'un connait une méthode qui permettrait d'analyser pourquoi le planificateur fait ce (mauvais) choix alors que ça fonctionne correctement dans les autres cas?
En quoi le vaccum corrige le probleme? (un ANALYSE seul ne change rien)

Merci d'avance

kenrio

Membre

Re : dégradation performances

Vous pouvez montrer l' Explain de la requete  ?

Dernière modification par kenrio (19/06/2013 12:07:01)

stef

Membre

Re : dégradation performances

EXECUTION PROBLEME :
Limit  (cost=131.20..343826.72 rows=100 width=543)
  ->  Nested Loop  (cost=131.20..1460837.17 rows=425 width=543)
        ->  Merge Join  (cost=131.20..1458226.05 rows=425 width=375)
              Merge Cond: (((c.ident)::text = v.ident) AND ((c.dossier)::text = (v.dossier)::text))
              ->  Index Scan using controle_tech_pkey on controle_tech c  (cost=0.00..773165.47 rows=483132 width=76)
                    Index Cond: (type = 3)
                    Filter: ((d_dif IS NULL) AND ((departement)::text = '66'::text) AND (status = 'OK'::status_type))
              ->  Index Scan using voiture_pkey on voiture v  (cost=0.00..678491.52 rows=1972731 width=299)
                    Filter: (date(d_loc) < '2013-06-07'::date)
        ->  Index Scan using loueur_pkey on loueur l  (cost=0.00..6.13 rows=1 width=168)
              Index Cond: ((ident)::text = (c.ident)::text)

EXECUTION OK :
Limit  (cost=0.00..162608.77 rows=100 width=637)
  ->  Nested Loop  (cost=0.00..11930605.13 rows=7337 width=637)
        ->  Nested Loop  (cost=0.00..11893133.11 rows=7337 width=478)
              ->  Seq Scan on controle_tech c  (cost=0.00..651662.00 rows=2843501 width=76)
                    Filter: ((d_dif IS NULL) AND ((departement)::text = '66'::text) AND (type = 3) AND (status = 'OK'::status_type))
              ->  Index Scan using voiture_pkey on voiture v(cost=0.00..3.94 rows=1 width=402)
                    Index Cond: ((ident = (c.ident)::text) AND ((dossier)::text = (c.dossier)::text))
                    Filter: (date(d_loc) < '2013-06-07'::date)
        ->  Index Scan using loueur_pkey on loueur l(cost=0.00..5.09 rows=1 width=159)
              Index Cond: ((ident)::text = (c.ident)::text)

Dernière modification par stef (19/06/2013 12:26:20)

kenrio

Membre

Re : dégradation performances

vos pouvez faire un explain analyse à la place, histoire d'avoir les temps

stef

Membre

Re : dégradation performances

Limit  (cost=131.26..344091.59 rows=100 width=543) (actual time=14245.027..401557.521 rows=100 loops=1)
  ->  Nested Loop  (cost=131.26..1461962.69 rows=425 width=543) (actual time=14245.025..401557.437 rows=100 loops=1)
        ->  Merge Join  (cost=131.26..1459351.57 rows=425 width=375) (actual time=14240.453..401104.198 rows=100 loops=1)
              Merge Cond: (((c.ident)::text = v.ident) AND ((c.dossier)::text = (v.dossier)::text))
              ->  Index Scan using controle_tech_pkey on controle_tech c  (cost=0.00..773993.48 rows=483132 width=76) (actual time=1002.566..236317.019 rows=1612 loops=1)
                    Index Cond: (type = 3)
                    Filter: ((d_dif IS NULL) AND ((departement)::text = '66'::text) AND (status = 'OK'::status_type))
              ->  Index Scan using voiture_pkey on voiture v  (cost=0.00..678791.53 rows=1972731 width=299) (actual time=0.050..164449.243 rows=210255 loops=1)
                    Filter: (date(d_loc) < '2013-06-07'::date)
        ->  Index Scan using loueur_pkey on loueur l (cost=0.00..6.13 rows=1 width=168) (actual time=2.628..4.523 rows=1 loops=100)
              Index Cond: ((ident)::text = (c.ident)::text)
Total runtime: 401557.804 ms

gleu

Administrateur

Re : dégradation performances

Il serait bien d'avoir le EXPLAIN ANALYZE pour la bonne exécution aussi.

Là, ce qu'on peut dire, c'est qu'il se trompe complètement sur son estimation du nombre de lignes. Il pense récupérer 483132 alors qu'il en récupère 1612. Il est très étonnant qu'un ANALYZE ne change rien. Pouvez-vous coller les stats de la table ici ? D'ailleurs, il serait intéressant de connaître les stats avant et après ANALYZE, puis avant et après VACUUM. De plus, combien de lignes fait la table control_tech ?

---

Guillaume.

kenrio

Membre

Re : dégradation performances

Je trouvais aussi très étonnant que le planificateur se trompe à l'envers comparé à d'habitude

je suis content de voir que je suis pas le seul a sécher sur le problème

SAS

Membre

Re : dégradation performances

Le département a-t-il une particularité ?
Une répartition ou une volumétrie différente ?

---

Stéphane Schildknecht
Conseil, formations et support PostgreSQL
http://www.loxodata.com

stef

Membre

Re : dégradation performances

pas de répartitions tres originale pour ce département.
(je suis en train de creuser l'analyse de ce côté.)
Vous pensez à un probleme de stats?

SAS

Membre

Re : dégradation performances

Un problème de stats, ou une répartition à ce point particulière que le plan diffère grandement.

Tout le reste est identique ? Index, schémas...

---

Stéphane Schildknecht
Conseil, formations et support PostgreSQL
http://www.loxodata.com

stef

Membre

Re : dégradation performances

les schémas sont identiques.
je cherche des informations de répartition de données "différentes" mais je ne trouve pas grand chose manuellement.