explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8jVh

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

GroupAggregate (cost=3,441,207.08..259,074,689,867.36 rows=1,304 width=366) (actual rows= loops=)

  • Group Key: dimdebmo.libelle, dimdo.libelle, dimdo.libelle_regroupement_client, dimdebmo.secteur_activite, ((((dimcolab.nom)::text || ' '::text) || (dimcolab.prenom)::text)), dimcre.date_integration, dimcre.date_creation, facb2b.statut, subquerylitige.typelitige, dimcre.type, (CASE WHEN (dimcre.montant_versee <> '0'::numeric) THEN 1 ELSE 0 END), dimcre.date_paiement, ((CASE WHEN ((dimcre.montant_versee <> '0'::numeric) AND ((dimcre.date_paiement - dimcre.date_echeance_piece) <= 0)) THEN (dimcre.date_paiement - dimcre.date_echeance_piece) WHEN ((dimcre.montant_versee <> '0'::numeric) AND ((dimcre.date_paiement - dimcre.date_echeance_piece) > 0)) THEN (dimcre.date_paiement - dimcre.date_echeance_piece) WHEN ((CURRENT_DATE - dimcre.date_echeance_piece) <= 0) THEN (CURRENT_DATE - dimcre.date_echeance_piece) ELSE (CURRENT_DATE - dimcre.date_echeance_piece) END > 0)), dimcre.source, dimcre.date_echeance_piece
2. 0.000 0.000 ↓ 0.0

Sort (cost=3,441,207.08..3,441,210.34 rows=1,304 width=167) (actual rows= loops=)

  • Sort Key: dimdebmo.libelle, dimdo.libelle, dimdo.libelle_regroupement_client, dimdebmo.secteur_activite, ((((dimcolab.nom)::text || ' '::text) || (dimcolab.prenom)::text)), dimcre.date_integration, dimcre.date_creation, facb2b.statut, subquerylitige.typelitige, dimcre.type, (CASE WHEN (dimcre.montant_versee <> '0'::numeric) THEN 1 ELSE 0 END), dimcre.date_paiement, ((CASE WHEN ((dimcre.montant_versee <> '0'::numeric) AND ((dimcre.date_paiement - dimcre.date_echeance_piece) <= 0)) THEN (dimcre.date_paiement - dimcre.date_echeance_piece) WHEN ((dimcre.montant_versee <> '0'::numeric) AND ((dimcre.date_paiement - dimcre.date_echeance_piece) > 0)) THEN (dimcre.date_paiement - dimcre.date_echeance_piece) WHEN ((CURRENT_DATE - dimcre.date_echeance_piece) <= 0) THEN (CURRENT_DATE - dimcre.date_echeance_piece) ELSE (CURRENT_DATE - dimcre.date_echeance_piece) END > 0)), dimcre.source, dimcre.date_echeance_piece
3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,359.96..3,441,139.61 rows=1,304 width=167) (actual rows= loops=)

  • Hash Cond: ((dimcre.pk_creance)::text = (subquerylitige.fk_dwh_dim_creance)::text)
4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,255.26..3,440,576.87 rows=1,304 width=130) (actual rows= loops=)

  • Join Filter: ((facb2b.fk_dwh_dim_creance)::text = (dimcre.pk_creance)::text)
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,000.71..3,440,075.23 rows=1,304 width=110) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.42..3,435,222.26 rows=1,306 width=79) (actual rows= loops=)

  • Workers Planned: 2
7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..3,434,091.66 rows=544 width=79) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on dwh_dim_creance dimcre (cost=0.00..3,429,763.83 rows=545 width=64) (actual rows= loops=)

  • Filter: (is_b2b AND (CASE WHEN ((montant_versee <> '0'::numeric) AND ((date_paiement - date_echeance_piece) <= 0)) THEN (date_paiement - date_echeance_piece) WHEN ((montant_versee <> '0'::numeric) AND ((date_paiement - date_echeance_piece) > 0)) THEN (date_paiement - date_echeance_piece) WHEN ((CURRENT_DATE - date_echeance_piece) <= 0) THEN (CURRENT_DATE - date_echeance_piece) ELSE (CURRENT_DATE - date_echeance_piece) END <= 30))
9. 0.000 0.000 ↓ 0.0

