explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8k3O

Settings
# exclusive inclusive rows x rows loops node
1. 18.485 773,005.755 ↓ 9,021.0 9,021 1

GroupAggregate (cost=4,296,206.30..4,296,206.44 rows=1 width=1,373) (actual time=772,986.418..773,005.755 rows=9,021 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
2. 74.714 772,987.270 ↓ 9,021.0 9,021 1

Sort (cost=4,296,206.30..4,296,206.30 rows=1 width=1,309) (actual time=772,986.375..772,987.270 rows=9,021 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: 4,365kB
3. 150.092 772,912.556 ↓ 9,021.0 9,021 1

Nested Loop Left Join (cost=6.14..4,296,206.29 rows=1 width=1,309) (actual time=93.707..772,912.556 rows=9,021 loops=1)

4. 33,051.800 772,618.128 ↓ 9,021.0 9,021 1

Nested Loop Left Join (cost=5.72..4,296,205.36 rows=1 width=1,262) (actual time=93.650..772,618.128 rows=9,021 loops=1)

  • Join Filter: (a1_part_ody_cons.part_id = a1_role_pol_cons.part_id)
  • Rows Removed by Join Filter: 643,216,182
5. 57.767 611,937.220 ↓ 9,021.0 9,021 1

Nested Loop Left Join (cost=5.72..4,292,720.22 rows=1 width=1,262) (actual time=70.128..611,937.220 rows=9,021 loops=1)

6. 66.257 611,708.054 ↓ 9,021.0 9,021 1

Nested Loop Left Join (cost=5.28..4,292,719.62 rows=1 width=1,266) (actual time=70.099..611,708.054 rows=9,021 loops=1)

7. 41,505.825 611,596.692 ↓ 9,021.0 9,021 1

Nested Loop Left Join (cost=4.86..4,292,718.71 rows=1 width=1,253) (actual time=70.073..611,596.692 rows=9,021 loops=1)

  • Join Filter: (part.part_id = role_pol.part_id)
  • Rows Removed by Join Filter: 813,617,651
8. 72.461 405,719.226 ↓ 9,021.0 9,021 1

Nested Loop Left Join (cost=4.86..4,289,233.57 rows=1 width=1,253) (actual time=47.077..405,719.226 rows=9,021 loops=1)

9. 143,207.304 405,484.387 ↓ 9,021.0 9,021 1

Nested Loop Left Join (cost=4.42..4,289,232.98 rows=1 width=1,249) (actual time=47.035..405,484.387 rows=9,021 loops=1)

  • Join Filter: (fe_historique_facture.fact_id = fe_facture.fact_id)
  • Rows Removed by Join Filter: 2,194,457,450
  • Filter: ((fe_historique_facture.hfact_id = 1) OR (fe_historique_facture.hfact_id IS NULL))
  • Rows Removed by Filter: 12
10. 63.968 17,627.563 ↓ 9,021.0 9,021 1

Nested Loop (cost=4.42..4,279,231.38 rows=1 width=1,247) (actual time=5.548..17,627.563 rows=9,021 loops=1)

11. 30.623 15,054.803 ↓ 309.0 38,012 1

Nested Loop (cost=4.00..4,277,722.12 rows=123 width=162) (actual time=0.336..15,054.803 rows=38,012 loops=1)

  • Join Filter: (bi_fam_prod.soc_id = bi_soc.soc_id)
  • Rows Removed by Join Filter: 802
12. 92.676 15,024.180 ↓ 264.0 38,012 1

Nested Loop (cost=4.00..4,277,708.38 rows=144 width=140) (actual time=0.324..15,024.180 rows=38,012 loops=1)

  • Join Filter: (bi_prod.fam_id = bi_fam_prod.fam_id)
  • Rows Removed by Join Filter: 365,732
13. 739.609 14,931.504 ↓ 93.2 107,314 1

Nested Loop (cost=4.00..4,277,619.84 rows=1,151 width=127) (actual time=0.313..14,931.504 rows=107,314 loops=1)

  • Join Filter: (pol.pro_id = bi_prod.pro_id)
  • Rows Removed by Join Filter: 9,547,455
14. 169.005 13,762.639 ↓ 93.2 107,314 1

Nested Loop (cost=4.00..4,275,096.60 rows=1,151 width=101) (actual time=0.214..13,762.639 rows=107,314 loops=1)

  • Join Filter: (a1_role_pol_pren.pol_id = fe_objet_facturable.id_gest_objf)
15. 146.620 13,056.324 ↓ 8.8 107,462 1

Nested Loop (cost=3.58..4,268,531.92 rows=12,225 width=70) (actual time=0.194..13,056.324 rows=107,462 loops=1)

16. 206.286 12,587.318 ↓ 8.8 107,462 1

Nested Loop Left Join (cost=3.16..4,263,203.02 rows=12,225 width=54) (actual time=0.180..12,587.318 rows=107,462 loops=1)

17. 116.739 11,306.412 ↓ 8.8 107,462 1

Nested Loop (cost=2.74..4,251,982.83 rows=12,225 width=41) (actual time=0.164..11,306.412 rows=107,462 loops=1)

18. 23.117 10,974.749 ↓ 8.8 107,462 1

Nested Loop (cost=2.44..4,248,184.31 rows=12,225 width=41) (actual time=0.146..10,974.749 rows=107,462 loops=1)

19. 100.164 10,521.788 ↓ 8.9 107,461 1

Nested Loop Left Join (cost=2.01..4,240,974.98 rows=12,135 width=37) (actual time=0.132..10,521.788 rows=107,461 loops=1)

20. 17.695 10,206.702 ↓ 8.9 107,461 1

Nested Loop Left Join (cost=1.59..4,229,837.39 rows=12,135 width=24) (actual time=0.125..10,206.702 rows=107,461 loops=1)

21. 118.885 9,974.085 ↓ 8.9 107,461 1

Nested Loop Left Join (cost=1.29..4,226,045.33 rows=12,135 width=24) (actual time=0.118..9,974.085 rows=107,461 loops=1)

22. 160.399 8,888.051 ↓ 8.9 107,461 1

Nested Loop (cost=0.86..4,218,836.00 rows=12,135 width=20) (actual time=0.097..8,888.051 rows=107,461 loops=1)

23. 690.143 7,975.418 ↓ 8.3 107,462 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.083..7,975.418 rows=107,462 loops=1)

  • Filter: (mupo_gen = (SubPlan 4))
  • Rows Removed by Filter: 2,320,963
  • Heap Fetches: 1,835
24.          

SubPlan (for Index Only Scan)

25. 0.000 7,285.275 ↑ 1.0 1 2,428,425

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

26.          

Initplan (for Result)

27. 2,428.425 7,285.275 ↑ 1.0 1 2,428,425

Limit (cost=0.43..1.58 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,428,425)

28. 4,856.850 4,856.850 ↑ 70.0 1 2,428,425

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,428,425)

  • 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. 752.234 752.234 ↑ 1.0 1 107,462

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

  • Index Cond: ((pol_id = mut_pol.pol_id) AND (mupo_gen = mut_pol.mupo_gen))
