explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pUOm

Settings
# exclusive inclusive rows x rows loops node
1. 2,484.422 19,384.025 ↓ 428.0 428 1

GroupAggregate (cost=35,540.50..225,914.29 rows=1 width=366) (actual time=214.677..19,384.025 rows=428 loops=1)

  • 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, li.type, 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. 1.440 133.293 ↓ 506.0 506 1

Sort (cost=35,540.50..35,540.50 rows=1 width=183) (actual time=132.928..133.293 rows=506 loops=1)

  • 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, li.type, 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.date_echeance_piece
  • Sort Method: quicksort Memory: 159kB
3. 3.777 131.853 ↓ 506.0 506 1

Nested Loop Left Join (cost=1,342.48..35,540.49 rows=1 width=183) (actual time=67.505..131.853 rows=506 loops=1)

  • Join Filter: ((fsfb2b.fk_dwh_dim_creance)::text = (dimcre.pk_creance)::text)
  • Rows Removed by Join Filter: 35403
4. 0.133 119.474 ↓ 506.0 506 1

Nested Loop Left Join (cost=1,169.29..35,315.58 rows=1 width=153) (actual time=66.472..119.474 rows=506 loops=1)

5. 0.307 92.523 ↓ 506.0 506 1

Nested Loop (cost=1,000.42..35,129.61 rows=1 width=126) (actual time=66.443..92.523 rows=506 loops=1)

6. 1.379 87.617 ↓ 255.5 511 1

Nested Loop (cost=1,000.00..35,113.43 rows=2 width=135) (actual time=66.410..87.617 rows=511 loops=1)

  • Join Filter: ((dimcre.fk_dwh_dim_donneur_ordre)::text = (dimdo.pk_donneur_ordre)::text)
  • Rows Removed by Join Filter: 9790
7. 0.000 85.727 ↑ 1.2 511 1

Gather (cost=1,000.00..34,868.57 rows=592 width=130) (actual time=66.382..85.727 rows=511 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 109.823 109.823 ↑ 1.5 170 3

Parallel Seq Scan on dwh_dim_creance dimcre (cost=0.00..33,809.38 rows=247 width=130) (actual time=59.742..109.823 rows=170 loops=3)

  • Filter: (is_b2b AND ((source)::text = 'BORA'::text) 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 >= 1) 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))
  • Rows Removed by Filter: 163949
9. 0.497 0.511 ↓ 1.5 20 511

Materialize (cost=0.00..129.45 rows=13 width=47) (actual time=0.000..0.001 rows=20 loops=511)

10. 0.014 0.014 ↓ 1.6 21 1

Seq Scan on dwh_dim_donneur_ordre dimdo (cost=0.00..129.39 rows=13 width=47) (actual time=0.009..0.014 rows=21 loops=1)

  • Filter: ((source)::text = 'BORA'::text)
  • Rows Removed by Filter: 1
11. 4.599 4.599 ↑ 1.0 1 511

Index Scan using pk_dwh_dim_debiteur_morale on dwh_dim_debiteur_morale dimdebmo (cost=0.42..8.09 rows=1 width=45) (actual time=0.009..0.009 rows=1 loops=511)

  • Index Cond: ((pk_debiteur_morale)::text = (dimcre.fk_dwh_dim_debiteur_morale)::text)
  • Filter: ((source)::text = 'BORA'::text)
12. 0.480 26.818 ↓ 0.0 0 506

Nested Loop (cost=168.87..185.96 rows=1 width=73) (actual time=0.031..0.053 rows=0 loops=506)

13. 11.066 25.806 ↓ 0.0 0 506

Hash Join (cost=168.46..183.35 rows=1 width=96) (actual time=0.029..0.051 rows=0 loops=506)

  • Hash Cond: (((facb2b_1.fk_dwh_dim_creance)::text = (facb2b.fk_dwh_dim_creance)::text) AND ((max(facb2b_1.date_aux_horo)) = facb2b.date_aux_horo))
14. 11.236 11.704 ↑ 1.0 541 133

HashAggregate (cost=146.16..151.57 rows=541 width=54) (actual time=0.007..0.088 rows=541 loops=133)

  • Group Key: facb2b_1.fk_dwh_dim_creance
15. 0.468 0.468 ↑ 1.0 1,744 1

Seq Scan on dwh_fait_suivi_facture_b2b facb2b_1 (cost=0.00..137.44 rows=1,744 width=54) (actual time=0.005..0.468 rows=1,744 loops=1)

