explain.depesz.com

PostgreSQL's explain analyze made readable

Result: L2Tk

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

Unique (cost=7,676,958,973,374.04..7,676,958,975,660.76 rows=152,448 width=74) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=7,676,958,973,374.04..7,676,958,973,755.16 rows=152,448 width=74) (actual rows= loops=)

  • Sort Key: nb_ville.dlp_pjt_numero, nb_ville.hmc_type_historique, nb_ville.cal_date_historique_id, (sum(nb_ville.dlp_nb_ville_vendue)), nb_ville.dlp_multiville
3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=7,676,958,935,477.02..7,676,958,960,249.82 rows=152,448 width=74) (actual rows= loops=)

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

Sort (cost=7,676,958,935,477.02..7,676,958,939,288.22 rows=1,524,480 width=50) (actual rows= loops=)

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

Subquery Scan on nb_ville (cost=7,676,958,736,990.59..7,676,958,778,913.79 rows=1,524,480 width=50) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Unique (cost=7,676,958,736,990.59..7,676,958,763,669.00 rows=1,524,480 width=62) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Sort (cost=7,676,958,736,990.59..7,676,958,740,801.79 rows=1,524,480 width=62) (actual rows= loops=)

  • Sort Key: vpc.dlp_pjt_numero, vpc.hmc_type_historique, vpc.cal_date_historique_id, vaa.agglo_produit, (count(DISTINCT vaa.agglo_produit)), (CASE WHEN (SubPlan 3) THEN 'Paris-Banlieue et régions'::text WHEN ((count(DIST (...)
8. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=2,712,473.27..7,676,958,580,427.37 rows=1,524,480 width=62) (actual rows= loops=)

  • Group Key: vpc.dlp_pjt_numero, vpc.hmc_type_historique, vpc.cal_date_historique_id, vaa.agglo_produit
9. 0.000 0.000 ↓ 0.0

Sort (cost=2,712,473.27..2,716,284.47 rows=1,524,480 width=22) (actual rows= loops=)

  • Sort Key: vpc.dlp_pjt_numero, vpc.hmc_type_historique, vpc.cal_date_historique_id, vaa.agglo_produit
10. 0.000 0.000 ↓ 0.0

Hash Join (cost=22,321.26..2,555,910.05 rows=1,524,480 width=22) (actual rows= loops=)

  • Hash Cond: ((vpc.pve_dmc_id = vaa.pve_dmc_id) AND (vpc.ccd_concedant_id = vaa.ccd_dmc_id))
11. 0.000 0.000 ↓ 0.0

Hash Join (cost=13,759.71..2,524,938.72 rows=4,268,513 width=22) (actual rows= loops=)

  • Hash Cond: (vpc.pve_dmc_id = pve.pve_dmc_id)
12. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_mvw_photo_commerce_vpc vpc (cost=0.00..2,471,232.27 rows=15,217,427 width=18) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash (cost=13,160.00..13,160.00 rows=47,977 width=4) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_produit_version_elementaire_pve pve (cost=0.00..13,160.00 rows=47,977 width=4) (actual rows= loops=)

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

Hash (cost=5,131.02..5,131.02 rows=228,702 width=20) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

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

17.          

SubPlan (for GroupAggregate)

18. 0.000 0.000 ↓ 0.0

Result (cost=5,035,778.82..5,035,792.48 rows=683 width=4) (actual rows= loops=)

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

CTE projet_mv

20. 0.000 0.000 ↓ 0.0

Unique (cost=2,517,891.54..2,517,894.95 rows=683 width=12) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Sort (cost=2,517,891.54..2,517,893.25 rows=683 width=12) (actual rows= loops=)

  • Sort Key: (count(DISTINCT vaa_1.agglo_produit))
22. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=8,561.55..2,517,859.38 rows=683 width=12) (actual rows= loops=)

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

Hash Join (cost=8,561.55..2,517,847.43 rows=683 width=16) (actual rows= loops=)

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

Seq Scan on dmc_mvw_photo_commerce_vpc vpc0 (cost=0.00..2,509,275.84 rows=1,913 width=12) (actual rows= loops=)

  • Filter: (dlp_pjt_numero = vpc.dlp_pjt_numero)
25. 0.000 0.000 ↓ 0.0

Hash (cost=5,131.02..5,131.02 rows=228,702 width=20) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

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

27. 0.000 0.000 ↓ 0.0

CTE Scan on projet_mv (cost=5,035,778.82..5,035,792.48 rows=683 width=4) (actual rows= loops=)

28.          

SubPlan (for Result)

29. 0.000 0.000 ↓ 0.0

Unique (cost=2,517,879.59..2,517,883.43 rows=173 width=12) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Group (cost=2,517,879.59..2,517,883.00 rows=173 width=12) (actual rows= loops=)

  • Group Key: vaa_2.agglo_produit
31. 0.000 0.000 ↓ 0.0

Sort (cost=2,517,879.59..2,517,881.29 rows=683 width=12) (actual rows= loops=)

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

Hash Join (cost=8,561.55..2,517,847.43 rows=683 width=12) (actual rows= loops=)

  • Hash Cond: ((vpc1.pve_dmc_id = vaa_2.pve_dmc_id) AND (vpc1.ccd_concedant_id = vaa_2.ccd_dmc_id))
33. 0.000 0.000 ↓ 0.0

Seq Scan on dmc_mvw_photo_commerce_vpc vpc1 (cost=0.00..2,509,275.84 rows=1,913 width=8) (actual rows= loops=)

  • Filter: (dlp_pjt_numero = vpc.dlp_pjt_numero)
34. 0.000 0.000 ↓ 0.0

Hash (cost=5,131.02..5,131.02 rows=228,702 width=20) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

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