Forums PostgreSQL.fr

zeb

Membre

requête trop longue du jour au lendemain

Bonjour,
J'ai une requête assez complexe avec un NOT IN qui du jour au lendemain s'est mise a planté. C'est à dire qu'elle monopolise 100% du CPU.
La veille elle fonctionnait très bien. Et je n'ai pas eu un surplus de données entre ces 2 jours.
Du coup j'ai modifié ma requête en utilisant  NOT EXISTS du coup ça ne plante plus mais je ne comprends pas pourquoi du jour au lendemain ça a bogué. Surtout qua'auparavant elle ne prenait même pas une seconde.
Auriez-vous un explication ? Avez-vous déjà eu le cas ?

zeb

Membre

Re : requête trop longue du jour au lendemain

Voila le resultat d'un explain analyse avant ma modif sur le NOT IN
"Sort  (cost=17266160.19..17266160.20 rows=1 width=143) (actual time=105041.585..105041.585 rows=0 loops=1)"
"  Sort Key: zon.priorite, ol.num_commande"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Nested Loop  (cost=189.14..17266160.18 rows=1 width=143) (actual time=105041.577..105041.577 rows=0 loops=1)"
"        Join Filter: (client_vgs.id_zone = zon.id)"
"        ->  Nested Loop  (cost=0.57..17265971.58 rows=1 width=108) (actual time=105041.577..105041.577 rows=0 loops=1)"
"              Join Filter: (fz.filtre = filtre_vague.num_filtre)"
"              Rows Removed by Join Filter: 67"
"              ->  Seq Scan on filtre_vague  (cost=0.00..1.19 rows=1 width=2) (actual time=0.006..0.008 rows=1 loops=1)"
"                    Filter: (num_vague = 1)"
"                    Rows Removed by Filter: 14"
"              ->  Nested Loop  (cost=0.57..17265970.32 rows=6 width=112) (actual time=105040.991..105041.563 rows=67 loops=1)"
"                    Join Filter: ((zg.code)::text = (fz.zone)::text)"
"                    Rows Removed by Join Filter: 4422"
"                    ->  Seq Scan on filtre_zone fz  (cost=0.00..6.17 rows=67 width=14) (actual time=0.012..0.053 rows=67 loops=1)"
"                          Filter: ((utilisateur)::text = 'FLEUR'::text)"
"                          Rows Removed by Filter: 267"
"                    ->  Materialize  (cost=0.57..17265958.13 rows=6 width=113) (actual time=1567.769..1567.776 rows=67 loops=67)"
"                          ->  Nested Loop  (cost=0.57..17265958.10 rows=6 width=113) (actual time=105040.493..105040.849 rows=67 loops=1)"
"                                Join Filter: (client_vgs.id_zone = zg.id)"
"                                Rows Removed by Join Filter: 4422"
"                                ->  Seq Scan on zone_geographique zg  (cost=0.00..1.67 rows=67 width=9) (actual time=0.002..0.004 rows=67 loops=1)"
"                                ->  Materialize  (cost=0.57..17265950.41 rows=6 width=104) (actual time=1567.209..1567.768 rows=67 loops=67)"
"                                      ->  Nested Loop  (cost=0.57..17265950.38 rows=6 width=104) (actual time=105002.973..105040.300 rows=67 loops=1)"
"                                            Join Filter: (client_vgs.id_tiers = tiers_vgs.id)"
"                                            Rows Removed by Join Filter: 488698"
"                                            ->  Seq Scan on tiers tiers_vgs  (cost=0.00..182.95 rows=7295 width=10) (actual time=0.002..0.434 rows=7295 loops=1)"
"                                            ->  Materialize  (cost=0.57..17265110.90 rows=6 width=102) (actual time=14.388..14.392 rows=67 loops=7295)"
"                                                  ->  Nested Loop  (cost=0.57..17265110.87 rows=6 width=102) (actual time=104963.733..104981.774 rows=67 loops=1)"
"                                                        Join Filter: (client_util.id_client_vgs = client_vgs.id)"
"                                                        Rows Removed by Join Filter: 174066"
"                                                        ->  Seq Scan on client_vgs  (cost=0.00..40.99 rows=2599 width=12) (actual time=0.003..0.117 rows=2599 loops=1)"
"                                                        ->  Materialize  (cost=0.57..17264835.98 rows=6 width=98) (actual time=40.386..40.387 rows=67 loops=2599)"
"                                                              ->  Nested Loop  (cost=0.57..17264835.95 rows=6 width=98) (actual time=104961.855..104961.951 rows=67 loops=1)"
"                                                                    Join Filter: (ue.id_emballage = emb.id)"
"                                                                    Rows Removed by Join Filter: 1005"
"                                                                    ->  Seq Scan on emballage emb  (cost=0.00..1.16 rows=16 width=48) (actual time=0.002..0.002 rows=16 loops=1)"
"                                                                    ->  Materialize  (cost=0.57..17264833.37 rows=6 width=54) (actual time=6550.302..6560.116 rows=67 loops=16)"
"                                                                          ->  Nested Loop  (cost=0.57..17264833.34 rows=6 width=54) (actual time=104804.830..104961.814 rows=67 loops=1)"
"                                                                                Join Filter: (ol.id = ue.id_ol)"
"                                                                                Rows Removed by Join Filter: 1218237"
"                                                                                ->  Nested Loop  (cost=0.57..1996.17 rows=5 width=46) (actual time=8.116..29.088 rows=64 loops=1)"
"                                                                                      Join Filter: (tiers_util.id = client_util.id_tiers)"
"                                                                                      Rows Removed by Join Filter: 297792"
"                                                                                      ->  Nested Loop  (cost=0.57..1353.51 rows=8 width=46) (actual time=7.120..7.691 rows=64 loops=1)"
"                                                                                            ->  Index Scan using ol_pkey on ol  (cost=0.29..1121.86 rows=37 width=42) (actual time=7.099..7.307 rows=64 loops=1)"
"                                                                                                  Filter: ((reel_expedie IS FALSE) AND (ope IS FALSE) AND ((code_utilisateur)::text = 'FLEUR'::text) AND (date_chargement = '2015-04-29'::date))"
"                                                                                                  Rows Removed by Filter: 18832"
"                                                                                            ->  Index Scan using tiers_code_type_key on tiers tiers_util  (cost=0.28..6.25 rows=1 width=15) (actual time=0.005..0.005 rows=1 loops=64)"
"                                                                                                  Index Cond: (((code)::text = (ol.code_client)::text) AND ((type)::text = 'FLEUR'::text))"
"                                                                                      ->  Materialize  (cost=0.00..95.81 rows=4654 width=8) (actual time=0.000..0.115 rows=4654 loops=64)"
"                                                                                            ->  Seq Scan on client_utilisateur client_util  (cost=0.00..72.54 rows=4654 width=8) (actual time=0.005..0.471 rows=4654 loops=1)"
"                                                                                ->  Materialize  (cost=0.00..17261397.32 rows=19860 width=12) (actual time=0.167..1638.433 rows=19036 loops=64)"
"                                                                                      ->  Seq Scan on unite_expedition ue  (cost=0.00..17261298.02 rows=19860 width=12) (actual time=10.533..104786.603 rows=19036 loops=1)"
"                                                                                            Filter: (NOT (SubPlan 1))"
"                                                                                            Rows Removed by Filter: 20588"
"                                                                                            SubPlan 1"
"                                                                                              ->  Materialize  (cost=0.00..785.83 rows=33322 width=4) (actual time=0.002..1.382 rows=24958 loops=39624)"
"                                                                                                    ->  Seq Scan on ue_ot ueot  (cost=0.00..488.22 rows=33322 width=4) (actual time=0.005..3.235 rows=33322 loops=1)"
"        ->  Sort  (cost=188.57..188.58 rows=1 width=23) (never executed)"
"              Sort Key: (CASE (supertie.code IS NULL) WHEN CASE_TEST_EXPR THEN tie.code ELSE supertie.code END), zon.priorite"
"              ->  Nested Loop  (cost=1.84..188.57 rows=1 width=23) (never executed)"
"                    Join Filter: (cdr.id = zon.id_cdr)"
"                    ->  Nested Loop Left Join  (cost=1.84..186.06 rows=1 width=16) (never executed)"
"                          Filter: ((CASE (supertie.code IS NULL) WHEN CASE_TEST_EXPR THEN tie.code ELSE supertie.code END)::text = 'CDR02'::text)"
"                          ->  Hash Left Join  (cost=1.55..94.31 rows=12 width=14) (never executed)"
"                                Hash Cond: (cdr.id_super_cdr = supercdr.id)"
"                                ->  Nested Loop  (cost=0.28..92.87 rows=12 width=14) (never executed)"
"                                      ->  Seq Scan on cdr  (cost=0.00..1.12 rows=12 width=12) (never executed)"
"                                      ->  Index Scan using tiers_pkey on tiers tie  (cost=0.28..7.64 rows=1 width=10) (never executed)"
"                                            Index Cond: (id = cdr.id_tiers)"
"                                ->  Hash  (cost=1.12..1.12 rows=12 width=8) (never executed)"
"                                      ->  Seq Scan on cdr supercdr  (cost=0.00..1.12 rows=12 width=8) (never executed)"
"                          ->  Index Scan using tiers_pkey on tiers supertie  (cost=0.28..7.63 rows=1 width=10) (never executed)"
"                                Index Cond: (supercdr.id_tiers = id)"
"                    ->  Seq Scan on zone_geographique zon  (cost=0.00..1.67 rows=67 width=15) (never executed)"
"Total runtime: 105042.945 ms"

