explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uJpj

Settings
# exclusive inclusive rows x rows loops node
1. 1,319.002 141,566.234 ↓ 1.0 30,535 1

Hash Join (cost=2,255.78..151,188,671.08 rows=30,412 width=142) (actual time=31.305..141,566.234 rows=30,535 loops=1)

  • Hash Cond: (pp.c_projectphase_id = ph.c_projectphase_id)
2. 21.867 60.269 ↓ 1.0 30,535 1

Hash Join (cost=2,195.87..3,222.27 rows=30,412 width=34) (actual time=24.280..60.269 rows=30,535 loops=1)

  • Hash Cond: (pu.c_project_plan_id = pp.c_project_plan_id)
3. 22.505 26.356 ↓ 1.0 30,535 1

HashAggregate (cost=863.30..1,167.42 rows=30,412 width=38) (actual time=12.119..26.356 rows=30,535 loops=1)

  • Group Key: pu.c_project_plan_id
4. 3.851 3.851 ↓ 1.0 30,547 1

Seq Scan on c_project_userplan pu (cost=0.00..787.24 rows=30,424 width=6) (actual time=0.004..3.851 rows=30,547 loops=1)

5. 5.688 12.046 ↓ 1.0 30,535 1

Hash (cost=953.92..953.92 rows=30,292 width=34) (actual time=12.046..12.046 rows=30,535 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2302kB
6. 6.358 6.358 ↓ 1.0 30,535 1

Seq Scan on c_project_plan pp (cost=0.00..953.92 rows=30,292 width=34) (actual time=0.005..6.358 rows=30,535 loops=1)

7. 0.054 0.778 ↓ 1.0 203 1

Hash (cost=57.41..57.41 rows=200 width=62) (actual time=0.778..0.778 rows=203 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
8. 0.082 0.724 ↓ 1.0 203 1

Hash Join (cost=37.68..57.41 rows=200 width=62) (actual time=0.486..0.724 rows=203 loops=1)

  • Hash Cond: (pl.c_project_id = pr.c_project_id)
9. 0.133 0.476 ↓ 1.0 203 1

Hash Join (cost=22.95..39.93 rows=200 width=50) (actual time=0.306..0.476 rows=203 loops=1)

  • Hash Cond: (bl.c_bpartner_location_id = pl.c_bpartner_location_id)
10. 0.052 0.052 ↑ 1.0 237 1

Seq Scan on c_bpartner_location bl (cost=0.00..13.45 rows=245 width=43) (actual time=0.002..0.052 rows=237 loops=1)

11. 0.054 0.291 ↓ 1.0 203 1

Hash (cost=20.45..20.45 rows=200 width=21) (actual time=0.291..0.291 rows=203 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
12. 0.098 0.237 ↓ 1.0 203 1

Hash Join (cost=9.70..20.45 rows=200 width=21) (actual time=0.129..0.237 rows=203 loops=1)

  • Hash Cond: (ph.c_projectline_id = pl.c_projectline_id)
13. 0.026 0.026 ↓ 1.0 203 1

Seq Scan on c_projectphase ph (cost=0.00..8.00 rows=200 width=14) (actual time=0.005..0.026 rows=203 loops=1)

14. 0.044 0.113 ↓ 1.0 214 1

Hash (cost=7.09..7.09 rows=209 width=21) (actual time=0.113..0.113 rows=214 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
15. 0.069 0.069 ↓ 1.0 214 1

Seq Scan on c_projectline pl (cost=0.00..7.09 rows=209 width=21) (actual time=0.003..0.069 rows=214 loops=1)

16. 0.031 0.166 ↓ 1.0 116 1

Hash (cost=13.32..13.32 rows=113 width=26) (actual time=0.166..0.166 rows=116 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
17. 0.054 0.135 ↓ 1.0 116 1

Hash Join (cost=6.63..13.32 rows=113 width=26) (actual time=0.076..0.135 rows=116 loops=1)

  • Hash Cond: (pr.c_bpartner_id = bp.c_bpartner_id)
18. 0.021 0.021 ↓ 1.0 116 1

Seq Scan on c_project pr (cost=0.00..5.13 rows=113 width=14) (actual time=0.005..0.021 rows=116 loops=1)

19. 0.031 0.060 ↑ 1.1 105 1

Hash (cost=5.17..5.17 rows=117 width=26) (actual time=0.060..0.060 rows=105 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
20. 0.029 0.029 ↑ 1.1 105 1

Seq Scan on c_bpartner bp (cost=0.00..5.17 rows=117 width=26) (actual time=0.003..0.029 rows=105 loops=1)

21.          

SubPlan (for Hash Join)

22. 0.000 70,016.755 ↓ 0.0 0 30,535

Limit (cost=2,481.32..2,481.33 rows=1 width=10) (actual time=2.293..2.293 rows=0 loops=30,535)

23. 30.535 70,016.755 ↓ 0.0 0 30,535

Sort (cost=2,481.32..2,481.33 rows=1 width=10) (actual time=2.293..2.293 rows=0 loops=30,535)

  • Sort Key: ut.created DESC
  • Sort Method: quicksort Memory: 25kB
24. 6,596.476 69,986.220 ↓ 0.0 0 30,535

Hash Join (cost=2,235.23..2,481.31 rows=1 width=10) (actual time=2.014..2.292 rows=0 loops=30,535)

  • Hash Cond: ((max(c_project_usertime.c_project_usertime_id)) = ut.c_project_usertime_id)
25. 10,797.383 10,808.474 ↑ 1.1 9,599 9,599

HashAggregate (cost=1,868.17..1,971.78 rows=10,361 width=38) (actual time=0.002..1.126 rows=9,599 loops=9,599)

  • Group Key: c_project_plan.c_project_plan_id
26. 3.230 11.091 ↑ 1.0 10,298 1

Hash Join (cost=1,332.57..1,816.36 rows=10,361 width=12) (actual time=6.866..11.091 rows=10,298 loops=1)

  • Hash Cond: (c_project_usertime.c_project_plan_id = c_project_plan.c_project_plan_id)
27. 1.028 1.028 ↑ 1.0 10,473 1

Seq Scan on c_project_usertime (cost=0.00..340.64 rows=10,545 width=12) (actual time=0.004..1.028 rows=10,473 loops=1)

  • Filter: ((usernote IS NULL) OR (isworkfinished IS NOT NULL))
  • Rows Removed by Filter: 96
28. 3.641 6.833 ↓ 1.0 30,535 1

Hash (cost=953.92..953.92 rows=30,292 width=6) (actual time=6.833..6.833 rows=30,535 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1419kB
29. 3.192 3.192 ↓ 1.0 30,535 1

Seq Scan on c_project_plan (cost=0.00..953.92 rows=30,292 width=6) (actual time=0.002..3.192 rows=30,535 loops=1)

30. 30.535 52,581.270 ↓ 0.0 0 30,535

Hash (cost=367.05..367.05 rows=1 width=16) (actual time=1.722..1.722 rows=0 loops=30,535)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
31. 52,550.735 52,550.735 ↓ 0.0 0 30,535

Seq Scan on c_project_usertime ut (cost=0.00..367.05 rows=1 width=16) (actual time=1.448..1.721 rows=0 loops=30,535)

  • Filter: (c_project_plan_id = pp.c_project_plan_id)
  • Rows Removed by Filter: 10569
32. 0.000 70,047.290 ↓ 0.0 0 30,535

Limit (cost=2,481.32..2,481.33 rows=1 width=13) (actual time=2.294..2.294 rows=0 loops=30,535)

33. 61.070 70,047.290 ↓ 0.0 0 30,535

Sort (cost=2,481.32..2,481.33 rows=1 width=13) (actual time=2.294..2.294 rows=0 loops=30,535)

  • Sort Key: ut_1.created DESC
  • Sort Method: quicksort Memory: 25kB
34. 6,711.664 69,986.220 ↓ 0.0 0 30,535

Hash Join (cost=2,235.23..2,481.31 rows=1 width=13) (actual time=2.015..2.292 rows=0 loops=30,535)

  • Hash Cond: ((max(c_project_usertime_1.c_project_usertime_id)) = ut_1.c_project_usertime_id)
35. 10,682.075 10,693.286 ↑ 1.1 9,599 9,599

HashAggregate (cost=1,868.17..1,971.78 rows=10,361 width=38) (actual time=0.002..1.114 rows=9,599 loops=9,599)

  • Group Key: c_project_plan_1.c_project_plan_id
36. 3.395 11.211 ↑ 1.0 10,298 1

Hash Join (cost=1,332.57..1,816.36 rows=10,361 width=12) (actual time=6.872..11.211 rows=10,298 loops=1)

  • Hash Cond: (c_project_usertime_1.c_project_plan_id = c_project_plan_1.c_project_plan_id)
37. 1.030 1.030 ↑ 1.0 10,473 1

Seq Scan on c_project_usertime c_project_usertime_1 (cost=0.00..340.64 rows=10,545 width=12) (actual time=0.002..1.030 rows=10,473 loops=1)

  • Filter: ((usernote IS NULL) OR (isworkfinished IS NOT NULL))
  • Rows Removed by Filter: 96
38. 3.619 6.786 ↓ 1.0 30,535 1

Hash (cost=953.92..953.92 rows=30,292 width=6) (actual time=6.786..6.786 rows=30,535 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1419kB
39. 3.167 3.167 ↓ 1.0 30,535 1

Seq Scan on c_project_plan c_project_plan_1 (cost=0.00..953.92 rows=30,292 width=6) (actual time=0.002..3.167 rows=30,535 loops=1)

40. 0.000 52,581.270 ↓ 0.0 0 30,535

Hash (cost=367.05..367.05 rows=1 width=19) (actual time=1.722..1.722 rows=0 loops=30,535)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
41. 52,581.270 52,581.270 ↓ 0.0 0 30,535

Seq Scan on c_project_usertime ut_1 (cost=0.00..367.05 rows=1 width=19) (actual time=1.447..1.722 rows=0 loops=30,535)

  • Filter: (c_project_plan_id = pp.c_project_plan_id)
  • Rows Removed by Filter: 10569
42. 30.535 122.140 ↑ 1.0 1 30,535

Aggregate (cost=8.31..8.32 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=30,535)

43. 91.605 91.605 ↑ 1.0 1 30,535

Index Only Scan using c_project_plan_id_idx_userplan on c_project_userplan (cost=0.29..8.30 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=30,535)

  • Index Cond: (c_project_plan_id = pp.c_project_plan_id)
  • Heap Fetches: 19311
Planning time : 3.708 ms
Execution time : 141,568.284 ms