Forums PostgreSQL.fr

flo

Membre

Re : [RESOLU] Utilisation des fonctions fenêtrées

Sinon, j'ai quand même quelque chose sur la requête : au vu de ce que vous dites, la table intervention qui est jointurée avec les sous-requêtes sert à garantir qu'on puisse avoir toutes les possibilités de la clause Where . En ce sens, faire travailler postgres sur toute la table intervention ne sert à rien, c'est seulement int_refinst qui nous intéresse.

Or ant_refinst est probablement la clé primaire d'une autre table (la table des techniciens?). Je serais vous, je remplacerait sans hésiter la table intervention qui apparait en premier dans la requête par cette table : elle est probablement beaucoup plus petite, et elle évite le DISTINCT (un gros tri en moins...) Si la table contient des identifiants d'autres personnes (des personnes qui ne sont pas techniciens) je tenterait le coup tout de même, en ajoutant le critère qui va bien.

Ca n'empêche pas de regarder si l'ANALYZE a été fait récemment. Au fait, vous travaillez toujours sur l'environnement de développement? On (les développeurs) a souvent tendance à oublier les opérations de maintenance sur les bases de test et de développement...

Au passage, une question pour Guillaume : je suppose que sa table "bouge" beaucoup (création de nouvelles lignes, voire suppressions). Si c'est le cas, il faudrait qu'il s'assure qu'il y a un ANALYZE régulier fait sur la base de prod (voire celle de test) ?

flo

Membre

Re : [RESOLU] Utilisation des fonctions fenêtrées

Je pense à autre chose, mais je n'en suis pas sûre : dans la clause where, il y a la condition :

(nb_inter_sav IS NOT NULL OR nb_inter_ig IS NOT NULL) AND (change_box_ig IS NOT NULL OR change_box_sav IS NOT NULL)

autrement dit, si on renomme les sous-requêtes en A, B, C, D (j'ai la flemme de recopier...) :

(A ou B ) et (C ou D)

On ne pourrait pas remplacer la clause where par des jointures de type FULL OUTER JOIN entre A et B  , et entre C et D, et une INNER JOIN entre le résultat?
Je n'ai pas l'habitude du FULL OUTER JOIN, mais il me semble que c'est l'équivalent d'un OU logique?

Si je ne me trompe pas, on peut donc de cette manière se passer de la première table de la requête.

Qu'en pensez-vous?

loicDu28

Membre

Re : [RESOLU] Utilisation des fonctions fenêtrées

Réponse à gleu : Si par Analyse tu parle d'un Explain Analyse sur la requête, oui je l'est effectué.

Réponse à flo :
1) Effectivement lorsque je remplace la table intervention qui apparait en premier dans la requête par la table correspondant aux technicien (table 'presta') la requête s'exécute beaucoup plus rapidement (250ms)
Voici la requête modifié :

SELECT DISTINCT presta.pre_id, ((coalesce(change_box_ig,0))+(coalesce(change_box_sav,0)))AS nb_box, (coalesce(nb_inter_sav,0)+coalesce(nb_inter_ig,0))AS nb_inter
			FROM presta LEFT JOIN 
			(SELECT int_refinst, COUNT(*) AS change_box_ig
			 FROM intervention JOIN conclusion_ig ON id_ca = conclu_ig_id 
			 WHERE conclu_ig_date BETWEEN '20091214' AND '20091219'
			 AND conclu_ig_anc_box <>''
			 GROUP BY int_refinst) as tab_ig ON tab_ig.int_refinst = presta.pre_id
			LEFT JOIN
			(SELECT int_refinst, COUNT(*) AS change_box_sav
			 FROM intervention JOIN conclusion_sav ON id_ca = conclu_sav_id 
			 WHERE conclu_sav_date BETWEEN '20091214' AND '20091219'
			 AND conclu_sav_anc_box <>''
			GROUP BY int_refinst) as tab_sav ON tab_sav.int_refinst = presta.pre_id
			LEFT JOIN
			(SELECT int_refinst,COUNT(*) AS nb_inter_sav
			 FROM intervention JOIN conclusion_sav ON id_ca = conclu_sav_id 
			 WHERE conclu_sav_date BETWEEN '20091214' AND '20091219'
			 GROUP BY int_refinst
			 HAVING COUNT(*) > 10
			) as tab_nb_inter_sav on tab_nb_inter_sav.int_refinst = presta.pre_id
			LEFT JOIN
			(SELECT int_refinst,COUNT(*) AS nb_inter_ig
			 FROM intervention JOIN conclusion_ig ON id_ca = conclu_ig_id 
			 WHERE conclu_ig_date BETWEEN '20091214' AND '20091219'
			 GROUP BY int_refinst
			 HAVING COUNT(*) > 10
			) as tab_nb_inter_ig on tab_nb_inter_ig.int_refinst = presta.pre_id
			WHERE (nb_inter_sav IS NOT NULL OR nb_inter_ig IS NOT NULL)
			AND (change_box_ig IS NOT NULL OR change_box_sav IS NOT NULL)