Index Scan using pk_dwh_dim_debiteur_morale on dwh_dim_debiteur_morale dimdebmo (cost=0.42..7.94 rows=1 width=34) (actual rows= loops=)

  • Index Cond: ((pk_debiteur_morale)::text = (dimcre.fk_dwh_dim_debiteur_morale)::text)
10. 0.000 0.000 ↓ 0.0

Index Scan using pk_dwh_dim_donneur_ordre on dwh_dim_donneur_ordre dimdo (cost=0.28..3.72 rows=1 width=49) (actual rows= loops=)

  • Index Cond: ((pk_donneur_ordre)::text = (dimcre.fk_dwh_dim_donneur_ordre)::text)
11. 0.000 0.000 ↓ 0.0

Materialize (cost=254.56..482.08 rows=1 width=66) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=254.56..482.08 rows=1 width=66) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash Join (cost=254.14..477.91 rows=1 width=96) (actual rows= loops=)

  • Hash Cond: (((facb2b.fk_dwh_dim_creance)::text = (facb2b_1.fk_dwh_dim_creance)::text) AND (facb2b.date_aux_horo = (max(facb2b_1.date_aux_horo))))
14. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_fait_suivi_facture_b2b facb2b (cost=0.00..211.11 rows=2,411 width=104) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash (cost=240.87..240.87 rows=885 width=54) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

HashAggregate (cost=223.17..232.02 rows=885 width=54) (actual rows= loops=)

  • Group Key: facb2b_1.fk_dwh_dim_creance
17. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_fait_suivi_facture_b2b facb2b_1 (cost=0.00..211.11 rows=2,411 width=54) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Index Scan using pk_dwh_dim_collaborateur on dwh_dim_collaborateur dimcolab (cost=0.41..4.17 rows=1 width=34) (actual rows= loops=)

  • Index Cond: ((pk_collaborateur)::text = (facb2b.fk_dwh_dim_collaborateur)::text)
19. 0.000 0.000 ↓ 0.0

Hash (cost=74.56..74.56 rows=2,411 width=62) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Subquery Scan on subquerylitige (cost=26.34..74.56 rows=2,411 width=62) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

HashAggregate (cost=26.34..50.45 rows=2,411 width=62) (actual rows= loops=)

  • Group Key: fsfb2b.fk_dwh_dim_creance, li.type
22. 0.000 0.000 ↓ 0.0

Merge Join (cost=2.98..14.29 rows=2,411 width=62) (actual rows= loops=)

  • Merge Cond: ((fsfb2b.fk_dwh_dim_litige)::text = (li.pk_litige)::text)
23. 0.000 0.000 ↓ 0.0

Index Scan using fk_dwh_fait_suivi_facture_b2b_dwh_dim_litige on dwh_fait_suivi_facture_b2b fsfb2b (cost=0.28..886.55 rows=2,411 width=92) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Sort (cost=2.33..2.36 rows=11 width=62) (actual rows= loops=)

  • Sort Key: li.pk_litige
25. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_dim_litige li (cost=0.00..2.14 rows=11 width=62) (actual rows= loops=)

  • Filter: ((statut)::text = 'OPEN'::text)
26.          

SubPlan (forGroupAggregate)

27. 0.000 0.000 ↓ 0.0

