explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iDFS

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

Hash Left Join (cost=1,267,905.18..470,348,162,697,134,016.00 rows=23,517,408 width=736) (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=123,912.93..2,272,597.93 rows=23,517,408 width=712) (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=123,908.88..2,064,845.83 rows=23,517,408 width=683) (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=123,903.98..2,001,079.59 rows=23,517,408 width=672) (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=123,902.82..1,901,969.35 rows=23,517,408 width=668) (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=123,895.79..1,838,767.50 rows=23,517,408 width=664) (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=123,882.93..1,775,810.19 rows=23,517,408 width=664) (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=122,364.47..1,712,546.17 rows=23,517,408 width=668) (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=50,543.75..1,578,991.80 rows=23,517,408 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=35,953.24..1,502,666.52 rows=23,517,408 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,750.04..1,406,729.37 rows=23,517,408 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,000)) OR ((dmc_calendrier_cal.atterrissage (...)
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,737.18..1,336,237.30 rows=25,741,725 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,246,235.84 rows=33,621,484 width=118) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash (cost=1,283.09..1,283.09 rows=36,327 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=36,327 width=12) (actual rows= loops=)

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

Hash (cost=10.16..10.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..10.16 rows=216 width=12) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash (cost=29,908.09..29,908.09 rows=343,609 width=61) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_produit_version_vendu_pvv (cost=0.00..29,908.09 rows=343,609 width=61) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=12,498.56..12,498.56 rows=167,356 width=16) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_produit_version_elementaire_pve (cost=0.00..12,498.56 rows=167,356 width=16) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=64,584.21..64,584.21 rows=578,921 width=481) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_detail_ligne_projet_dlp (cost=0.00..64,584.21 rows=578,921 width=481) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=1,253.84..1,253.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..1,253.84 rows=21,170 width=4) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

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

27. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_commercial_cmc dmc_commercial_cmc_init (cost=0.00..10.16 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.91..2.91 rows=91 width=37) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_concedant_dept_cde (cost=0.00..2.91 rows=91 width=37) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash (cost=1,143,977.15..1,143,977.15 rows=863 width=18) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=1,143,852.04..1,143,977.15 rows=863 width=18) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,143,852.04..1,143,968.52 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,143,852.04..1,143,873.61 rows=8,628 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,141,449.97..1,143,288.00 rows=8,628 width=14) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Unique (cost=1,141,449.97..1,143,201.72 rows=8,628 width=18) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Sort (cost=1,141,449.97..1,141,741.93 rows=116,783 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=68,515.00..1,131,620.66 rows=116,783 width=18) (actual rows= loops=)

  • Workers Planned: 4
44. 0.000 0.000 ↓ 0.0

Hash Join (cost=67,515.00..1,118,942.36 rows=29,196 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,036,101.56 rows=5,838,353 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=67,478.82..67,478.82 rows=2,895 width=20) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_detail_ligne_projet_dlp dlp (cost=0.00..67,478.82 rows=2,895 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,053.09..10,000,000,057.20 rows=1 width=10) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10,000,000,053.09..10,000,000,057.20 rows=1 width=10) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Merge Join (cost=52.94..53.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=44.49..44.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..44.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,053.09..10,000,000,057.20 rows=1 width=10) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10,000,000,053.09..10,000,000,057.20 rows=1 width=10) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Merge Join (cost=52.94..53.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=44.49..44.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..44.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)