gleu

Administrateur

Re : requête trop longue du jour au lendemain

En ayant que le plan actuel et pas le plan avant, difficile de dire quoi que ce soit.

---

Guillaume.

zeb

Membre

Re : requête trop longue du jour au lendemain

C'est justement l'inverse le plan fourni est le plan d'avant ma modification. Il prends 105042.945 ms !

Celui ci-dessous est celui avec ma modification avec NOT EXISTS qui prends seulement 10.590 ms.

"Sort  (cost=1354.86..1354.86 rows=1 width=143) (actual time=10.220..10.220 rows=0 loops=1)"
"  Sort Key: pri.priorite, ol.num_commande"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Nested Loop Semi Join  (cost=592.54..1354.85 rows=1 width=143) (actual time=10.184..10.184 rows=0 loops=1)"
"        ->  Nested Loop Anti Join  (cost=592.40..1354.31 rows=1 width=147) (actual time=6.609..10.122 rows=26 loops=1)"
"              ->  Nested Loop  (cost=592.11..1353.94 rows=1 width=147) (actual time=6.585..10.049 rows=26 loops=1)"
"                    ->  Hash Join  (cost=591.84..1353.28 rows=1 width=148) (actual time=6.021..9.356 rows=26 loops=1)"
"                          Hash Cond: (client_vgs.id_zone = pri.id_zone)"
"                          ->  Hash Join  (cost=403.24..1164.63 rows=11 width=113) (actual time=5.860..9.175 rows=156 loops=1)"
"                                Hash Cond: (ue.id_emballage = emb.id)"
"                                ->  Hash Join  (cost=401.88..1163.12 rows=11 width=69) (actual time=5.843..9.125 rows=156 loops=1)"
"                                      Hash Cond: (ue.id_ol = ol.id)"
"                                      ->  Seq Scan on unite_expedition ue  (cost=0.00..612.19 rows=39719 width=12) (actual time=0.003..2.639 rows=39624 loops=1)"
"                                      ->  Hash  (cost=401.82..401.82 rows=5 width=61) (actual time=3.443..3.443 rows=64 loops=1)"
"                                            Buckets: 1024  Batches: 1  Memory Usage: 6kB"
"                                            ->  Nested Loop  (cost=307.36..401.82 rows=5 width=61) (actual time=2.056..3.377 rows=64 loops=1)"
"                                                  ->  Nested Loop  (cost=307.22..400.93 rows=5 width=52) (actual time=2.052..3.297 rows=64 loops=1)"
"                                                        ->  Nested Loop  (cost=306.93..398.35 rows=5 width=50) (actual time=1.726..2.555 rows=64 loops=1)"
"                                                              ->  Hash Join  (cost=306.65..396.70 rows=5 width=46) (actual time=1.720..2.413 rows=64 loops=1)"
"                                                                    Hash Cond: (client_util.id_tiers = tiers_util.id)"
"                                                                    ->  Seq Scan on client_utilisateur client_util  (cost=0.00..72.54 rows=4654 width=8) (actual time=0.004..0.350 rows=4654 loops=1)"
"                                                                    ->  Hash  (cost=306.55..306.55 rows=8 width=46) (actual time=1.709..1.709 rows=64 loops=1)"
"                                                                          Buckets: 1024  Batches: 1  Memory Usage: 5kB"
"                                                                          ->  Hash Join  (cost=188.24..306.55 rows=8 width=46) (actual time=1.621..1.694 rows=64 loops=1)"
"                                                                                Hash Cond: ((ol.code_client)::text = (tiers_util.code)::text)"
"                                                                                ->  Bitmap Heap Scan on ol  (cost=5.26..123.26 rows=37 width=42) (actual time=0.040..0.093 rows=64 loops=1)"
"                                                                                      Recheck Cond: (((code_utilisateur)::text = 'FLEUR'::text) AND (date_chargement = '2015-04-29'::date))"
"                                                                                      Filter: ((reel_expedie IS FALSE) AND (ope IS FALSE))"
"                                                                                      ->  Bitmap Index Scan on ol_code_utilisateur_date_chargement_num_commande_reel_exped_key  (cost=0.00..5.25 rows=39 width=0) (actual time=0.028..0.028 rows=74 loops=1)"
"                                                                                            Index Cond: (((code_utilisateur)::text = 'FLEUR'::text) AND (date_chargement = '2015-04-29'::date) AND (reel_expedie = false))"
"                                                                                ->  Hash  (cost=162.86..162.86 rows=1609 width=15) (actual time=1.569..1.569 rows=1609 loops=1)"
"                                                                                      Buckets: 1024  Batches: 1  Memory Usage: 76kB"
"                                                                                      ->  Bitmap Heap Scan on tiers tiers_util  (cost=32.75..162.86 rows=1609 width=15) (actual time=0.974..1.289 rows=1609 loops=1)"
"                                                                                            Recheck Cond: ((type)::text = 'FLEUR'::text)"
"                                                                                            ->  Bitmap Index Scan on fki_tiers_type_fk_type_tiers_code  (cost=0.00..32.35 rows=1609 width=0) (actual time=0.966..0.966 rows=1609 loops=1)"
"                                                                                                  Index Cond: ((type)::text = 'FLEUR'::text)"
"                                                              ->  Index Scan using client_vgs_pkey on client_vgs  (cost=0.28..0.32 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=64)"
"                                                                    Index Cond: (id = client_util.id_client_vgs)"
"                                                        ->  Index Scan using tiers_pkey on tiers tiers_vgs  (cost=0.28..0.51 rows=1 width=10) (actual time=0.011..0.011 rows=1 loops=64)"
"                                                              Index Cond: (id = client_vgs.id_tiers)"
"                                                  ->  Index Scan using zone_geographique_pkey on zone_geographique zg  (cost=0.14..0.17 rows=1 width=9) (actual time=0.001..0.001 rows=1 loops=64)"
"                                                        Index Cond: (id = client_vgs.id_zone)"
"                                ->  Hash  (cost=1.16..1.16 rows=16 width=48) (actual time=0.010..0.010 rows=16 loops=1)"
"                                      Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"                                      ->  Seq Scan on emballage emb  (cost=0.00..1.16 rows=16 width=48) (actual time=0.004..0.005 rows=16 loops=1)"
"                          ->  Hash  (cost=188.59..188.59 rows=1 width=43) (actual time=0.152..0.152 rows=17 loops=1)"
"                                Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"                                ->  Subquery Scan on pri  (cost=188.57..188.59 rows=1 width=43) (actual time=0.146..0.148 rows=17 loops=1)"
"                                      ->  Sort  (cost=188.57..188.58 rows=1 width=23) (actual time=0.146..0.146 rows=17 loops=1)"
"                                            Sort Key: (CASE (supertie.code IS NULL) WHEN CASE_TEST_EXPR THEN tie.code ELSE supertie.code END), zon.priorite"
"                                            Sort Method: quicksort  Memory: 26kB"
"                                            ->  Nested Loop  (cost=1.84..188.57 rows=1 width=23) (actual time=0.044..0.110 rows=17 loops=1)"
"                                                  Join Filter: (cdr.id = zon.id_cdr)"
"                                                  Rows Removed by Join Filter: 184"
"                                                  ->  Nested Loop Left Join  (cost=1.84..186.06 rows=1 width=16) (actual time=0.032..0.066 rows=3 loops=1)"
"                                                        Filter: ((CASE (supertie.code IS NULL) WHEN CASE_TEST_EXPR THEN tie.code ELSE supertie.code END)::text = 'CDR02'::text)"
"                                                        Rows Removed by Filter: 9"
"                                                        ->  Hash Left Join  (cost=1.55..94.31 rows=12 width=14) (actual time=0.021..0.051 rows=12 loops=1)"
"                                                              Hash Cond: (cdr.id_super_cdr = supercdr.id)"
"                                                              ->  Nested Loop  (cost=0.28..92.87 rows=12 width=14) (actual time=0.010..0.036 rows=12 loops=1)"
"                                                                    ->  Seq Scan on cdr  (cost=0.00..1.12 rows=12 width=12) (actual time=0.005..0.006 rows=12 loops=1)"
"                                                                    ->  Index Scan using tiers_pkey on tiers tie  (cost=0.28..7.64 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=12)"
"                                                                          Index Cond: (id = cdr.id_tiers)"
"                                                              ->  Hash  (cost=1.12..1.12 rows=12 width=8) (actual time=0.005..0.005 rows=12 loops=1)"
"                                                                    Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"                                                                    ->  Seq Scan on cdr supercdr  (cost=0.00..1.12 rows=12 width=8) (actual time=0.001..0.003 rows=12 loops=1)"
"                                                        ->  Index Scan using tiers_pkey on tiers supertie  (cost=0.28..7.63 rows=1 width=10) (actual time=0.000..0.001 rows=0 loops=12)"
"                                                              Index Cond: (supercdr.id_tiers = id)"
"                                                  ->  Seq Scan on zone_geographique zon  (cost=0.00..1.67 rows=67 width=15) (actual time=0.001..0.003 rows=67 loops=3)"
"                    ->  Index Scan using filtre_vague_pkey on filtre_zone fz  (cost=0.27..0.65 rows=1 width=14) (actual time=0.026..0.026 rows=1 loops=26)"
"                          Index Cond: (((zone)::text = (zg.code)::text) AND ((utilisateur)::text = 'FLEUR'::text))"
"              ->  Index Only Scan using ue_ot_pkey on ue_ot ueot  (cost=0.29..0.35 rows=2 width=4) (actual time=0.002..0.002 rows=0 loops=26)"
"                    Index Cond: (id_ue = ue.id)"
"                    Heap Fetches: 0"
"        ->  Index Only Scan using filtre_vague_pkey1 on filtre_vague  (cost=0.14..0.34 rows=1 width=2) (actual time=0.002..0.002 rows=0 loops=26)"
"              Index Cond: ((num_vague = 1) AND (num_filtre = fz.filtre))"
"              Heap Fetches: 0"
"Total runtime: 10.590 ms"

gleu

Administrateur

Re : requête trop longue du jour au lendemain

C'est bien ce que je disais. Vous nous fournissez le plan actuel mais pas le plan avant le problème, pour la même requête. On ne peut pas vraiment vous aider avec seulement ça.

---

Guillaume.

zeb

Membre

Re : requête trop longue du jour au lendemain

Je ne sais pas vraiment comment vous le dire mais j'ai bien publié 2 messages avec 2 plans différents correspondant au plan d'avant ma modification et au plan après ma modification. Je l'ai écrit clairement au début de mes messages.
Où alors on ne se comprends vraiment pas ??????

guk92

Membre

Re : requête trop longue du jour au lendemain

L'EXPLAIN ANALYZE est horriblement long, comment distinguer les 2 messages ? gleu ne l'a peut-être pas vu.