Optimisation de requêtes

**jerome_** · 14/04/2010, 10h06

Bonjour,

J'essaie actuellement d'optimiser mes requêtes sur Postgres.

Je suis un peu perdu sur ce qu'il est possible d'optimiser ou non, c'est pourquoi j'ai besoin de votre aide .

Merci

Le schéma :

Code :

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

1
2
3
4
5
 
=========                  ============                                   ====================
= Carte =----------------->= Commande =---------------------------------->= Fichier Commande =
=========  (num_commande)  ============  (num_fichier_commande_client)    ====================
 800000                       3000                                               2000

Ma requête :

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
 
SELECT 
 	fichier_commande_client.date_reception_commande AS COLUMN_1, 
 	carte.num_carte AS COLUMN_2, 
 	produit_client_produit.libelle_produit_client AS COLUMN_3, 
 	commande.creation AS COLUMN_4, 
 	commande.num_commande AS COLUMN_5,
	carte.id_carte AS COLUMN_6
 FROM 
 	carte 
 	JOIN 
 		commande ON 
 		carte.num_commande = commande.num_commande 
 	JOIN 
 		fichier_commande_client ON 
 		commande.num_fichier_commande_client = fichier_commande_client.num_fichier_commande_client 
 	LEFT JOIN 
 		produit_client_produit ON 
 		carte.num_produit = produit_client_produit.num_produit 
 
 WHERE 
 	fichier_commande_client.date_reception_commande >= (date '2010-03-01' )
 	AND 
 	fichier_commande_client.date_reception_commande <= COALESCE(NULL, current_timestamp) 
 	AND 
 	fichier_commande_client.num_client = '185000'
 ORDER BY COLUMN_1 ASC

La requête prend 17secondes pour sortir 63000 résultats

L'explain analyse :

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
 
Sort  (cost=82772.77..83035.41 rows=105058 width=134) (actual time=1129.446..1160.467 rows=62710 loops=1)
  Sort Key: fichier_commande_client.date_reception_commande
  Sort Method:  external sort  Disk: 10688kB
  ->  Hash Left Join  (cost=154.99..59646.49 rows=105058 width=134) (actual time=26.361..1030.390 rows=62710 loops=1)
        Hash Cond: ((carte.num_produit)::text = (produit_client_produit.num_produit)::text)
        ->  Hash Join  (cost=128.45..58175.41 rows=105058 width=94) (actual time=25.007..952.029 rows=62710 loops=1)
              Hash Cond: ((carte.num_commande)::text = (commande.num_commande)::text)
              ->  Seq Scan on carte  (cost=0.00..54172.73 rows=752973 width=85) (actual time=0.010..464.375 rows=752751 loops=1)
              ->  Hash  (cost=124.64..124.64 rows=305 width=17) (actual time=7.350..7.350 rows=297 loops=1)
                    ->  Hash Join  (cost=71.53..124.64 rows=305 width=17) (actual time=3.457..7.018 rows=297 loops=1)
                          Hash Cond: ((commande.num_fichier_commande_client)::text = (fichier_commande_client.num_fichier_commande_client)::text)
                          ->  Seq Scan on commande  (cost=0.00..41.86 rows=2186 width=17) (actual time=0.008..1.362 rows=2186 loops=1)
                          ->  Hash  (cost=67.72..67.72 rows=305 width=16) (actual time=3.310..3.310 rows=297 loops=1)
                                ->  Seq Scan on fichier_commande_client  (cost=0.00..67.72 rows=305 width=16) (actual time=2.066..2.980 rows=297 loops=1)
                                      Filter: ((date_reception_commande >= '2010-03-01'::date) AND ((num_client)::text = '185000'::text) AND (date_reception_commande <= COALESCE(now())))
        ->  Hash  (cost=18.46..18.46 rows=646 width=52) (actual time=1.327..1.327 rows=646 loops=1)
              ->  Seq Scan on produit_client_produit  (cost=0.00..18.46 rows=646 width=52) (actual time=0.013..0.637 rows=646 loops=1)
