explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rpVx

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,425,401.21..382,445,061,658,247,488.00 rows=19,122,253 width=738) (actual rows= loops=)

  • Hash Cond: ((dmc_detail_ligne_projet_dlp.dlp_pjt_numero = a.dlp_pjt_numero) AND (dmc_detail_ligne_projet_dlp.dlp_lpr_dw_id = a.dlp_lpr_dw_id) AND ((dmc_historique_montant_commerciaux_hmc.hmc_type_historique)::text = (a.hmc_type_historique)::text))
2. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=126,914.89..2,081,993.09 rows=19,122,253 width=714) (actual rows= loops=)

  • Hash Cond: (dmc_concedant_ccd.ccd_simba2_id = dmc_concedant_dept_cde.ccd_id)
3. 0.000 0.000 ↓ 0.0

Hash Join (cost=126,910.86..1,914,135.21 rows=19,122,253 width=684) (actual rows= loops=)

  • Hash Cond: (dmc_historique_montant_commerciaux_hmc.tpu_type_pub_id = dmc_type_pub_tpu.tpu_dmc_id)
4. 0.000 0.000 ↓ 0.0

Hash Join (cost=126,905.96..1,862,285.27 rows=19,122,253 width=673) (actual rows= loops=)

  • Hash Cond: (dmc_historique_montant_commerciaux_hmc.ste_societe_id = dmc_societe_ste.ste_dmc_id)
5. 0.000 0.000 ↓ 0.0

Hash Join (cost=126,904.80..1,781,697.48 rows=19,122,253 width=669) (actual rows= loops=)

  • Hash Cond: (dmc_historique_montant_commerciaux_hmc.ccd_concedant_id = dmc_concedant_ccd.ccd_dmc_id)
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=126,897.77..1,730,306.08 rows=19,122,253 width=665) (actual rows= loops=)

  • Hash Cond: (dmc_historique_montant_commerciaux_hmc.cmc_commercial_initial_id = dmc_commercial_cmc_init.cmc_dmc_id)
7. 0.000 0.000 ↓ 0.0

Hash Join (cost=126,883.69..1,679,111.20 rows=19,122,253 width=665) (actual rows= loops=)

  • Hash Cond: (dmc_detail_ligne_projet_dlp.tan_tiers_annonceur_id = dmc_tiers_annonceur_tan.tan_dmc_id)
8. 0.000 0.000 ↓ 0.0

Hash Join (cost=125,801.23..1,627,822.77 rows=19,122,253 width=669) (actual rows= loops=)

  • Hash Cond: (dmc_historique_montant_commerciaux_hmc.dlp_detail_ligne_projet_id = dmc_detail_ligne_projet_dlp.dlp_dmc_id)
9. 0.000 0.000 ↓ 0.0

Hash Join (cost=51,398.37..1,503,223.63 rows=19,122,253 width=191) (actual rows= loops=)

  • Hash Cond: (dmc_historique_montant_commerciaux_hmc.pve_produit_version_elementaire_id = dmc_produit_version_elementaire_pve.pve_dmc_id)
10. 0.000 0.000 ↓ 0.0

Hash Join (cost=36,527.97..1,438,156.07 rows=19,122,253 width=179) (actual rows= loops=)

  • Hash Cond: (dmc_historique_montant_commerciaux_hmc.pvv_produit_version_vendu_id = dmc_produit_version_vendu_pvv.pvv_dmc_id)
11. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,675.19..1,353,106.76 rows=19,122,253 width=122) (actual rows= loops=)

  • Hash Cond: (dmc_historique_montant_commerciaux_hmc.cmc_commercial_portefeuille_id = dmc_commercial_cmc_port.cmc_dmc_id)
  • Join Filter: (((dmc_historique_montant_commerciaux_hmc.hmc_pourcentage_ventilation_portefeuille <> '0'::numeric) AND (dmc_calendrier_cal.cal_annee >= 2,018)) OR ((dmc_calendrier_cal.atterrissage (...)
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,657.33..1,296,160.53 rows=20,944,833 width=126) (actual rows= loops=)

  • Hash Cond: (dmc_historique_montant_commerciaux_hmc.cal_date_historique_id = dmc_calendrier_cal.cal_dmc_id)
13. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_historique_montant_commerciaux_hmc (cost=0.00..1,207,363.42 rows=33,193,142 width=118) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash (cost=1,283.09..1,283.09 rows=29,939 width=12) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_calendrier_cal (cost=0.00..1,283.09 rows=29,939 width=12) (actual rows= loops=)

  • Filter: ((cal_annee >= 2,018) OR (atterrissage IS NOT NULL))
16. 0.000 0.000 ↓ 0.0

Hash (cost=15.16..15.16 rows=216 width=12) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_commercial_cmc dmc_commercial_cmc_port (cost=0.00..15.16 rows=216 width=12) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash (cost=30,505.68..30,505.68 rows=347,768 width=61) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_produit_version_vendu_pvv (cost=0.00..30,505.68 rows=347,768 width=61) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=12,732.40..12,732.40 rows=171,040 width=16) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_produit_version_elementaire_pve (cost=0.00..12,732.40 rows=171,040 width=16) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=67,159.05..67,159.05 rows=579,505 width=482) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_detail_ligne_projet_dlp (cost=0.00..67,159.05 rows=579,505 width=482) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=817.84..817.84 rows=21,170 width=4) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Index Only Scan using dmc_tiers_annonceur_tan_pkey on dmc_tiers_annonceur_tan (cost=0.29..817.84 rows=21,170 width=4) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Hash (cost=11.38..11.38 rows=216 width=4) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Index Only Scan using dmc_commercial_cmc_pkey on dmc_commercial_cmc dmc_commercial_cmc_init (cost=0.14..11.38 rows=216 width=4) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash (cost=4.79..4.79 rows=179 width=8) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_concedant_ccd (cost=0.00..4.79 rows=179 width=8) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Hash (cost=1.07..1.07 rows=7 width=8) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_societe_ste (cost=0.00..1.07 rows=7 width=8) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash (cost=3.29..3.29 rows=129 width=15) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_type_pub_tpu (cost=0.00..3.29 rows=129 width=15) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash (cost=2.90..2.90 rows=90 width=38) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_concedant_dept_cde (cost=0.00..2.90 rows=90 width=38) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash (cost=1,298,471.22..1,298,471.22 rows=863 width=18) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=1,298,346.04..1,298,471.22 rows=863 width=18) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,298,346.04..1,298,462.59 rows=863 width=18) (actual rows= loops=)

  • Group Key: r.dlp_pjt_numero, r.dlp_lpr_dw_id, r.hmc_type_historique
39. 0.000 0.000 ↓ 0.0

Sort (cost=1,298,346.04..1,298,367.62 rows=8,634 width=14) (actual rows= loops=)

  • Sort Key: r.dlp_pjt_numero, r.dlp_lpr_dw_id, r.hmc_type_historique
40. 0.000 0.000 ↓ 0.0

Subquery Scan on r (cost=1,295,973.44..1,297,781.56 rows=8,634 width=14) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Unique (cost=1,295,973.44..1,297,695.22 rows=8,634 width=18) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Sort (cost=1,295,973.44..1,296,260.40 rows=114,785 width=18) (actual rows= loops=)

  • Sort Key: dlp.dlp_pjt_numero, dlp.dlp_lpr_dw_id, dlp.dlp_dml_dw_id, hmc.hmc_type_historique, dlp.dlp_duree
43. 0.000 0.000 ↓ 0.0

Gather (cost=71,092.80..1,286,326.58 rows=114,785 width=18) (actual rows= loops=)

  • Workers Planned: 1
44. 0.000 0.000 ↓ 0.0

Hash Join (cost=70,092.80..1,273,848.08 rows=67,521 width=18) (actual rows= loops=)

  • Hash Cond: (hmc.dlp_detail_ligne_projet_id = dlp.dlp_dmc_id)
45. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on dmc_historique_montant_commerciaux_hmc hmc (cost=0.00..1,168,312.66 rows=13,501,851 width=6) (actual rows= loops=)

  • Filter: ((NOT hmc_histo) AND (hmc_pourcentage_ventilation_portefeuille <> 0.0) AND ((hmc_type_ventilation_portefeuille)::text = 'ACHAT'::text))
