explain.depesz.com

PostgreSQL's explain analyze made readable

Result: npBi

Settings
# exclusive inclusive rows x rows loops node
1. 2.119 41,893.049 ↓ 164.2 657 1

HashAggregate (cost=27,312.96..27,313.00 rows=4 width=66) (actual time=41,892.946..41,893.049 rows=657 loops=1)

2. 2.535 41,890.930 ↓ 167.0 668 1

Nested Loop Left Join (cost=2.28..27,312.90 rows=4 width=66) (actual time=38,193.063..41,890.930 rows=668 loops=1)

3. 0.478 38,138.239 ↓ 164.2 657 1

Subquery Scan on data (cost=1.72..27,286.48 rows=4 width=58) (actual time=38,137.142..38,138.239 rows=657 loops=1)

4. 9.295 38,137.761 ↓ 164.2 657 1

HashSetOp Except All (cost=1.72..27,286.44 rows=4 width=24) (actual time=38,137.140..38,137.761 rows=657 loops=1)

5. 1.610 38,128.466 ↓ 846.0 4,230 1

Append (cost=1.72..27,286.38 rows=5 width=24) (actual time=54.771..38,128.466 rows=4,230 loops=1)

6. 0.228 171.519 ↓ 292.0 1,168 1

Result (cost=1.72..246.41 rows=4 width=17) (actual time=54.771..171.519 rows=1,168 loops=1)

7. 0.123 171.291 ↓ 292.0 1,168 1

Append (cost=1.72..246.41 rows=4 width=17) (actual time=54.770..171.291 rows=1,168 loops=1)

8. 0.155 159.934 ↓ 582.0 582 1

Subquery Scan on *SELECT* 1 (cost=1.72..61.60 rows=1 width=17) (actual time=54.770..159.934 rows=582 loops=1)

9. 0.776 159.779 ↓ 582.0 582 1

Nested Loop (cost=1.72..61.59 rows=1 width=17) (actual time=54.768..159.779 rows=582 loops=1)

10.          

Initplan (for Nested Loop)

11. 0.033 0.033 ↑ 1.0 1 1

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.033..0.033 rows=1 loops=1)

12. 0.342 153.732 ↓ 582.0 582 1

Nested Loop (cost=1.40..55.24 rows=1 width=17) (actual time=50.669..153.732 rows=582 loops=1)

13. 1.164 91.094 ↓ 149.8 599 1

Merge Join (cost=0.98..43.60 rows=4 width=24) (actual time=50.333..91.094 rows=599 loops=1)

  • Merge Cond: ((pso.code_point)::text = (jour.code_point)::text)
14. 66.014 66.014 ↑ 1.1 600 1

Index Only Scan using point_sacoche_obligatoire_organisation_id_key on point_sacoche_obligatoire pso (cost=0.41..30.07 rows=666 width=17) (actual time=26.862..66.014 rows=600 loops=1)

  • Index Cond: (organisation_id = 4413)
  • Heap Fetches: 105
15. 23.916 23.916 ↓ 2.7 650 1

Index Only Scan using jour_pkey on jour (cost=0.42..11.23 rows=240 width=11) (actual time=23.455..23.916 rows=650 loops=1)

  • Index Cond: ((organisation_id = 4413) AND (joursemaine = $6))
  • Heap Fetches: 0
16. 62.296 62.296 ↑ 1.0 1 599

Index Only Scan using point_codepoint_org_actif on point p (cost=0.42..2.90 rows=1 width=11) (actual time=0.103..0.104 rows=1 loops=599)

  • Index Cond: ((code_point = (pso.code_point)::text) AND (organisation_id = 4413) AND (actif = 1))
  • Heap Fetches: 12
17. 5.238 5.238 ↑ 1.0 1 582

Index Scan using organisation_client_organisation_id_pkey on organisation_client oc (cost=0.28..6.30 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=582)

  • Index Cond: (organisation_id = 4413)
  • Filter: (actif = 1)