Total runtime: 1177.482 ms

Est-il possible d'optimiser la partie jointure sur les cartes?

**Oishiiii** · 14/04/2010, 10h14

Bonjour,

Pour optimiser votre requête, voir la structure des tables aurait été utile.

- Vous avez des indexs sur vos tables ?
- A quoi sert le COALESCE ?
- La colonne num_client est de type chaine de caractère ?

**SQLpro** · 14/04/2010, 10h33

Votre requête récrite avec des alias :

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
SELECT FCC.date_reception_commande AS COLUMN_1, 
       C.num_carte AS COLUMN_2, 
       PCP.libelle_produit_client AS COLUMN_3, 
       CD.creation AS COLUMN_4, 
       CD.num_commande AS COLUMN_5,
       C.id_carte AS COLUMN_6
FROM   carte AS C
       INNER JOIN commande AS CD 
             ON C.num_commande = CD.num_commande 
       INNER JOIN fichier_commande_client AS FCC 
             ON CD.num_fichier_commande_client = FCC.num_fichier_commande_client 
       LEFT OUTER JOIN produit_client_produit AS PCP
             ON C.num_produit = PCP.num_produit 
WHERE  FCC.date_reception_commande >= '2010-03-01'
  AND  FCC.date_reception_commande <= current_timestamp
  AND  FCC.num_client = '185000'
 ORDER BY COLUMN_1 ASC

Créez l'index suivant s'il n'existe pas :

Code :

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

CREATE INDEX X0001 ON fichier_commande_client (num_client, date_reception_commande, num_fichier_commande_client)

A +

**jerome_** · 14/04/2010, 11h12

Tout d'abord, merci de ta réponse :

- structure des tables et indexes :

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
 
CREATE TABLE carte
(
  id_carte character(50) NOT NULL,
  num_carte character(19),
  numero_carte character(10),
  etat character(2),
  num_commande character varying(50) NOT NULL,
  num_produit character varying(10) NOT NULL,
  CONSTRAINT carte_pkey PRIMARY KEY (id_carte),
  CONSTRAINT fk_carte_1 FOREIGN KEY (num_commande)
      REFERENCES commande (num_commande) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);
 
CREATE INDEX index_fk_carte_1
  ON carte
  USING btree
  (num_commande);
 
CREATE INDEX index_fk_carte_2
  ON carte
  USING btree
  (num_produit);
 
CREATE TABLE commande
(
  num_commande character varying(50) NOT NULL,
  creation timestamp(6) with time zone,
  num_fichier_commande_client character varying(50) NOT NULL,
  CONSTRAINT commande_pkey PRIMARY KEY (num_commande),
  CONSTRAINT fk_commande_1 FOREIGN KEY (num_fichier_commande_client)
      REFERENCES fichier_commande_client (num_fichier_commande_client) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);
 
CREATE INDEX index_fk_commande_1
  ON commande
  USING btree
  (num_fichier_commande_client);
 
 
CREATE TABLE fichier_commande_client
(
  num_fichier_commande_client character varying(50) NOT NULL,
  date_reception_commande timestamp(6) with time zone,
  num_client character varying(10) NOT NULL,
  CONSTRAINT fichier_commande_client_pkey PRIMARY KEY (num_fichier_commande_client),
);
 
CREATE TABLE produit_client_produit
(
  num_client character varying(10) NOT NULL,
  num_produit_client character varying(10) NOT NULL,
  libelle_produit_client character(45),
  num_produit character varying(5) NOT NULL,
  CONSTRAINT produit_client_produit_pkey PRIMARY KEY (num_produit_client, num_produit),
);
 
CREATE INDEX index_fk_produit_client_produit_3
  ON produit_client_produit
  USING btree
  (num_produit);

- COALESCE :
en effet, ici il ne sert à rien.

**jerome_** · 14/04/2010, 11h49