et son explain analyze :

"Unique  (cost=22773.45..22787.22 rows=1377 width=40) (actual time=158.923..159.139 rows=42 loops=1)"
"  ->  Sort  (cost=22773.45..22776.90 rows=1377 width=40) (actual time=158.916..158.982 rows=42 loops=1)"
"        Sort Key: presta.pre_id, (COALESCE(tab_ig.change_box_ig, 0::bigint) + COALESCE(tab_sav.change_box_sav, 0::bigint)), (COALESCE(tab_nb_inter_sav.nb_inter_sav, 0::bigint) + COALESCE(tab_nb_inter_ig.nb_inter_ig, 0::bigint))"
"        ->  Hash Left Join  (cost=22279.89..22701.66 rows=1377 width=40) (actual time=146.337..158.735 rows=42 loops=1)"
"              Hash Cond: ((presta.pre_id)::text = (tab_nb_inter_ig.int_refinst)::text)"
"              Filter: ((tab_nb_inter_sav.nb_inter_sav IS NOT NULL) OR (tab_nb_inter_ig.nb_inter_ig IS NOT NULL))"
"              ->  Hash Left Join  (cost=18710.06..19063.25 rows=1377 width=32) (actual time=100.332..112.152 rows=270 loops=1)"
"                    Hash Cond: ((presta.pre_id)::text = (tab_nb_inter_sav.int_refinst)::text)"
"                    ->  Hash Left Join  (cost=5492.94..5695.14 rows=1377 width=24) (actual time=28.342..39.252 rows=270 loops=1)"
"                          Hash Cond: ((presta.pre_id)::text = (tab_sav.int_refinst)::text)"
"                          Filter: ((tab_ig.change_box_ig IS NOT NULL) OR (tab_sav.change_box_sav IS NOT NULL))"
"                          ->  Hash Left Join  (cost=1151.31..1306.40 rows=1377 width=16) (actual time=7.886..15.771 rows=1367 loops=1)"
"                                Hash Cond: ((presta.pre_id)::text = (tab_ig.int_refinst)::text)"
"                                ->  Seq Scan on presta  (cost=0.00..149.77 rows=1377 width=8) (actual time=0.017..3.124 rows=1367 loops=1)"
"                                ->  Hash  (cost=1151.11..1151.11 rows=16 width=16) (actual time=7.835..7.835 rows=61 loops=1)"
"                                      ->  Subquery Scan tab_ig  (cost=1150.75..1151.11 rows=16 width=16) (actual time=7.412..7.705 rows=61 loops=1)"
"                                            ->  HashAggregate  (cost=1150.75..1150.95 rows=16 width=7) (actual time=7.407..7.513 rows=61 loops=1)"
"                                                  ->  Nested Loop  (cost=11.44..1150.67 rows=16 width=7) (actual time=1.330..7.194 rows=71 loops=1)"
"                                                        ->  Bitmap Heap Scan on conclusion_ig  (cost=11.44..1017.10 rows=16 width=13) (actual time=1.262..5.019 rows=71 loops=1)"
"                                                              Recheck Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
"                                                              Filter: ((conclu_ig_anc_box)::text <> ''::text)"
"                                                              ->  Bitmap Index Scan on i_conclu_ig_date  (cost=0.00..11.44 rows=317 width=0) (actual time=1.008..1.008 rows=1662 loops=1)"
"                                                                    Index Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
"                                                        ->  Index Scan using intervention_pkey on intervention  (cost=0.00..8.34 rows=1 width=20) (actual time=0.017..0.019 rows=1 loops=71)"
"                                                              Index Cond: ((intervention.id_ca)::text = (conclusion_ig.conclu_ig_id)::text)"
"                          ->  Hash  (cost=4338.68..4338.68 rows=236 width=16) (actual time=20.374..20.374 rows=234 loops=1)"
"                                ->  Subquery Scan tab_sav  (cost=4333.37..4338.68 rows=236 width=16) (actual time=18.675..19.854 rows=234 loops=1)"
"                                      ->  HashAggregate  (cost=4333.37..4336.32 rows=236 width=7) (actual time=18.670..19.083 rows=234 loops=1)"
"                                            ->  Nested Loop  (cost=39.22..4332.19 rows=236 width=7) (actual time=1.760..17.773 rows=335 loops=1)"
"                                                  ->  Bitmap Heap Scan on conclusion_sav  (cost=39.22..2422.01 rows=236 width=13) (actual time=1.733..8.873 rows=335 loops=1)"
"                                                        Recheck Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
"                                                        Filter: ((conclu_sav_anc_box)::text <> ''::text)"
"                                                        ->  Bitmap Index Scan on i_conclu_sav_date  (cost=0.00..39.16 rows=1490 width=0) (actual time=1.528..1.528 rows=2537 loops=1)"
"                                                              Index Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
"                                                  ->  Index Scan using intervention_pkey on intervention  (cost=0.00..8.08 rows=1 width=20) (actual time=0.013..0.015 rows=1 loops=335)"
"                                                        Index Cond: ((intervention.id_ca)::text = (conclusion_sav.conclu_sav_id)::text)"
"                    ->  Hash  (cost=13206.98..13206.98 rows=812 width=16) (actual time=71.947..71.947 rows=44 loops=1)"
"                          ->  Subquery Scan tab_nb_inter_sav  (cost=13184.65..13206.98 rows=812 width=16) (actual time=71.516..71.838 rows=44 loops=1)"
"                                ->  HashAggregate  (cost=13184.65..13198.86 rows=812 width=7) (actual time=71.510..71.693 rows=44 loops=1)"
"                                      Filter: (count(*) > 10)"
"                                      ->  Nested Loop  (cost=39.53..13173.47 rows=1490 width=7) (actual time=1.693..65.881 rows=2537 loops=1)"
"                                            ->  Bitmap Heap Scan on conclusion_sav  (cost=39.53..2418.59 rows=1490 width=13) (actual time=1.657..8.509 rows=2537 loops=1)"
"                                                  Recheck Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
"                                                  ->  Bitmap Index Scan on i_conclu_sav_date  (cost=0.00..39.16 rows=1490 width=0) (actual time=1.413..1.413 rows=2537 loops=1)"
"                                                        Index Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
"                                            ->  Index Scan using intervention_pkey on intervention  (cost=0.00..7.21 rows=1 width=20) (actual time=0.010..0.012 rows=1 loops=2537)"
"                                                  Index Cond: ((intervention.id_ca)::text = (conclusion_sav.conclu_sav_id)::text)"
"              ->  Hash  (cost=3565.87..3565.87 rows=317 width=16) (actual time=45.870..45.870 rows=10 loops=1)"
"                    ->  Subquery Scan tab_nb_inter_ig  (cost=3557.15..3565.87 rows=317 width=16) (actual time=45.679..45.837 rows=10 loops=1)"
"                          ->  HashAggregate  (cost=3557.15..3562.70 rows=317 width=7) (actual time=45.673..45.799 rows=10 loops=1)"
"                                Filter: (count(*) > 10)"
"                                ->  Nested Loop  (cost=11.52..3554.77 rows=317 width=7) (actual time=1.175..41.830 rows=1662 loops=1)"
"                                      ->  Bitmap Heap Scan on conclusion_ig  (cost=11.52..1016.38 rows=317 width=13) (actual time=1.142..5.472 rows=1662 loops=1)"
"                                            Recheck Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
"                                            ->  Bitmap Index Scan on i_conclu_ig_date  (cost=0.00..11.44 rows=317 width=0) (actual time=0.995..0.995 rows=1662 loops=1)"
"                                                  Index Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
"                                      ->  Index Scan using intervention_pkey on intervention  (cost=0.00..8.00 rows=1 width=20) (actual time=0.009..0.011 rows=1 loops=1662)"
"                                            Index Cond: ((intervention.id_ca)::text = (conclusion_ig.conclu_ig_id)::text)"
"Total runtime: 160.808 ms"

