explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YeUT

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

GroupAggregate (cost=32,103,843,714.12..32,103,843,873.00 rows=1,156 width=70) (actual rows= loops=)

  • Group Key: nb_ville.dlp_pjt_numero, nb_ville.hmc_type_historique, nb_ville.dlp_multiville
2. 0.000 0.000 ↓ 0.0

Sort (cost=32,103,843,714.12..32,103,843,743.00 rows=11,555 width=46) (actual rows= loops=)

  • Sort Key: nb_ville.dlp_pjt_numero, nb_ville.hmc_type_historique, nb_ville.dlp_multiville
3. 0.000 0.000 ↓ 0.0

Subquery Scan on nb_ville (cost=1,143,344.93..32,103,842,934.37 rows=11,555 width=46) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,143,344.93..32,103,842,818.82 rows=11,555 width=58) (actual rows= loops=)

  • Group Key: dlp_0.dlp_pjt_numero, hmc.hmc_type_historique, vaa.agglo_produit
5. 0.000 0.000 ↓ 0.0

Sort (cost=1,143,344.93..1,143,373.82 rows=11,555 width=18) (actual rows= loops=)

  • Sort Key: dlp_0.dlp_pjt_numero, hmc.hmc_type_historique, vaa.agglo_produit
6. 0.000 0.000 ↓ 0.0

Gather (cost=88,712.61..1,142,565.18 rows=11,555 width=18) (actual rows= loops=)

  • Workers Planned: 4
7. 0.000 0.000 ↓ 0.0

Hash Join (cost=87,712.61..1,140,409.68 rows=2,889 width=18) (actual rows= loops=)

  • Hash Cond: ((hmc.pve_produit_version_elementaire_id = vaa.pve_dmc_id) AND (hmc.ccd_concedant_id = vaa.ccd_dmc_id))
8. 0.000 0.000 ↓ 0.0

Hash Join (cost=67,515.00..1,118,942.36 rows=29,196 width=14) (actual rows= loops=)

  • Hash Cond: (hmc.dlp_detail_ligne_projet_id = dlp_0.dlp_dmc_id)
9. 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=14) (actual rows= loops=)

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

Hash (cost=67,478.82..67,478.82 rows=2,895 width=8) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_detail_ligne_projet_dlp dlp_0 (cost=0.00..67,478.82 rows=2,895 width=8) (actual rows= loops=)

  • Filter: (upper((dlp_libelle)::text) = 'ESPACE'::text)
12. 0.000 0.000 ↓ 0.0

Hash (cost=19,237.95..19,237.95 rows=63,977 width=24) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash Join (cost=13,501.79..19,237.95 rows=63,977 width=24) (actual rows= loops=)

  • Hash Cond: (vaa.pve_dmc_id = pve_1.pve_dmc_id)
14. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_var_agglo_vaa vaa (cost=0.00..5,135.44 rows=228,844 width=20) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash (cost=12,916.95..12,916.95 rows=46,787 width=4) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_produit_version_elementaire_pve pve_1 (cost=0.00..12,916.95 rows=46,787 width=4) (actual rows= loops=)

  • Filter: ((pve_pdv_univers)::text = 'BUS'::text)
17.          

SubPlan (for GroupAggregate)

18. 0.000 0.000 ↓ 0.0

Result (cost=2,778,251.75..2,778,251.77 rows=1 width=4) (actual rows= loops=)

  • One-Time Filter: (hashed SubPlan 2)
19.          

CTE projet_mv

20. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=91,996.02..1,389,125.86 rows=1 width=12) (actual rows= loops=)

  • Group Key: dlp_1.dlp_pjt_numero
  • Filter: (count(DISTINCT vaa_1.agglo_produit) > 1)
21. 0.000 0.000 ↓ 0.0

Hash Join (cost=91,996.02..1,389,125.82 rows=4 width=16) (actual rows= loops=)

  • Hash Cond: (hmc_1.dlp_detail_ligne_projet_id = dlp_1.dlp_dmc_id)
22. 0.000 0.000 ↓ 0.0

Gather (cost=23,069.89..1,314,134.12 rows=2,310,675 width=16) (actual rows= loops=)

  • Workers Planned: 4
23. 0.000 0.000 ↓ 0.0