46. 0.000 0.000 ↓ 0.0

Hash (cost=70,056.57..70,056.57 rows=2,898 width=20) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_detail_ligne_projet_dlp dlp (cost=0.00..70,056.57 rows=2,898 width=20) (actual rows= loops=)

  • Filter: (upper((dlp_libelle)::text) = 'ESPACE'::text)
48.          

SubPlan (for Hash Left Join)

49. 0.000 0.000 ↓ 0.0

Unique (cost=10,000,000,039.09..10,000,000,043.20 rows=1 width=10) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10,000,000,039.09..10,000,000,043.20 rows=1 width=10) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Merge Join (cost=38.94..39.02 rows=1 width=14) (actual rows= loops=)

  • Merge Cond: ((pvv.pvv_dep_departement_geographique_libelle)::text = (dmc_commercial_departement_cde.dep_libelle)::text)
52. 0.000 0.000 ↓ 0.0

Sort (cost=8.45..8.46 rows=1 width=10) (actual rows= loops=)

  • Sort Key: pvv.pvv_dep_departement_geographique_libelle
53. 0.000 0.000 ↓ 0.0

Index Scan using dmc_produit_version_vendu_pvv_pkey on dmc_produit_version_vendu_pvv pvv (cost=0.42..8.44 rows=1 width=10) (actual rows= loops=)

  • Index Cond: (pvv_dmc_id = dmc_produit_version_vendu_pvv.pvv_dmc_id)
54. 0.000 0.000 ↓ 0.0

Sort (cost=30.49..30.52 rows=13 width=14) (actual rows= loops=)

  • Sort Key: dmc_commercial_departement_cde.dep_libelle
55. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_commercial_departement_cde (cost=0.00..30.25 rows=13 width=14) (actual rows= loops=)

  • Filter: (cmc_commercial_id = dmc_detail_ligne_projet_dlp.cmc_dmc_id)
56. 0.000 0.000 ↓ 0.0

Index Only Scan using dmc_commercial_cmc_pkey on dmc_commercial_cmc (cost=0.14..4.16 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (cmc_dmc_id = dmc_detail_ligne_projet_dlp.cmc_dmc_id)
57. 0.000 0.000 ↓ 0.0

Unique (cost=10,000,000,039.09..10,000,000,043.20 rows=1 width=10) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10,000,000,039.09..10,000,000,043.20 rows=1 width=10) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Merge Join (cost=38.94..39.02 rows=1 width=14) (actual rows= loops=)

  • Merge Cond: ((pvv_1.pvv_dep_departement_geographique_libelle)::text = (dmc_commercial_departement_cde_1.dep_libelle)::text)
60. 0.000 0.000 ↓ 0.0

Sort (cost=8.45..8.46 rows=1 width=10) (actual rows= loops=)

  • Sort Key: pvv_1.pvv_dep_departement_geographique_libelle
61. 0.000 0.000 ↓ 0.0

Index Scan using dmc_produit_version_vendu_pvv_pkey on dmc_produit_version_vendu_pvv pvv_1 (cost=0.42..8.44 rows=1 width=10) (actual rows= loops=)

  • Index Cond: (pvv_dmc_id = dmc_produit_version_vendu_pvv.pvv_dmc_id)
62. 0.000 0.000 ↓ 0.0

Sort (cost=30.49..30.52 rows=13 width=14) (actual rows= loops=)

  • Sort Key: dmc_commercial_departement_cde_1.dep_libelle
63. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_commercial_departement_cde dmc_commercial_departement_cde_1 (cost=0.00..30.25 rows=13 width=14) (actual rows= loops=)

  • Filter: (cmc_commercial_id = dmc_detail_ligne_projet_dlp.cmc_dmc_id)
64. 0.000 0.000 ↓ 0.0

Index Only Scan using dmc_commercial_cmc_pkey on dmc_commercial_cmc dmc_commercial_cmc_1 (cost=0.14..4.16 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (cmc_dmc_id = dmc_detail_ligne_projet_dlp.cmc_dmc_id)