2) Ensuite j'ai essayé la même requête avec FULL OUTER JOIN et INNER JOIN comme voici :

SELECT DISTINCT presta.pre_id, ((coalesce(change_box_ig,0))+(coalesce(change_box_sav,0)))AS nb_box, (coalesce(nb_inter_sav,0)+coalesce(nb_inter_ig,0))AS nb_inter
FROM presta LEFT JOIN 
(SELECT int_refinst, COUNT(*) AS change_box_ig
 FROM intervention JOIN conclusion_ig ON id_ca = conclu_ig_id 
 WHERE conclu_ig_date BETWEEN '20091214' AND '20091219'
 AND conclu_ig_anc_box <>''
 GROUP BY int_refinst) as tab_ig ON tab_ig.int_refinst = presta.pre_id
FULL OUTER JOIN
(SELECT int_refinst, COUNT(*) AS change_box_sav
 FROM intervention JOIN conclusion_sav ON id_ca = conclu_sav_id 
 WHERE conclu_sav_date BETWEEN '20091214' AND '20091219'
 AND conclu_sav_anc_box <>''
GROUP BY int_refinst) as tab_sav ON tab_sav.int_refinst = presta.pre_id
INNER JOIN
(SELECT int_refinst,COUNT(*) AS nb_inter_sav
 FROM intervention JOIN conclusion_sav ON id_ca = conclu_sav_id 
 WHERE conclu_sav_date BETWEEN '20091214' AND '20091219'
 GROUP BY int_refinst
 HAVING COUNT(*) > 10
) as tab_nb_inter_sav on tab_nb_inter_sav.int_refinst = presta.pre_id
FULL OUTER JOIN
(SELECT int_refinst,COUNT(*) AS nb_inter_ig
 FROM intervention JOIN conclusion_ig ON id_ca = conclu_ig_id 
 WHERE conclu_ig_date BETWEEN '20091214' AND '20091219'
 GROUP BY int_refinst
 HAVING COUNT(*) > 10
) as tab_nb_inter_ig on tab_nb_inter_ig.int_refinst = presta.pre_id

