explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P4p0

Settings
# exclusive inclusive rows x rows loops node
1. 0.346 70,225.823 ↓ 0.0 0 1

Nested Loop Semi Join (cost=2,781.48..3,531.23 rows=1 width=116) (actual time=70,225.823..70,225.823 rows=0 loops=1)

  • Join Filter: (res.id = this_.id)
  • Rows Removed by Join Filter: 4905
2. 0.795 21.847 ↑ 1.0 1,635 1

Sort (cost=1,792.62..1,796.81 rows=1,675 width=116) (actual time=21.703..21.847 rows=1,635 loops=1)

  • Sort Key: res.id
  • Sort Method: quicksort Memory: 416kB
3. 0.287 21.052 ↑ 1.0 1,635 1

Hash Left Join (cost=702.04..1,702.93 rows=1,675 width=116) (actual time=3.698..21.052 rows=1,635 loops=1)

  • Hash Cond: (ass.role_type_id = rolet.id)
4. 0.339 20.732 ↑ 1.0 1,635 1

Hash Left Join (cost=696.95..1,674.80 rows=1,675 width=94) (actual time=3.661..20.732 rows=1,635 loops=1)

  • Hash Cond: (plh.project_id = proj.id)
5. 0.568 19.953 ↑ 1.0 1,635 1

Hash Left Join (cost=611.49..1,566.31 rows=1,675 width=52) (actual time=3.213..19.953 rows=1,635 loops=1)

  • Hash Cond: (ass.placeholder_id = plh.id)
6. 0.441 16.794 ↑ 1.0 1,635 1

Hash Join (cost=137.23..1,069.02 rows=1,675 width=44) (actual time=0.572..16.794 rows=1,635 loops=1)

  • Hash Cond: (ass.resource_id = res.id)
7. 15.816 15.816 ↑ 1.0 1,635 1

Seq Scan on assignment ass (cost=0.00..908.76 rows=1,675 width=44) (actual time=0.020..15.816 rows=1,635 loops=1)

  • Filter: ((id > 0) AND (((start_date <= ('now'::cstring)::date) AND ((end_date IS NULL) OR (end_date >= ('now'::cstring)::date))) OR (start_date IS NULL)))
  • Rows Removed by Filter: 18938
8. 0.244 0.537 ↑ 1.0 2,642 1

