explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v8pF

Settings
# exclusive inclusive rows x rows loops node
1. 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=)

2. 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(DISTINCT vaa.agglo_produit) = 1) A (...)
3. 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
4. 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
5. 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))
6. 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)
7. 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=)

8. 0.000 0.000 ↓ 0.0

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

9. 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)
10. 0.000 0.000 ↓ 0.0

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

11. 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=)

12.          

SubPlan (for GroupAggregate)

13. 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)
14.          

CTE projet_mv

15. 0.000 0.000 ↓ 0.0

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

16. 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))
17. 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)
18. 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))
19. 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)
20. 0.000 0.000 ↓ 0.0

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

21. 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=)

22. 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=)

23.          

SubPlan (for Result)

24. 0.000 0.000 ↓ 0.0

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

25. 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
26. 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
27. 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))
28. 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)
29. 0.000 0.000 ↓ 0.0

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

30. 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=)