16. 0.000 3.036 ↑ 3.0 1 506

Hash (cost=22.26..22.26 rows=3 width=104) (actual time=0.006..0.006 rows=1 loops=506)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
17. 3.036 3.036 ↑ 3.0 1 506

Index Scan using fk_dwh_fait_suivi_facture_b2b_dwh_dim_creance on dwh_fait_suivi_facture_b2b facb2b (cost=0.28..22.26 rows=3 width=104) (actual time=0.005..0.006 rows=1 loops=506)

  • Index Cond: ((fk_dwh_dim_creance)::text = (dimcre.pk_creance)::text)
18. 0.532 0.532 ↑ 1.0 1 133

Index Scan using pk_dwh_dim_collaborateur on dwh_dim_collaborateur dimcolab (cost=0.41..2.44 rows=1 width=43) (actual time=0.004..0.004 rows=1 loops=133)

  • Index Cond: ((pk_collaborateur)::text = (facb2b.fk_dwh_dim_collaborateur)::text)
  • Filter: ((source)::text = 'BORA'::text)
19. 7.645 8.602 ↑ 22.7 70 506

HashAggregate (cost=173.19..189.09 rows=1,590 width=62) (actual time=0.002..0.017 rows=70 loops=506)

  • Group Key: fsfb2b.fk_dwh_dim_creance, li.type
20. 0.130 0.957 ↑ 14.5 110 1

Hash Join (cost=5.79..165.24 rows=1,590 width=62) (actual time=0.082..0.957 rows=110 loops=1)

  • Hash Cond: ((fsfb2b.fk_dwh_dim_litige)::text = (li.pk_litige)::text)
21. 0.759 0.759 ↑ 1.0 1,744 1

Seq Scan on dwh_fait_suivi_facture_b2b fsfb2b (cost=0.00..137.44 rows=1,744 width=92) (actual time=0.006..0.759 rows=1,744 loops=1)

22. 0.014 0.068 ↑ 1.0 62 1