Hash (cost=103.77..103.77 rows=2,677 width=8) (actual time=0.537..0.537 rows=2,642 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 136kB
9. 0.293 0.293 ↑ 1.0 2,642 1

Seq Scan on resource res (cost=0.00..103.77 rows=2,677 width=8) (actual time=0.003..0.293 rows=2,642 loops=1)

10. 1.249 2.591 ↑ 1.0 11,567 1

Hash (cost=329.67..329.67 rows=11,567 width=16) (actual time=2.591..2.591 rows=11,567 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 671kB
11. 1.342 1.342 ↑ 1.0 11,567 1

Seq Scan on placeholder plh (cost=0.00..329.67 rows=11,567 width=16) (actual time=0.002..1.342 rows=11,567 loops=1)

12. 0.202 0.440 ↓ 1.0 1,266 1

Hash (cost=69.65..69.65 rows=1,265 width=50) (actual time=0.440..0.440 rows=1,266 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 119kB
13. 0.238 0.238 ↓ 1.0 1,266 1

Seq Scan on project proj (cost=0.00..69.65 rows=1,265 width=50) (actual time=0.002..0.238 rows=1,266 loops=1)

14. 0.014 0.033 ↑ 1.0 93 1

Hash (cost=3.93..3.93 rows=93 width=30) (actual time=0.033..0.033 rows=93 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
15. 0.019 0.019 ↑ 1.0 93 1

Seq Scan on role_type rolet (cost=0.00..3.93 rows=93 width=30) (actual time=0.002..0.019 rows=93 loops=1)

16. 0.135 70,203.630 ↓ 3.0 3 1,635

Materialize (cost=988.86..1,692.55 rows=1 width=8) (actual time=2.065..42.938 rows=3 loops=1,635)

17. 0.002 70,203.495 ↓ 3.0 3 1

Subquery Scan on this_ (cost=988.86..1,692.54 rows=1 width=8) (actual time=3,375.595..70,203.495 rows=3 loops=1)

18. 0.014 70,203.493 ↓ 3.0 3 1

Nested Loop Left Join (cost=988.86..1,692.53 rows=1 width=16) (actual time=3,375.594..70,203.493 rows=3 loops=1)

  • Join Filter: (nwo.resource_id = res_1.id)
  • Rows Removed by Join Filter: 21
19.          

CTE nwo

20. 0.006 14.126 ↑ 7.3 7 1

HashAggregate (cost=981.72..982.23 rows=51 width=24) (actual time=14.125..14.126 rows=7 loops=1)

  • Group Key: ca.resource_id, ca.start_date, ca.end_date
21. 0.022 14.120 ↑ 9.1 7 1

Hash Join (cost=91.80..981.24 rows=64 width=24) (actual time=7.638..14.120 rows=7 loops=1)

  • Hash Cond: (ca.contract_id = c.id)
22. 13.803 13.803 ↑ 1.2 326 1

Seq Scan on contract_assignment ca (cost=0.00..887.35 rows=386 width=32) (actual time=5.899..13.803 rows=326 loops=1)

  • Filter: ((('now'::cstring)::date >= start_date) AND (('now'::cstring)::date <= end_date))
  • Rows Removed by Filter: 19648
23. 0.001 0.295 ↑ 340.0 1 1

Hash (cost=87.55..87.55 rows=340 width=8) (actual time=0.295..0.295 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.146 0.294 ↑ 340.0 1 1

Hash Join (cost=1.09..87.55 rows=340 width=8) (actual time=0.252..0.294 rows=1 loops=1)

  • Hash Cond: (c.contract_type_id = ct_1.id)
25. 0.143 0.143 ↑ 1.0 2,041 1

Seq Scan on contract c (cost=0.00..75.41 rows=2,041 width=16) (actual time=0.001..0.143 rows=2,041 loops=1)

26. 0.002 0.005 ↑ 1.0 1 1

Hash (cost=1.08..1.08 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on contract_type ct_1 (cost=0.00..1.08 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)

  • Filter: (processing_type = 2)
  • Rows Removed by Filter: 5
28. 19.689 70,189.112 ↓ 3.0 3 1

Nested Loop (cost=6.63..708.39 rows=1 width=16) (actual time=3,361.365..70,189.112 rows=3 loops=1)

  • Join Filter: ((resource_employment_data.resource_id = res_1.id) AND (((resource_employment_data.start_date <= ('now'::cstring)::date) AND (resloc.start_date = (SubPlan 4)) AND (resorg.start_date = (SubPlan 6))) OR ((resource_emplo (...)
  • Rows Removed by Join Filter: 80597
29. 3.146 42.443 ↓ 1,612.0 1,612 1

Hash Join (cost=4.92..32.44 rows=1 width=12) (actual time=34.125..42.443 rows=1,612 loops=1)

  • Hash Cond: (resource_employment_data.office_id = ofc.id)
30. 39.282 39.282 ↓ 537.3 1,612 1

Function Scan on resource_employment_data (cost=0.26..27.75 rows=3 width=20) (actual time=34.103..39.282 rows=1,612 loops=1)

  • Filter: ((employment_status_calculated = 2) AND ((start_date <= ('now'::cstring)::date) OR (start_date > ('now'::cstring)::date)))
  • Rows Removed by Filter: 1030
31. 0.006 0.015 ↑ 1.0 38 1

Hash (cost=4.19..4.19 rows=38 width=8) (actual time=0.015..0.015 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
32. 0.009 0.009 ↑ 1.0 38 1

Seq Scan on office ofc (cost=0.00..4.19 rows=38 width=8) (actual time=0.004..0.009 rows=38 loops=1)

  • Filter: (id = ANY ('{1,1175123,1246673,2,867022,601098,512515,603909,135244,491081,270165,856735,207638,207640,907542,361562,103665,103664,103667,103666,103669,105397,212727,103668,103671,105399,105398,103670,103 (...)
33. 125.736 70,126.836 ↓ 50.0 50 1,612

Nested Loop Left Join (cost=1.71..667.19 rows=1 width=199) (actual time=0.871..43.503 rows=50 loops=1,612)

34. 22.568 1,974.700 ↓ 50.0 50 1,612

Nested Loop (cost=1.41..111.88 rows=1 width=40) (actual time=0.025..1.225 rows=50 loops=1,612)

35. 19.344 1,871.532 ↓ 50.0 50 1,612

Nested Loop (cost=1.27..109.51 rows=1 width=40) (actual time=0.024..1.161 rows=50 loops=1,612)

36. 0.000 1,771.588 ↓ 50.0 50 1,612

Nested Loop (cost=1.12..108.24 rows=1 width=48) (actual time=0.023..1.099 rows=50 loops=1,612)

37. 0.000 1,616.836 ↓ 50.0 50 1,612

Nested Loop (cost=0.84..107.86 rows=1 width=40) (actual time=0.021..1.003 rows=50 loops=1,612)

38. 40.300 436.852 ↓ 40.8 367 1,612

Nested Loop (cost=0.56..103.72 rows=9 width=20) (actual time=0.007..0.271 rows=367 loops=1,612)

39. 9.672 9.672 ↑ 1.0 4 1,612

Index Only Scan using organization_chart_pkey on organization_chart org (cost=0.28..7.55 rows=4 width=8) (actual time=0.001..0.006 rows=4 loops=1,612)

  • Index Cond: (id = ANY ('{6866,9254,9256,9257}'::bigint[]))
  • Heap Fetches: 6448
40. 386.880 386.880 ↓ 4.4 92 6,448

Index Scan using indx_ro_on_organization_id on resource_organization resorg (cost=0.28..23.83 rows=21 width=28) (actual time=0.004..0.060 rows=92 loops=6,448)

  • Index Cond: (organization_id = org.id)
  • Filter: (position_id = 1)
  • Rows Removed by Filter: 172
41. 1,183.208 1,183.208 ↓ 0.0 0 591,604

Index Scan using indx_rl_on_resource_id on resource_location resloc (cost=0.28..0.45 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=591,604)

  • Index Cond: (resource_id = resorg.resource_id)
  • Filter: (location_id = 2)
  • Rows Removed by Filter: 3
42. 161.200 161.200 ↑ 1.0 1 80,600

Index Only Scan using resource_pkey on resource res_1 (cost=0.28..0.37 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=80,600)

  • Index Cond: (id = resorg.resource_id)
  • Heap Fetches: 80600
43. 80.600 80.600 ↑ 1.0 1 80,600

Index Only Scan using position_pkey on "position" pos (cost=0.14..1.26 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=80,600)

  • Index Cond: (id = 1)
  • Heap Fetches: 0
44. 80.600 80.600 ↑ 1.0 1 80,600

Index Scan using location_pkey on location loc (cost=0.15..2.37 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=80,600)

  • Index Cond: (id = 2)
45. 68,026.400 68,026.400 ↓ 0.0 0 80,600

Index Scan using unique_time_off on time_off tmf (cost=0.30..555.29 rows=1 width=167) (actual time=0.844..0.844 rows=0 loops=80,600)

  • Index Cond: ((start_date <= ('now'::cstring)::date) AND (end_date >= ('now'::cstring)::date) AND (resource_id = res_1.id))
46.          

SubPlan (for Nested Loop)

47. 0.011 0.066 ↑ 1.0 1 11

Result (cost=1.76..1.77 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=11)

48.          

Initplan (for Result)

49. 0.022 0.055 ↑ 1.0 1 11

Limit (cost=0.29..1.76 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=11)

50. 0.033 0.033 ↑ 3.0 1 11

Index Only Scan Backward using resource_location__unique__resource_id__start_date on resource_location (cost=0.29..4.71 rows=3 width=4) (actual time=0.003..0.003 rows=1 loops=11)

  • Index Cond: ((resource_id = res_1.id) AND (start_date IS NOT NULL) AND (start_date <= ('now'::cstring)::date))
  • Heap Fetches: 11
51. 0.006 0.078 ↑ 1.0 1 6

Result (cost=1.91..1.92 rows=1 width=0) (actual time=0.013..0.013 rows=1 loops=6)

52.          

Initplan (for Result)

53. 0.054 0.072 ↑ 1.0 1 6

Limit (cost=0.29..1.91 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=6)

54. 0.018 0.018 ↑ 2.0 1 6

Index Only Scan Backward using resource_organization__unique__resource_id__start_date on resource_organization (cost=0.29..3.54 rows=2 width=4) (actual time=0.003..0.003 rows=1 loops=6)

  • Index Cond: ((resource_id = res_1.id) AND (start_date IS NOT NULL) AND (start_date <= ('now'::cstring)::date))
  • Heap Fetches: 6
55. 0.000 0.000 ↓ 0.0 0

Result (cost=2.50..2.51 rows=1 width=0) (never executed)

56.          

Initplan (for Result)

57. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.28..2.50 rows=1 width=4) (never executed)

58. 0.000 0.000 ↓ 0.0 0

Index Only Scan Backward using resource_location__unique__resource_id__start_date on resource_location resource_location_1 (cost=0.28..2.50 rows=1 width=4) (never executed)

  • Index Cond: ((resource_id = res_1.id) AND (start_date IS NOT NULL) AND (start_date <= resource_employment_data.start_date))
  • Heap Fetches: 0
59. 0.000 0.000 ↓ 0.0 0

Result (cost=2.50..2.51 rows=1 width=0) (never executed)

60.          

Initplan (for Result)

61. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.28..2.50 rows=1 width=4) (never executed)

62. 0.000 0.000 ↓ 0.0 0

Index Only Scan Backward using resource_organization__unique__resource_id__start_date on resource_organization resource_organization_1 (cost=0.28..2.50 rows=1 width=4) (never executed)

  • Index Cond: ((resource_id = res_1.id) AND (start_date IS NOT NULL) AND (start_date <= resource_employment_data.start_date))
  • Heap Fetches: 0
63. 14.130 14.130 ↑ 7.3 7 3

CTE Scan on nwo (cost=0.00..1.02 rows=51 width=8) (actual time=4.709..4.710 rows=7 loops=3)

64.          

SubPlan (for Nested Loop Left Join)

65. 0.237 0.237 ↑ 1.0 1 3

Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.079..0.079 rows=1 loops=3)

Planning time : 4.935 ms
Execution time : 70,226.548 ms