explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Utex

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 36,179.858 ↓ 197.0 197 1

GroupAggregate (cost=4,296,261.83..4,296,261.97 rows=1 width=1,373) (actual time=36,179.101..36,179.858 rows=197 loops=1)

  • Group Key: bi_soc.soc_id, bi_fam_prod.fam_id, bi_prod.pro_id, fe_objet_facturable.objf_id, fe_facture.fact_id, ((SubPlan 1)), ((SubPlan 2)), fe_historique_facture.hfact_id, ((fe_facture.contentieux)::integer), ((fe_facture.plan_paiement)::integer), pol.pol_id, (CASE WHEN ((pol.no_police)::text > '100'::text) THEN (substr((pol.no_police)::text, 1, (length((pol.no_police)::text) - 2)))::character varying ELSE pol.no_police END), pol_gen.pri_dte_effet, a1_bi_partenaire_ody_pren.nom_prenom, a1_bi_partenaire_ody_cons.numero_contact, a1_bi_partenaire_ody_cons.nom_prenom, a1_bi_partenaire_ody_cour.nom_prenom, a1_bi_partenaire_ody_autre.nom_prenom
  • rows=0 loops=197)
  • rows=0 loops=197)
  • Execution Time: 36,180.826 ms(114 lignes)
2. 1.624 36,179.063 ↓ 197.0 197 1

Sort (cost=4,296,261.83..4,296,261.84 rows=1 width=1,309) (actual time=36,179.047..36,179.063 rows=197 loops=1)

  • Sort Key: bi_soc.soc_id, bi_fam_prod.fam_id, bi_prod.pro_id, fe_objet_facturable.objf_id, fe_facture.fact_id, ((SubPlan 1)), ((SubPlan 2)), fe_historique_facture.hfact_id, ((fe_facture.contentieux)::integer), ((fe_facture.plan_paiement)::integer), pol.pol_id, (CASE WHEN ((pol.no_police)::text > '100'::text) THEN (substr((pol.no_police)::text, 1, (length((pol.no_police)::text) - 2)))::character varying ELSE pol.no_police END), pol_gen.pri_dte_effet, a1_bi_partenaire_ody_pren.nom_prenom, a1_bi_partenaire_ody_cons.numero_contact, a1_bi_partenaire_ody_cons.nom_prenom, a1_bi_partenaire_ody_cour.nom_prenom, a1_bi_partenaire_ody_autre.nom_prenom
  • Sort Method: quicksort Memory: 119kB
3. 7.031 36,177.439 ↓ 197.0 197 1

Nested Loop Left Join (cost=6.14..4,296,261.82 rows=1 width=1,309) (actual time=405.203..36,177.439 rows=197 loops=1)

4. 3,553.329 36,170.408 ↓ 197.0 197 1

Nested Loop Left Join (cost=5.72..4,296,260.89 rows=1 width=1,262) (actual time=404.583..36,170.408 rows=197 loops=1)

  • Join Filter: (a1_part_ody_cons.part_id = a1_role_pol_cons.part_id)
  • Rows Removed by Join Filter: 15,319,520
5. 4.182 32,617.079 ↓ 197.0 197 1

Nested Loop Left Join (cost=5.72..4,292,785.51 rows=1 width=1,262) (actual time=390.031..32,617.079 rows=197 loops=1)

6. 2.626 32,612.897 ↓ 197.0 197 1

Nested Loop Left Join (cost=5.28..4,292,784.91 rows=1 width=1,266) (actual time=390.005..32,612.897 rows=197 loops=1)

7. 4,154.913 32,610.271 ↓ 197.0 197 1

Nested Loop Left Join (cost=4.86..4,292,783.99 rows=1 width=1,253) (actual time=389.980..32,610.271 rows=197 loops=1)

  • Join Filter: (part.part_id = role_pol.part_id)
  • Rows Removed by Join Filter: 17,842,697
8. 5.042 28,455.358 ↓ 197.0 197 1

Nested Loop Left Join (cost=4.86..4,289,308.61 rows=1 width=1,253) (actual time=365.131..28,455.358 rows=197 loops=1)

9. 7,862.143 28,450.316 ↓ 197.0 197 1

Nested Loop Left Join (cost=4.42..4,289,308.01 rows=1 width=1,249) (actual time=365.093..28,450.316 rows=197 loops=1)

  • Join Filter: (fe_historique_facture.fact_id = fe_facture.fact_id)
  • Rows Removed by Join Filter: 47,972,448
  • Filter: ((fe_historique_facture.hfact_id = 1) OR (fe_historique_facture.hfact_id IS NULL))
  • Rows Removed by Filter: 1