18. 0.000 0.885 ↓ 31.0 31 1

Nested Loop (cost=1.72..61.59 rows=1 width=17) (actual time=0.283..0.885 rows=31 loops=1)

19.          

Initplan (for Nested Loop)

20. 0.025 0.025 ↑ 1.0 1 1

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.024..0.025 rows=1 loops=1)

21. 0.029 0.798 ↓ 31.0 31 1

Nested Loop (cost=1.40..55.24 rows=1 width=17) (actual time=0.273..0.798 rows=31 loops=1)

22. 0.263 0.463 ↓ 8.5 34 1

Merge Join (cost=0.98..43.60 rows=4 width=24) (actual time=0.251..0.463 rows=34 loops=1)

  • Merge Cond: ((pso_1.code_point)::text = (jour_1.code_point)::text)
23. 0.147 0.147 ↑ 1.1 600 1

Index Only Scan using point_sacoche_obligatoire_organisation_id_key on point_sacoche_obligatoire pso_1 (cost=0.41..30.07 rows=666 width=17) (actual time=0.016..0.147 rows=600 loops=1)

  • Index Cond: (organisation_id = 4413)
  • Heap Fetches: 105
24. 0.053 0.053 ↑ 3.0 81 1

Index Only Scan using jour_pkey on jour jour_1 (cost=0.42..11.23 rows=240 width=11) (actual time=0.043..0.053 rows=81 loops=1)

  • Index Cond: ((organisation_id = 4413) AND (joursemaine = $4))
  • Heap Fetches: 0
25. 0.306 0.306 ↑ 1.0 1 34

Index Only Scan using point_codepoint_org_actif on point p_1 (cost=0.42..2.90 rows=1 width=11) (actual time=0.009..0.009 rows=1 loops=34)

  • Index Cond: ((code_point = (pso_1.code_point)::text) AND (organisation_id = 4413) AND (actif = 1))
  • Heap Fetches: 0
26. 0.062 0.062 ↑ 1.0 1 31

Index Scan using organisation_client_organisation_id_pkey on organisation_client oc_1 (cost=0.28..6.30 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=31)

  • Index Cond: (organisation_id = 4413)
  • Filter: (actif = 1)
27. 0.003 0.765 ↓ 2.0 2 1

Nested Loop (cost=1.72..61.59 rows=1 width=17) (actual time=0.615..0.765 rows=2 loops=1)

28.          

Initplan (for Nested Loop)

29. 0.008 0.008 ↑ 1.0 1 1

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)

30. 0.008 0.746 ↓ 2.0 2 1

Nested Loop (cost=1.40..55.24 rows=1 width=17) (actual time=0.600..0.746 rows=2 loops=1)

31. 0.213 0.718 ↑ 2.0 2 1

Merge Join (cost=0.98..43.60 rows=4 width=24) (actual time=0.580..0.718 rows=2 loops=1)

  • Merge Cond: ((pso_2.code_point)::text = (jour_2.code_point)::text)
32. 0.149 0.149 ↑ 1.1 600 1

Index Only Scan using point_sacoche_obligatoire_organisation_id_key on point_sacoche_obligatoire pso_2 (cost=0.41..30.07 rows=666 width=17) (actual time=0.014..0.149 rows=600 loops=1)

  • Index Cond: (organisation_id = 4413)
  • Heap Fetches: 105
33. 0.356 0.356 ↑ 9.2 26 1

Index Only Scan using jour_pkey on jour jour_2 (cost=0.42..11.23 rows=240 width=11) (actual time=0.352..0.356 rows=26 loops=1)

  • Index Cond: ((organisation_id = 4413) AND (joursemaine = $2))
  • Heap Fetches: 0
34. 0.020 0.020 ↑ 1.0 1 2

Index Only Scan using point_codepoint_org_actif on point p_2 (cost=0.42..2.90 rows=1 width=11) (actual time=0.010..0.010 rows=1 loops=2)

  • Index Cond: ((code_point = (pso_2.code_point)::text) AND (organisation_id = 4413) AND (actif = 1))
  • Heap Fetches: 0