30. 967.149 967.149 ↓ 0.0 0 107,461

Index Scan using idx4fk_f_pog_in_pays on role_pol a1_role_pol_cour (cost=0.43..0.58 rows=1 width=12) (actual time=0.009..0.009 rows=0 loops=107,461)

  • 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
31. 214.922 214.922 ↓ 0.0 0 107,461

Index Scan using p_part on part a1_part_ody_cour (cost=0.29..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=107,461)

  • Index Cond: (part_id = a1_role_pol_cour.part_id)
32. 214.922 214.922 ↓ 0.0 0 107,461

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,461)

  • Index Cond: (a1_part_ody_cour.numero_contact = numero_contact)
33. 429.844 429.844 ↑ 1.0 1 107,461

Index Scan using idx4fk_f_pog_in_pays on role_pol a1_role_pol_pren (cost=0.43..0.58 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=107,461)

  • 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
34. 214.924 214.924 ↑ 1.0 1 107,462

Index Scan using p_part 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,462)

  • Index Cond: (part_id = a1_role_pol_pren.part_id)
35. 1,074.620 1,074.620 ↑ 1.0 1 107,462

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.010..0.010 rows=1 loops=107,462)

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

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

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

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.005..0.005 rows=1 loops=107,462)

  • Index Cond: ((pro_id = pol.pro_id) AND (id_gest_objf = pol.pol_id))