10. 1,897.691 20,588.173 ↓ 197.0 197 1

Nested Loop (cost=4.42..4,279,353.14 rows=1 width=1,247) (actual time=307.070..20,588.173 rows=197 loops=1)

11. 15.933 18,690.482 ↓ 306.8 38,044 1

Nested Loop (cost=4.00..4,277,830.25 rows=124 width=162) (actual time=1.773..18,690.482 rows=38,044 loops=1)

  • Join Filter: (bi_fam_prod.soc_id = bi_soc.soc_id)
  • Rows Removed by Join Filter: 802
12. 70.141 18,674.549 ↓ 262.4 38,044 1

Nested Loop (cost=4.00..4,277,816.41 rows=145 width=140) (actual time=1.749..18,674.549 rows=38,044 loops=1)

  • Join Filter: (bi_prod.fam_id = bi_fam_prod.fam_id)
  • Rows Removed by Join Filter: 365,888
13. 1,042.866 18,604.408 ↓ 92.7 107,358 1

Nested Loop (cost=4.00..4,277,727.35 rows=1,158 width=127) (actual time=1.730..18,604.408 rows=107,358 loops=1)

  • Join Filter: (pol.pro_id = bi_prod.pro_id)
  • Rows Removed by Join Filter: 9,358,250
14. 479.137 17,561.542 ↓ 92.7 107,358 1

Nested Loop (cost=4.00..4,275,188.79 rows=1,158 width=101) (actual time=1.182..17,561.542 rows=107,358 loops=1)

  • Join Filter: (a1_role_pol_pren.pol_id = fe_objet_facturable.id_gest_objf)
15. 311.883 17,082.405 ↓ 8.8 107,499 1

Nested Loop (cost=3.58..4,268,617.87 rows=12,231 width=70) (actual time=1.161..17,082.405 rows=107,499 loops=1)

16. 2,569.215 16,770.522 ↓ 8.8 107,499 1

Nested Loop Left Join (cost=3.16..4,263,286.36 rows=12,231 width=54) (actual time=1.134..16,770.522 rows=107,499 loops=1)

17. 259.042 14,201.307 ↓ 8.8 107,499 1

Nested Loop (cost=2.74..4,252,029.92 rows=12,231 width=41) (actual time=0.202..14,201.307 rows=107,499 loops=1)

18. 397.896 13,942.265 ↓ 8.8 107,499 1

Nested Loop (cost=2.44..4,248,229.53 rows=12,231 width=41) (actual time=0.187..13,942.265 rows=107,499 loops=1)

19. 266.305 13,544.369 ↓ 8.9 107,498 1

Nested Loop Left Join (cost=2.01..4,241,013.40 rows=12,135 width=37) (actual time=0.177..13,544.369 rows=107,498 loops=1)

20. 151.841 13,278.064 ↓ 8.9 107,498 1

Nested Loop Left Join (cost=1.59..4,229,845.30 rows=12,135 width=24) (actual time=0.171..13,278.064 rows=107,498 loops=1)

21. 831.901 13,126.223 ↓ 8.9 107,498 1

Nested Loop Left Join (cost=1.29..4,226,053.24 rows=12,135 width=24) (actual time=0.157..13,126.223 rows=107,498 loops=1)

  • -> Index Scan using idx4fk_f_pog_in_pays on role_pol
22. 136.989 12,294.322 ↓ 8.9 107,498 1

Nested Loop (cost=0.86..4,218,837.10 rows=12,135 width=20) (actual time=0.127..12,294.322 rows=107,498 loops=1)

23. 247.126 7,534.876 ↓ 8.3 107,499 1

Index Only Scan using p_mupo on mut_pol (cost=0.43..4,191,113.60 rows=12,956 width=8) (actual time=0.105..7,534.876 rows=107,499 loops=1)

  • Filter: (mupo_gen = (SubPlan 4))
  • Rows Removed by Filter: 2,321,751
  • Heap Fetches: 5,667
24.          

SubPlan (for Index Only Scan)

25. 2,429.250 7,287.750 ↑ 1.0 1 2,429,250

Result (cost=1.58..1.59 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,429,250)

26.          

Initplan (for Result)

27. 0.000 4,858.500 ↑ 1.0 1 2,429,250

Limit (cost=0.43..1.58 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2,429,250)

28. 4,858.500 4,858.500 ↑ 70.0 1 2,429,250

Index Scan Backward using p_mupo on mut_pol mut (cost=0.43..80.88 rows=70 width=4) (actual time=0.002..0.002 rows=1 loops=2,429,250)

  • Index Cond: ((pol_id = mut_pol.pol_id) AND (mupo_gen IS NOT NULL))
  • Filter: ((etat)::text <> 'SIMU'::text)
  • Rows Removed by Filter: 0
