Discussion :

[djaih]

Bonjour,

Mon problème est simple.
La requête ci-dessous est longue et gourmande :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT
DISTINCT t1.id
FROM t1
inner JOIN t2 on t2.id = t1.id
WHERE t2.champ_1= 13
AND t2.champ_2 = 1

Hors c'est une requête simple avec une jointure simple.
t1 contient 1 700 000 lignes et t2 370 000.

Je crois avoir ciblé là ou ça pèche. En effet sur un explain, j'obtiens sur la jointure t2.id = t1.id un nested loop qui est assez gourmand, alors que j'ai des index sur les deux tables. En glanant des infos par ci par là, j'ai vu qu'il y avait un rapport avec les statistiques de la table mais sans comprendre plus que cela.

De plus, cette requête est une version simplifiée au minimum de plusieurs requêtes que j'utilise sur mes sites. Et ces sites accueillent quelques 70 000 visites par jour donc le moindre problème de performance nuit à l’accès des sites.

Si quelqu'un a une idée sur le pourquoi du comment, je suis preneur.

merci à tous

[punkoff]

bonjour,

pour des problèmes de perfs il faut :
- executer un : ANALYZE NOM_TABLE (de toutes les tables de la requetes)
- executer un EXPLAIN ANALYZE MA_REQUETE et poster le résultat texte + la requête.
- donner les ORDRE DDL des tables + indexs associés.

[djaih]

Voila pour le QUERY PLAN

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Unique  (cost=11.75..14040.70 rows=2597 width=4) (actual time=0.424..3031.548 rows=1278 loops=1)
  ->  Nested Loop  (cost=11.75..14034.21 rows=2597 width=4) (actual time=0.423..2925.272 rows=1151261 loops=1)
        ->  Index Scan using t2_idx1 on t2  (cost=0.00..40.49 rows=9 width=4) (actual time=0.176..10.357 rows=1281 loops=1)
              Index Cond: ((etat = 1) AND (vendeur_id = 13))
        ->  Bitmap Heap Scan on t2  (cost=11.75..1549.68 rows=414 width=4) (actual time=0.278..2.119 rows=899 loops=1281)
              Recheck Cond: (residence_id = t2.residence_id)
              ->  Bitmap Index Scan on t1_idx1  (cost=0.00..11.65 rows=414 width=0) (actual time=0.230..0.230 rows=936 loops=1281)
                    Index Cond: (residence_id = t2.residence_id)
Total runtime: 3031.919 ms

[djaih]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
CREATE TABLE _dn.t1 (
  residence_id INTEGER, 
  prix REAL, 
  date_promo INTEGER, 
  min_prix DOUBLE PRECISION, 
  dispo INTEGER, 
  date_d DATE, 
  date_f DATE, 
  heberge_id INTEGER, 
  contrainte_id BIGINT, 
  score BIGINT, 
  type INTEGER, 
  type2 INTEGER, 
  theme INTEGER, 
  pays_id INTEGER, 
  region_id INTEGER, 
  num VARCHAR, 
  ville_id INTEGER, 
  heberge_cap SMALLINT, 
  nuite_id INTEGER, 
  jour_id INTEGER, 
  type_id INTEGER, 
  label_camping_qualite BOOLEAN, 
  forfait_id INTEGER, 
  theme_ete INTEGER, 
  theme_hiver INTEGER, 
  pax BOOLEAN DEFAULT false, 
  fournisseur_id INTEGER, 
  tarif_prix_promo REAL, 
  com_min REAL, 
  nuite_nom VARCHAR[], 
  quartier_id INTEGER, 
  domaine_id INTEGER, 
  priorite INTEGER, 
  request INTEGER DEFAULT 0, 
  vendeur_id INTEGER DEFAULT 0, 
  prio_ville INTEGER DEFAULT 1, 
  massif BOOLEAN
) WITHOUT OIDS;
 
COMMENT ON COLUMN _dn.t1.priorite
IS 'donne la priorite pour l''affichage des listes de resultats';
 