Hash Join (cost=22,069.89..1,082,066.62 rows=577,669 width=16) (actual rows= loops=)

  • Hash Cond: ((hmc_1.pve_produit_version_elementaire_id = vaa_1.pve_dmc_id) AND (hmc_1.ccd_concedant_id = vaa_1.ccd_dmc_id))
24. 0.000 0.000 ↓ 0.0

Hash Join (cost=13,501.79..1,064,929.42 rows=1,632,203 width=16) (actual rows= loops=)

  • Hash Cond: (hmc_1.pve_produit_version_elementaire_id = pve_1_1.pve_dmc_id)
25. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on dmc_historique_montant_commerciaux_hmc hmc_1 (cost=0.00..1,036,101.56 rows=5,838,353 width=12) (actual rows= loops=)

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

Hash (cost=12,916.95..12,916.95 rows=46,787 width=4) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_produit_version_elementaire_pve pve_1_1 (cost=0.00..12,916.95 rows=46,787 width=4) (actual rows= loops=)

  • Filter: ((pve_pdv_univers)::text = 'BUS'::text)
28. 0.000 0.000 ↓ 0.0

Hash (cost=5,135.44..5,135.44 rows=228,844 width=20) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_var_agglo_vaa vaa_1 (cost=0.00..5,135.44 rows=228,844 width=20) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Hash (cost=68,926.12..68,926.12 rows=1 width=8) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_detail_ligne_projet_dlp dlp_1 (cost=0.00..68,926.12 rows=1 width=8) (actual rows= loops=)

  • Filter: ((dlp_pjt_numero = dlp_0.dlp_pjt_numero) AND (upper((dlp_libelle)::text) = 'ESPACE'::text))
32. 0.000 0.000 ↓ 0.0

CTE Scan on projet_mv (cost=2,778,251.75..2,778,251.77 rows=1 width=4) (actual rows= loops=)

33.          

SubPlan (for Result)

34. 0.000 0.000 ↓ 0.0

Unique (cost=1,389,125.86..1,389,125.88 rows=4 width=12) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Sort (cost=1,389,125.86..1,389,125.87 rows=4 width=12) (actual rows= loops=)

  • Sort Key: vaa_2.agglo_produit
36. 0.000 0.000 ↓ 0.0

Hash Join (cost=91,996.02..1,389,125.82 rows=4 width=12) (actual rows= loops=)

  • Hash Cond: (hmc_2.dlp_detail_ligne_projet_id = dlp_2.dlp_dmc_id)
37. 0.000 0.000 ↓ 0.0

Gather (cost=23,069.89..1,314,134.12 rows=2,310,675 width=16) (actual rows= loops=)

  • Workers Planned: 4
38. 0.000 0.000 ↓ 0.0

Hash Join (cost=22,069.89..1,082,066.62 rows=577,669 width=16) (actual rows= loops=)

  • Hash Cond: ((hmc_2.pve_produit_version_elementaire_id = vaa_2.pve_dmc_id) AND (hmc_2.ccd_concedant_id = vaa_2.ccd_dmc_id))
39. 0.000 0.000 ↓ 0.0

Hash Join (cost=13,501.79..1,064,929.42 rows=1,632,203 width=16) (actual rows= loops=)

  • Hash Cond: (hmc_2.pve_produit_version_elementaire_id = pve_1_2.pve_dmc_id)
40. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on dmc_historique_montant_commerciaux_hmc hmc_2 (cost=0.00..1,036,101.56 rows=5,838,353 width=12) (actual rows= loops=)

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

Hash (cost=12,916.95..12,916.95 rows=46,787 width=4) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_produit_version_elementaire_pve pve_1_2 (cost=0.00..12,916.95 rows=46,787 width=4) (actual rows= loops=)

  • Filter: ((pve_pdv_univers)::text = 'BUS'::text)
43. 0.000 0.000 ↓ 0.0

Hash (cost=5,135.44..5,135.44 rows=228,844 width=20) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_var_agglo_vaa vaa_2 (cost=0.00..5,135.44 rows=228,844 width=20) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Hash (cost=68,926.12..68,926.12 rows=1 width=4) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_detail_ligne_projet_dlp dlp_2 (cost=0.00..68,926.12 rows=1 width=4) (actual rows= loops=)

  • Filter: ((dlp_pjt_numero = dlp_0.dlp_pjt_numero) AND (upper((dlp_libelle)::text) = 'ESPACE'::text))