Hash (cost=5.02..5.02 rows=62 width=62) (actual time=0.068..0.068 rows=62 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
23. 0.054 0.054 ↑ 1.0 62 1

Seq Scan on dwh_dim_litige li (cost=0.00..5.02 rows=62 width=62) (actual time=0.006..0.054 rows=62 loops=1)

  • Filter: (((statut)::text = 'OPEN'::text) AND ((source)::text = 'BORA'::text))
  • Rows Removed by Filter: 6
24.          

SubPlan (forGroupAggregate)

25. 1.012 2,335.696 ↑ 1.0 1 506

Aggregate (cost=23,760.35..23,760.36 rows=1 width=8) (actual time=4.616..4.616 rows=1 loops=506)

26. 1.518 2,334.684 ↓ 0.0 0 506

Nested Loop (cost=0.00..23,760.34 rows=4 width=0) (actual time=4.614..4.614 rows=0 loops=506)

27. 2,333.166 2,333.166 ↓ 0.0 0 506

Seq Scan on dwh_fait_suivi_action suiv_sub_7 (cost=0.00..1,541.90 rows=1 width=178) (actual time=4.611..4.611 rows=0 loops=506)

  • Filter: (((numero_facture_b2b)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
  • Rows Removed by Filter: 29034
28. 0.000 0.000 ↓ 0.0 0

Seq Scan on dwh_dim_creance c_sub_7 (cost=0.00..22,218.40 rows=4 width=7) (never executed)

  • Filter: (((numero_piece)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
29. 1.012 2,328.106 ↑ 1.0 1 506

Aggregate (cost=23,760.35..23,760.36 rows=1 width=8) (actual time=4.601..4.601 rows=1 loops=506)

30. 1.012 2,327.094 ↓ 0.0 0 506

Nested Loop (cost=0.00..23,760.34 rows=4 width=0) (actual time=4.599..4.599 rows=0 loops=506)

31. 2,326.082 2,326.082 ↓ 0.0 0 506

Seq Scan on dwh_fait_suivi_action suiv_sub_6 (cost=0.00..1,541.90 rows=1 width=178) (actual time=4.597..4.597 rows=0 loops=506)

  • Filter: (((numero_facture_b2b)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
  • Rows Removed by Filter: 29034
32. 0.000 0.000 ↓ 0.0 0

Seq Scan on dwh_dim_creance c_sub_6 (cost=0.00..22,218.40 rows=4 width=7) (never executed)

  • Filter: (((numero_piece)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
33. 0.506 2,336.202 ↑ 1.0 1 506

Aggregate (cost=23,760.35..23,760.36 rows=1 width=8) (actual time=4.617..4.617 rows=1 loops=506)

34. 1.518 2,335.696 ↓ 0.0 0 506

Nested Loop (cost=0.00..23,760.34 rows=4 width=0) (actual time=4.616..4.616 rows=0 loops=506)

35. 2,334.178 2,334.178 ↓ 0.0 0 506

Seq Scan on dwh_fait_suivi_action suiv_sub_5 (cost=0.00..1,541.90 rows=1 width=178) (actual time=4.613..4.613 rows=0 loops=506)

  • Filter: (((numero_facture_b2b)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
  • Rows Removed by Filter: 29034
36. 0.000 0.000 ↓ 0.0 0

Seq Scan on dwh_dim_creance c_sub_5 (cost=0.00..22,218.40 rows=4 width=7) (never executed)

  • Filter: (((numero_piece)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
37. 1.012 2,327.600 ↑ 1.0 1 506

Aggregate (cost=23,760.35..23,760.36 rows=1 width=8) (actual time=4.599..4.600 rows=1 loops=506)

38. 1.518 2,326.588 ↓ 0.0 0 506

Nested Loop (cost=0.00..23,760.34 rows=4 width=0) (actual time=4.598..4.598 rows=0 loops=506)

39. 2,325.070 2,325.070 ↓ 0.0 0 506

Seq Scan on dwh_fait_suivi_action suiv_sub_4 (cost=0.00..1,541.90 rows=1 width=178) (actual time=4.595..4.595 rows=0 loops=506)

  • Filter: (((numero_facture_b2b)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
  • Rows Removed by Filter: 29034
40. 0.000 0.000 ↓ 0.0 0

Seq Scan on dwh_dim_creance c_sub_4 (cost=0.00..22,218.40 rows=4 width=7) (never executed)

  • Filter: (((numero_piece)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
41. 1.012 2,486.484 ↑ 1.0 1 506

Aggregate (cost=23,833.00..23,833.01 rows=1 width=8) (actual time=4.914..4.914 rows=1 loops=506)

42. 1.012 2,485.472 ↓ 0.0 0 506

Nested Loop (cost=0.00..23,832.99 rows=4 width=0) (actual time=4.912..4.912 rows=0 loops=506)

43. 2,484.460 2,484.460 ↓ 0.0 0 506

Seq Scan on dwh_fait_suivi_action suiv_sub_3 (cost=0.00..1,614.55 rows=1 width=178) (actual time=4.910..4.910 rows=0 loops=506)

  • Filter: ((date_action <= dimcre.date_paiement) AND ((numero_facture_b2b)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
  • Rows Removed by Filter: 29034
44. 0.000 0.000 ↓ 0.0 0

Seq Scan on dwh_dim_creance c_sub_3 (cost=0.00..22,218.40 rows=4 width=7) (never executed)

  • Filter: (((numero_piece)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
45. 0.506 2,481.424 ↑ 1.0 1 506

Aggregate (cost=23,833.00..23,833.01 rows=1 width=8) (actual time=4.904..4.904 rows=1 loops=506)

46. 1.518 2,480.918 ↓ 0.0 0 506

Nested Loop (cost=0.00..23,832.99 rows=4 width=0) (actual time=4.903..4.903 rows=0 loops=506)

47. 2,479.400 2,479.400 ↓ 0.0 0 506

Seq Scan on dwh_fait_suivi_action suiv_sub_2 (cost=0.00..1,614.55 rows=1 width=178) (actual time=4.900..4.900 rows=0 loops=506)

  • Filter: ((date_action <= dimcre.date_paiement) AND ((numero_facture_b2b)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
  • Rows Removed by Filter: 29034
48. 0.000 0.000 ↓ 0.0 0

Seq Scan on dwh_dim_creance c_sub_2 (cost=0.00..22,218.40 rows=4 width=7) (never executed)

  • Filter: (((numero_piece)::text = (dimcre.numero_piece)::text) AND ((source)::text = 'BORA'::text))
49. 1.012 2,470.798 ↑ 1.0 1 506

Aggregate (cost=23,833.00..23,833.01 rows=1 width=8) (actual time=4.883..4.883 rows=1 loops=506)

50. 2,469.786 2,469.786 ↓ 0.0 0 506

Nested Loop (cost=0.00..23,832.99 rows=4 width=0) (actual time=4.881..4.881 rows=0 loops=506)