29. 4,622.457 4,622.457 ↑ 1.0 1 107,499

Index Scan using p_pog on pol_gen (cost=0.43..2.14 rows=1 width=12) (actual time=0.043..0.043 rows=1 loops=107,499)

  • Index Cond: ((pol_id = mut_pol.pol_id) AND (mupo_gen = mut_pol.mupo_gen))
30. 429.992 752.486 ↓ 0.0 0 107,498

a1_role_pol_cour (cost=0.43..0.58 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=107,498)

  • Index Cond: ((pol_id = pol_gen.pol_id) AND (mupo_gen = pol_gen.mupo_gen))
  • Filter: ((role)::text = 'COUR'::text)
  • Rows Removed by Filter: 4
  • -> Index Scan using idx4fk_f_pog_in_pays on role_pol a1_role_pol_pren
31. 107.498 107.498 ↓ 0.0 0 107,498

Index Only Scan using olm_perf_bi_512 on part a1_part_ody_cour (cost=0.29..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=107,498)

  • Index Cond: (part_id = a1_role_pol_cour.part_id)
  • Heap Fetches: 42,179
32. 214.996 214.996 ↓ 0.0 0 107,498

Index Scan using bi_partenaire_uk1 on bi_partenaire a1_bi_partenaire_ody_cour (cost=0.42..0.92 rows=1 width=21) (actual time=0.002..0.002 rows=0 loops=107,498)

  • Index Cond: (a1_part_ody_cour.numero_contact = numero_contact)
33. 0.000 322.494 ↓ 0.0 1 107,498