Aggregate (cost=5,935,342.55..5,935,342.56 rows=1 width=8) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..5,935,337.69 rows=1,944 width=0) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_dim_creance c_sub_7 (cost=0.00..3,086,390.36 rows=1 width=7) (actual rows= loops=)

  • Filter: (((numero_piece)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
30. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_fait_suivi_action suiv_sub_7 (cost=0.00..2,848,927.89 rows=1,944 width=1) (actual rows= loops=)

  • Filter: (((numero_facture_b2b)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
31. 0.000 0.000 ↓ 0.0

Aggregate (cost=5,935,342.55..5,935,342.56 rows=1 width=8) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..5,935,337.69 rows=1,944 width=0) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_dim_creance c_sub_6 (cost=0.00..3,086,390.36 rows=1 width=7) (actual rows= loops=)

  • Filter: (((numero_piece)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
34. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_fait_suivi_action suiv_sub_6 (cost=0.00..2,848,927.89 rows=1,944 width=1) (actual rows= loops=)

  • Filter: (((numero_facture_b2b)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
35. 0.000 0.000 ↓ 0.0

Aggregate (cost=5,935,342.55..5,935,342.56 rows=1 width=8) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..5,935,337.69 rows=1,944 width=0) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_dim_creance c_sub_5 (cost=0.00..3,086,390.36 rows=1 width=7) (actual rows= loops=)

  • Filter: (((numero_piece)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
38. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_fait_suivi_action suiv_sub_5 (cost=0.00..2,848,927.89 rows=1,944 width=1) (actual rows= loops=)

  • Filter: (((numero_facture_b2b)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
39. 0.000 0.000 ↓ 0.0

Aggregate (cost=5,935,342.55..5,935,342.56 rows=1 width=8) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..5,935,337.69 rows=1,944 width=0) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_dim_creance c_sub_4 (cost=0.00..3,086,390.36 rows=1 width=7) (actual rows= loops=)

  • Filter: (((numero_piece)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
42. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_fait_suivi_action suiv_sub_4 (cost=0.00..2,848,927.89 rows=1,944 width=1) (actual rows= loops=)

  • Filter: (((numero_facture_b2b)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
43. 0.000 0.000 ↓ 0.0

Aggregate (cost=43,733,224.98..43,733,224.99 rows=1 width=8) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..37,409,048.48 rows=2,529,670,599 width=0) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_fait_suivi_action suiv_sub_3 (cost=0.00..2,848,927.89 rows=810,013 width=1) (actual rows= loops=)

  • Filter: ((date_action <= dimcre.date_paiement) AND ((numero_facture_b2b)::text = (dimcre.numero_piece)::text))
46. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..2,939,245.92 rows=3,123 width=7) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_dim_creance c_sub_3 (cost=0.00..2,939,230.30 rows=3,123 width=7) (actual rows= loops=)

  • Filter: ((numero_piece)::text = (dimcre.numero_piece)::text)
48. 0.000 0.000 ↓ 0.0

Aggregate (cost=43,733,224.98..43,733,224.99 rows=1 width=8) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..37,409,048.48 rows=2,529,670,599 width=0) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_fait_suivi_action suiv_sub_2 (cost=0.00..2,848,927.89 rows=810,013 width=1) (actual rows= loops=)

  • Filter: ((date_action <= dimcre.date_paiement) AND ((numero_facture_b2b)::text = (dimcre.numero_piece)::text))
51. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..2,939,245.92 rows=3,123 width=7) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_dim_creance c_sub_2 (cost=0.00..2,939,230.30 rows=3,123 width=7) (actual rows= loops=)

  • Filter: ((numero_piece)::text = (dimcre.numero_piece)::text)
53. 0.000 0.000 ↓ 0.0

Aggregate (cost=43,733,224.98..43,733,224.99 rows=1 width=8) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..37,409,048.48 rows=2,529,670,599 width=0) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_fait_suivi_action suiv_sub_1 (cost=0.00..2,848,927.89 rows=810,013 width=1) (actual rows= loops=)

  • Filter: ((date_action <= dimcre.date_paiement) AND ((numero_facture_b2b)::text = (dimcre.numero_piece)::text))
56. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..2,939,245.92 rows=3,123 width=7) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_dim_creance c_sub_1 (cost=0.00..2,939,230.30 rows=3,123 width=7) (actual rows= loops=)

  • Filter: ((numero_piece)::text = (dimcre.numero_piece)::text)
58. 0.000 0.000 ↓ 0.0

Aggregate (cost=43,733,224.98..43,733,224.99 rows=1 width=8) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..37,409,048.48 rows=2,529,670,599 width=0) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_fait_suivi_action suiv_sub (cost=0.00..2,848,927.89 rows=810,013 width=1) (actual rows= loops=)

  • Filter: ((date_action <= dimcre.date_paiement) AND ((numero_facture_b2b)::text = (dimcre.numero_piece)::text))
61. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..2,939,245.92 rows=3,123 width=7) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Seq Scan on dwh_dim_creance c_sub (cost=0.00..2,939,230.30 rows=3,123 width=7) (actual rows= loops=)

  • Filter: ((numero_piece)::text = (dimcre.numero_piece)::text)