35. 0.008 0.008 ↑ 1.0 1 2

Index Scan using organisation_client_organisation_id_pkey on organisation_client oc_2 (cost=0.28..6.30 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2)

  • Index Cond: (organisation_id = 4413)
  • Filter: (actif = 1)
36. 0.285 9.584 ↓ 553.0 553 1

Nested Loop (cost=1.72..61.59 rows=1 width=17) (actual time=0.353..9.584 rows=553 loops=1)

37.          

Initplan (for Nested Loop)

38. 0.009 0.009 ↑ 1.0 1 1

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)

39. 0.474 8.184 ↓ 553.0 553 1

Nested Loop (cost=1.40..55.24 rows=1 width=17) (actual time=0.340..8.184 rows=553 loops=1)

40. 0.900 1.473 ↓ 141.8 567 1

Merge Join (cost=0.98..43.60 rows=4 width=24) (actual time=0.318..1.473 rows=567 loops=1)

  • Merge Cond: ((pso_3.code_point)::text = (jour_3.code_point)::text)
41. 0.148 0.148 ↑ 1.1 600 1

Index Only Scan using point_sacoche_obligatoire_organisation_id_key on point_sacoche_obligatoire pso_3 (cost=0.41..30.07 rows=666 width=17) (actual time=0.010..0.148 rows=600 loops=1)

  • Index Cond: (organisation_id = 4413)
  • Heap Fetches: 105
42. 0.425 0.425 ↓ 2.5 595 1

Index Only Scan using jour_pkey on jour jour_3 (cost=0.42..11.23 rows=240 width=11) (actual time=0.305..0.425 rows=595 loops=1)

  • Index Cond: ((organisation_id = 4413) AND (joursemaine = $0))
  • Heap Fetches: 0
43. 6.237 6.237 ↑ 1.0 1 567

Index Only Scan using point_codepoint_org_actif on point p_3 (cost=0.42..2.90 rows=1 width=11) (actual time=0.011..0.011 rows=1 loops=567)

  • Index Cond: ((code_point = (pso_3.code_point)::text) AND (organisation_id = 4413) AND (actif = 1))
  • Heap Fetches: 12
44. 1.106 1.106 ↑ 1.0 1 553

Index Scan using organisation_client_organisation_id_pkey on organisation_client oc_3 (cost=0.28..6.30 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=553)

  • Index Cond: (organisation_id = 4413)
  • Filter: (actif = 1)
45. 2.427 37,955.337 ↓ 3,062.0 3,062 1

Subquery Scan on *SELECT* 5 (cost=0.57..27,039.97 rows=1 width=50) (actual time=19.864..37,955.337 rows=3,062 loops=1)

46. 37,952.910 37,952.910 ↓ 3,062.0 3,062 1

Index Scan using detail_dateref_mvtorgid on detail d (cost=0.57..27,039.96 rows=1 width=50) (actual time=19.863..37,952.910 rows=3,062 loops=1)

  • Index Cond: ((date_reference >= (('now'::cstring)::date - '3 days'::interval)) AND (mvt_organisation_id = 4413))
  • Filter: ((organisation_id = 4413) AND (((code_barre_code_exp)::text = (mvt_code_point)::text) OR ((code_barre_code_dest)::text = (mvt_code_point)::text)))
  • Rows Removed by Filter: 59
47. 3,750.156 3,750.156 ↓ 0.0 0 657

Index Scan using mouvement_organisation_id_operation_id_code_point_date_ref_key on mouvement m (cost=0.57..6.59 rows=1 width=27) (actual time=5.598..5.708 rows=0 loops=657)

  • Index Cond: ((organisation_id = data.organisation_id) AND (organisation_id = 4413) AND (operation_id = 1) AND ((code_point)::text = (data.code_point)::text) AND (date_reference = data.date_reference))
Total runtime : 41,893.600 ms