mais la requête me renvoit également les IPCA lorsque le nombre de box est à 0 donc ce n'est pas correct.

Merci beaucou pour votre aide.

Cordialement.

flo

Membre

Re : [RESOLU] Utilisation des fonctions fenêtrées

Analyse : je pense que Guillaume parlait de la commande analyse, qui sert à collecter les statistiques. Si les statistiques sont trop loin de la réalité, le planificateur se plante.
http://docs.postgresqlfr.org/8.1/sql-analyze.html

Requête :
Vous avez supprimé la clause where de la requête, donc elle ramène tous les techniciens.

Dernière modification par flo (22/04/2010 13:11:28)

loicDu28

Membre

Re : [RESOLU] Utilisation des fonctions fenêtrées

Bonjour,

En fait c'est pas grave si elle prend pas en compte l'index, la requête s'exécute assez rapidement.

J'ai retiré la clause where car tu avait dit :

On ne pourrait pas remplacer la clause where par des jointures de type FULL OUTER JOIN entre A et B  , et entre C et D, et une INNER JOIN entre le résultat?

Enfin, c'est pas grave je pense l'a laisser comme ça.

Cordialement.

flo

Membre

Re : [RESOLU] Utilisation des fonctions fenêtrées

C'était peut-être pas très clair :  certainement que cela fonctionne avec la table presta à la place de la table intervention. Mais il  faut évidemment laisser la clause where, sinon la requête est fausse.

L'autre post était plus une question aux autres membres du forum, pour la "culture", parce que je me demandais s'il n'y avait pas moyen de faire mieux.
Évidemment si la requête avec presta est suffisamment rapide, laissez-la telle quelle.

Par contre je vous conseille tout de même de vous renseigner sur les opérations de maintenance (analyse) au moins en production. Et si vous avez des tests à faire, sur l'environnement où vous faites les tests.

loicDu28

Membre

Re : [RESOLU] Utilisation des fonctions fenêtrées

Ok merci beaucoup pour votre aide en tout cas.

Cordialement.