COMMENT ON COLUMN _dn.t1.request
IS '1:tarif en request
0:tarif en allot';
 
COMMENT ON COLUMN _dn.t1.prio_ville
IS '1:ville primaire
2:ville secondaire';
 
CREATE INDEX t1_idx ON _dn.t1
  USING btree (residence_id, region_id, vendeur_id);
 
CREATE INDEX t1_idx1 ON _dn.t1
  USING btree (residence_id);
 
CREATE INDEX t1_idx2 ON _dn.t1
  USING btree (residence_id, vendeur_id, date_d, ville_id);
 
CREATE INDEX t1_idx20 ON _dn.t1
  USING btree (residence_id, pax, min_prix, nuite_id, date_d, vendeur_id, type, type2, theme, forfait_id, priorite, num, score, request);
 
CREATE INDEX t1_idx21 ON _dn.t1
  USING btree (residence_id, nuite_id, date_d, vendeur_id);
 
CREATE INDEX t1_idx22 ON _dn.t1
  USING btree (nuite_id, date_d);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE _dn.t2 (
  residence_id INTEGER, 
  vendeur_id INTEGER, 
  etat SMALLINT, 
  exclusif BOOLEAN DEFAULT false
) WITH OIDS;
 
ALTER TABLE _dn.t2
  ALTER COLUMN residence_id SET STATISTICS 50;
 
ALTER TABLE _dn.t2
  ALTER COLUMN vendeur_id SET STATISTICS 0;
 
ALTER TABLE _dn.t2
  ALTER COLUMN etat SET STATISTICS 0;
 
ALTER TABLE _dn.t2
  ALTER COLUMN exclusif SET STATISTICS 0;
 
CREATE INDEX t2_idx ON _dn.t2
  USING btree (residence_id, vendeur_id);
 
CREATE INDEX t2_idx1 ON _dn.t2
  USING btree (etat, vendeur_id, residence_id);

[djaih]

et la requete

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
EXPLAIN ANALYZE SELECT
DISTINCT t1.residence_id
FROM _dn.t1
inner JOIN _dn.t2 on t2.residence_id = t1.residence_id
WHERE t2.vendeur_id=13
AND t2.etat = 1

[ced]

Le recours au NESTED LOOP par le planificateur de PostgreSQL me laisse parfois dubitatif...
Essayez de le court-circuiter avant la requête, avec l'instruction suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

set enable_nestloop = OFF;

Puis relancez la requête. Est-ce que le plan d'exécution a changé ?

[punkoff]

A quoi sert votre jointure ?
Pourquoi ne pas avoir mis une clef étrangère dans votre table t2 sur les residence_id ?
Pourquoi n'y a-t-il pas de clef primaire ?

Essayez de passer par un exists :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
Select residence_id
FROM _dn.t1
WHERE exists (select null from _dn.t2 where t1.residence_id = t2.residence_id and t2.vendeur_id=13 AND t2.etat = 1)

Pi au vu de ceci :

Nested Loop (cost=11.75..14034.21 rows=2597 width=4) (actual time=0.423..2925.272 rows=1151261 loops=1)

On dirait qu'il fait un joli produit cartésien. comme si toutes les id_residence étaient identiques

[punkoff]

Y a ça aussi qui ne me parait pas très judicieux :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
ALTER TABLE _dn.t2
  ALTER COLUMN vendeur_id SET STATISTICS 0;
 
ALTER TABLE _dn.t2
  ALTER COLUMN etat SET STATISTICS 0;
 
ALTER TABLE _dn.t2
  ALTER COLUMN exclusif SET STATISTICS 0;

lisez ceci : http://www.postgresql.org/docs/9.1/s...ner-stats.html

Là en gros vous avez désactivé les statistiques pour les colonne à 0.
Ça n'aidera pas postgresql à choisir un bon plan d’exécution.

[djaih]

Merci, pour vos réponses. je vais étudier cela dès que possible.