nested loop sur une jointure simple

Version imprimable

12/02/2013, 16h12
djaih

nested loop sur une jointure simple
Bonjour,

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

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
12/02/2013, 16h46
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.

Voila pour le QUERY PLAN

Code:

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

Code:

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:

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);

et la requete

Code:

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

12/02/2013, 18h10
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:

set enable_nestloop = OFF;

Puis relancez la requête. Est-ce que le plan d'exécution a changé ?
13/02/2013, 11h20
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:

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 :

Citation:

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
13/02/2013, 13h07
punkoff
Y a ça aussi qui ne me parait pas très judicieux :
Code:

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.
13/02/2013, 16h24
djaih

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