38. 429.163 429.256 ↑ 1.6 90 107,314

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

39. 0.093 0.093 ↑ 1.0 145 1

Seq Scan on bi_prod (cost=0.00..19.46 rows=146 width=34) (actual time=0.011..0.093 rows=145 loops=1)

40. 0.000 0.000 ↑ 1.2 4 107,314

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

41. 0.018 0.018 ↑ 1.0 5 1

Seq Scan on bi_fam_prod (cost=0.00..3.65 rows=5 width=17) (actual time=0.006..0.018 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,012

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

43. 0.007 0.007 ↑ 3.0 2 1

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

  • Filter: (soc_id = ANY ('{3,4,5,6,8,10}'::integer[]))
44. 2,346.414 2,508.792 ↓ 0.0 0 38,012

Index Scan using idx4fk_f_fe_objf_fact on fe_facture (cost=0.43..12.26 rows=1 width=1,089) (actual time=0.032..0.066 rows=0 loops=38,012)

  • 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
45.          

SubPlan (for Index Scan)

46. 153.357 153.357 ↑ 1.0 1 9,021

Index Scan using bi_code_pkey on bi_code bic (cost=0.28..2.50 rows=1 width=16) (actual time=0.016..0.017 rows=1 loops=9,021)

  • Index Cond: (((code_type)::text = 'TypeEtatFacture'::text) AND ((fe_facture.etat)::text = (code_cle)::text))
  • Filter: ((cde_lan)::text = 'FR'::text)
47. 9.021 9.021 ↓ 0.0 0 9,021

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 rows=0 loops=9,021)

  • Index Cond: (((code_type)::text = 'TypeRappel'::text) AND ((code_cle)::text = (fe_facture.type_rappel)::text))
  • Filter: ((cde_lan)::text = 'FR'::text)
48. 244,649.520 244,649.520 ↑ 1.0 243,261 9,021

Seq Scan on fe_historique_facture (cost=0.00..6,317.24 rows=245,624 width=6) (actual time=0.005..27.120 rows=243,261 loops=9,021)

49. 162.378 162.378 ↓ 0.0 0 9,021

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

  • 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
50. 164,371.641 164,371.641 ↑ 1.1 90,192 9,021

Seq Scan on part (cost=0.00..2,288.95 rows=95,695 width=8) (actual time=0.006..18.221 rows=90,192 loops=9,021)

51. 45.105 45.105 ↓ 0.0 0 9,021

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 rows=0 loops=9,021)

  • Index Cond: (part.numero_contact = numero_contact)
52. 171.399 171.399 ↑ 1.0 1 9,021

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.015..0.019 rows=1 loops=9,021)

  • Index Cond: ((pol_id = pol_gen.pol_id) AND (mupo_gen = pol_gen.mupo_gen))
  • Filter: ((role)::text = 'CONS'::text)
  • Rows Removed by Filter: 4
53. 127,629.108 127,629.108 ↑ 1.3 71,303 9,021

Seq Scan on part a1_part_ody_cons (cost=0.00..2,288.95 rows=95,695 width=8) (actual time=0.005..14.148 rows=71,303 loops=9,021)

54. 144.336 144.336 ↑ 1.0 1 9,021

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.016..0.016 rows=1 loops=9,021)

  • Index Cond: (a1_part_ody_cons.numero_contact = numero_contact)
Planning time : 14.262 ms
Execution time : 773,006.963 ms