(cost=0.43..0.58 rows=1 width=12) (cost=0..0 rows=0 width=0) (actual time=0.002..0.003 rows=1 loops=107,498)

  • Index Cond: ((pol_id = pol_gen.pol_id) AND (mupo_gen = pol_gen.mupo_gen))
  • Filter: ((role)::text = 'PREN'::text)
  • Rows Removed by Filter: 3
  • -> Index Scan using bi_partenaire_uk1 on bi_partenaire a1_bi_partenaire_ody_autre (cost=0.42..0.92 rows=1 width=21) (actual time=0.005..0.005
  • Index Cond: (part.numero_contact = numero_contact)
34. 214.998 214.998 ↑ 1.0 1 107,499

Index Only Scan using olm_perf_bi_512 on part a1_part_ody_pren (cost=0.29..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=107,499)

  • Index Cond: (part_id = a1_role_pol_pren.part_id)
  • Heap Fetches: 107,499
35. 2,472.477 2,472.477 ↑ 1.0 1 107,499

Index Scan using bi_partenaire_uk1 on bi_partenaire a1_bi_partenaire_ody_pren (cost=0.42..0.92 rows=1 width=21) (actual time=0.023..0.023 rows=1 loops=107,499)

  • Index Cond: (a1_part_ody_pren.numero_contact = numero_contact)
36. 214.998 214.998 ↑ 1.0 1 107,499

Index Scan using p_pol on pol (cost=0.42..0.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=107,499)

  • Index Cond: (pol_id = a1_role_pol_pren.pol_id)
37. 429.996 429.996 ↑ 1.0 1 107,499

Index Scan using fe_objet_facturable_pro_id_id_gest_objf_type_application_ty_idx on fe_objet_facturable (cost=0.42..0.52 rows=1 width=39) (actual time=0.003..0.004 rows=1 loops=107,499)

  • Index Cond: ((pro_id = pol.pro_id) AND (id_gest_objf = pol.pol_id))
38. 428.857 429.432 ↑ 1.7 88 107,358

Materialize (cost=0.00..20.19 rows=146 width=34) (actual time=0.000..0.004 rows=88 loops=107,358)

39. 0.575 0.575 ↑ 1.0 146 1

Seq Scan on bi_prod (cost=0.00..19.46 rows=146 width=34) (actual time=0.414..0.575 rows=146 loops=1)

40. 0.000 0.000 ↑ 1.2 4 107,358

Materialize (cost=0.00..3.67 rows=5 width=17) (actual time=0.000..0.000 rows=4 loops=107,358)

41. 0.046 0.046 ↑ 1.0 5 1

Seq Scan on bi_fam_prod (cost=0.00..3.65 rows=5 width=17) (actual time=0.013..0.046 rows=5 loops=1)

  • Filter: (fam_id = ANY ('{2,3,7,8,39}'::integer[]))
  • Rows Removed by Filter: 35
42. 0.000 0.000 ↑ 6.0 1 38,044

Materialize (cost=0.00..2.15 rows=6 width=26) (actual time=0.000..0.000 rows=1 loops=38,044)

43. 0.021 0.021 ↑ 3.0 2 1

Seq Scan on bi_soc (cost=0.00..2.12 rows=6 width=26) (actual time=0.019..0.021 rows=2 loops=1)

  • Filter: (soc_id = ANY ('{3,4,5,6,8,10}'::integer[]))
44. 1,861.004 1,864.156 ↓ 0.0 0 38,044

Index Scan using idx4fk_f_fe_objf_fact on fe_facture (cost=0.43..12.27 rows=1 width=1,089) (actual time=0.049..0.049 rows=0 loops=38,044)

  • Index Cond: (objf_id = fe_objet_facturable.objf_id)
  • Filter: (((dte_fact >= ((((CURRENT_DATE - mod((date_part('isodow'::text, ((CURRENT_DATE - 1))::timestamp without time zone))::integer, 7)) + '00:00:00'::interval) + '00:00:00'::interval))::date) AND (dte_fact <= ((((CURRENT_DATE - mod((date_part('isodow'::text, ((CURRENT_DATE - 1))::timestamp without time zone))::integer, 7)) + '00:00:00'::interval) + '6 days'::interval))::date)) OR ((dte_valeur >= ((((CURRENT_DATE - mod((date_part('isodow'::text, ((CURRENT_DATE - 1))::timestamp without time zone))::integer, 7)) + '00:00:00'::interval) + '00:00:00'::interval))::date) AND (dte_valeur <= ((((CURRENT_DATE - mod((date_part('isodow'::text, ((CURRENT_DATE - 1))::timestamp without time zone))::integer, 7)) + '00:00:00'::interval) + '6 days'::interval))::date) AND (niv_rappel <> 0)))
  • Rows Removed by Filter: 21
  • -> Index Scan using bi_code_pkey on bi_code bic_1 (cost=0.28..2.50 rows=1 width=16) (actual time=0.001..0.001
  • Index Cond: (((code_type)::text = 'TypeRappel'::text) AND ((code_cle)::text = (fe_facture.type_rappel)::text))
  • Filter: ((cde_lan)::text = 'FR'::text)
45.          

SubPlan (for Index Scan)

46. 3.152 3.152 ↑ 1.0 1 197

Index Scan using bi_code_pkey on bi_code bic (cost=0.28..2.50 rows=1 width=16) (actual time=0.015..0.016 rows=1 loops=197)

  • Index Cond: (((code_type)::text = 'TypeEtatFacture'::text) AND ((fe_facture.etat)::text = (code_cle)::text))
  • Filter: ((cde_lan)::text = 'FR'::text)
47. 4,860.778 4,860.778 ↑ 1.0 243,515 197

Seq Scan on fe_historique_facture (cost=0.00..6,302.15 rows=243,515 width=6) (actual time=0.005..24.674 rows=243,515 loops=197)

48. 3.546 3.546 ↓ 0.0 0 197

Index Scan using idx4fk_f_pog_in_pays on role_pol (cost=0.43..0.58 rows=1 width=12) (actual time=0.017..0.018 rows=0 loops=197)

  • Index Cond: ((pol_id = pol_gen.pol_id) AND (mupo_gen = pol_gen.mupo_gen))
  • Filter: ((role)::text = 'MAND'::text)
  • Rows Removed by Filter: 5
49. 3,262.517 3,262.517 ↑ 1.1 90,572 197

Seq Scan on part (cost=0.00..2,285.17 rows=95,217 width=8) (actual time=0.005..16.561 rows=90,572 loops=197)

50. 3.349 3.349 ↑ 1.0 1 197

Index Scan using idx4fk_f_pog_in_pays on role_pol a1_role_pol_cons (cost=0.43..0.58 rows=1 width=12) (actual time=0.013..0.017 rows=1 loops=197)

  • Index Cond: ((pol_id = pol_gen.pol_id) AND (mupo_gen = pol_gen.mupo_gen))
  • Filter: ((role)::text = 'CONS'::text)
  • Rows Removed by Filter: 5
51. 2,786.959 2,786.959 ↑ 1.2 77,765 197

Seq Scan on part a1_part_ody_cons (cost=0.00..2,285.17 rows=95,217 width=8) (actual time=0.004..14.147 rows=77,765 loops=197)

52. 3.743 3.743 ↑ 1.0 1 197

Index Scan using bi_partenaire_uk1 on bi_partenaire a1_bi_partenaire_ody_cons (cost=0.42..0.92 rows=1 width=21) (actual time=0.019..0.019 rows=1 loops=197)

  • Index Cond: (a1_part_ody_cons.numero_contact = numero_contact)
Planning time : 22.485 ms