Merci SQLPro,

J'ai modifié la requête et créé l'index. (et analyse)

Quand je l'exécute telle quelle, je ne vois pas d'amélioration au niveau des performances.

**jerome_** · 14/04/2010, 11h56

Voici le nouveau plan d'exécution :

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
Sort  (cost=82740.69..83003.26 rows=105027 width=134) (actual time=1076.495..1107.563 rows=62710 loops=1)
  Sort Key: t_fichier_commande.date_reception_commande
  Sort Method:  external sort  Disk: 10688kB
  ->  Hash Left Join  (cost=129.51..59617.23 rows=105027 width=134) (actual time=5.182..980.869 rows=62710 loops=1)
        Hash Cond: ((t_carte.num_produit)::text = (t_produit_client.num_produit)::text)
        ->  Hash Join  (cost=102.97..58146.57 rows=105027 width=94) (actual time=4.520..906.041 rows=62710 loops=1)
              Hash Cond: ((t_carte.num_commande)::text = (t_commande.num_commande)::text)
              ->  Seq Scan on carte t_carte  (cost=0.00..54170.51 rows=752751 width=85) (actual time=0.030..443.387 rows=752751 loops=1)
              ->  Hash  (cost=99.16..99.16 rows=305 width=17) (actual time=2.281..2.281 rows=297 loops=1)
                    ->  Hash Join  (cost=46.05..99.16 rows=305 width=17) (actual time=0.465..2.131 rows=297 loops=1)
                          Hash Cond: ((t_commande.num_fichier_commande_client)::text = (t_fichier_commande.num_fichier_commande_client)::text)
                          ->  Seq Scan on commande t_commande  (cost=0.00..41.86 rows=2186 width=17) (actual time=0.005..0.679 rows=2186 loops=1)
                          ->  Hash  (cost=42.24..42.24 rows=305 width=16) (actual time=0.397..0.397 rows=297 loops=1)
                                ->  Bitmap Heap Scan on fichier_commande_client t_fichier_commande  (cost=12.14..42.24 rows=305 width=16) (actual time=0.121..0.255 rows=297 loops=1)
                                      Recheck Cond: (((num_client)::text = '185000'::text) AND (date_reception_commande >= '2010-03-01 00:00:00+01'::timestamp with time zone) AND (date_reception_commande <= now()))
                                      ->  Bitmap Index Scan on index_fk_fichier_commande_client_x1  (cost=0.00..12.07 rows=305 width=0) (actual time=0.114..0.114 rows=297 loops=1)
                                            Index Cond: (((num_client)::text = '185000'::text) AND (date_reception_commande >= '2010-03-01 00:00:00+01'::timestamp with time zone) AND (date_reception_commande <= now()))
        ->  Hash  (cost=18.46..18.46 rows=646 width=52) (actual time=0.647..0.647 rows=646 loops=1)
              ->  Seq Scan on produit_client_produit t_produit_client  (cost=0.00..18.46 rows=646 width=52) (actual time=0.006..0.316 rows=646 loops=1)
Total runtime: 1124.758 ms

**jerome_** · 14/04/2010, 12h09

mea culpa, les performances ont bien augmentées.

En fait, si j'exécute la requête telle quelle je ne vois aucune différence.

Seulement je l'utilise conjointement à un LIMIT X OFFSET Y pour parcourir les données.
En effet, il y a un gain très important avec la nouvelle requête +LIMIT (comparé à l'ancienne + LIMIT).

Si j'ai bien compris, c'est la création de cet index qui permet d'avoir plus de performance au niveau de l'ORDER BY , non?

**SQLpro** · 14/04/2010, 23h52

de la jointure et des critères en premier. De l'order by en dernier. Lisez ce que j'ai écrit sur l'indexation :
http://sqlpro.developpez.com/cours/quoi-indexer/
Notamment paragraphe IX

A +

Optimisation de requêtes

Requêtes PostgreSQL

Discussions similaires

Partager

Partager