explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fvLV

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 59,997.211 ↓ 3.7 74 1

Limit (cost=1,399,691.49..1,399,691.54 rows=20 width=1,105) (actual time=59,997.171..59,997.211 rows=74 loops=1)

  • Buffers: shared hit=2932685 read=1745
2. 0.314 59,997.187 ↓ 3.7 74 1

Sort (cost=1,399,691.49..1,399,691.54 rows=20 width=1,105) (actual time=59,997.170..59,997.187 rows=74 loops=1)

  • Sort Key: ((project0.name)::character varying(255)) COLLATE "en_US
  • Sort Method: quicksort Memory: 37kB
  • Buffers: shared hit=2932685 read=1745
3. 0.562 59,996.873 ↓ 3.7 74 1

Nested Loop Left Join (cost=0.00..1,399,691.06 rows=20 width=1,105) (actual time=1,047.180..59,996.873 rows=74 loops=1)

  • Join Filter: (project0.programid = program6.id)
  • Buffers: shared hit=2932682 read=1745
4. 0.156 11,475.769 ↓ 3.7 74 1

Nested Loop (cost=0.00..373,328.84 rows=20 width=73) (actual time=352.116..11,475.769 rows=74 loops=1)

  • Join Filter: (project0.projectstatuslabelid = projectstatuslabel7.id)
  • Rows Removed by Join Filter: 3
  • Buffers: shared hit=557692 read=693
5. 0.520 0.520 ↑ 1.0 1 1

Seq Scan on projectstatuslabel projectstatuslabel7 (cost=0.00..1.07 rows=1 width=16) (actual time=0.518..0.520 rows=1 loops=1)

  • Filter: (projectstatustype = 1)
  • Rows Removed by Filter: 5
  • Buffers: shared read=1
6. 1.054 11,475.093 ↓ 1.9 77 1

Seq Scan on project project0 (cost=0.00..373,327.26 rows=41 width=89) (actual time=209.874..11,475.093 rows=77 loops=1)

  • Filter: ((programid = 'faf506b2-6d9a-439e-93a4-7c3e8f495f6a'::uuid) AND ((alternatives: SubPlan 4 or hashed SubPlan 6) OR (istimeentryallowed AND (alternatives: SubPlan 8 or hashed SubPlan 10))))
  • Rows Removed by Filter: 132
  • Buffers: shared hit=557692 read=692
7.          

SubPlan (forSeq Scan)

8. 15.354 11,474.039 ↑ 30.0 1 79

Nested Loop Semi Join (cost=72.36..51,317.95 rows=30 width=0) (actual time=145.241..145.241 rows=1 loops=79)

  • Buffers: shared hit=557692 read=686
9. 53.957 53.957 ↑ 2.1 120 79

Index Scan using ixtkprojectid on task task8 (cost=0.29..91.36 rows=257 width=4) (actual time=0.181..0.683 rows=120 loops=79)

  • Index Cond: (projectid = project0.id)
  • Filter: (istimeentryallowed AND (NOT isclosed) AND (timeentrystartdate <= '2018-11-30'::date) AND (timeentryenddate >= '2018-11-16'::date))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=613 read=185
10. 928.467 11,404.728 ↓ 0.0 0 9,441

Hash Join (cost=72.07..259.24 rows=3,712 width=4) (actual time=1.208..1.208 rows=0 loops=9,441)

  • Hash Cond: (projectteammember9.id = taskteammemberassignment10.projectteammemberid)
  • Buffers: shared hit=557079 read=501
11. 10,218.150 10,221.354 ↑ 9.8 379 9,438

Seq Scan on projectteammember projectteammember9 (cost=4.53..170.75 rows=3,712 width=16) (actual time=0.002..1.083 rows=379 loops=9,438)

  • Filter: ((userid = 965) OR (hashed SubPlan 3))
  • Rows Removed by Filter: 6984
  • Buffers: shared hit=515456 read=58
12.          

SubPlan (forSeq Scan)

13. 0.003 3.204 ↑ 1.5 2 1

Nested Loop (cost=0.42..4.52 rows=3 width=4) (actual time=3.202..3.204 rows=2 loops=1)

  • Buffers: shared read=5
14. 2.248 2.248 ↑ 1.0 1 1

Index Scan using ixduuserid on departmentusers departmentusers12 (cost=0.28..2.29 rows=1 width=4) (actual time=2.248..2.248 rows=1 loops=1)

  • Index Cond: (userid = 965)
  • Buffers: shared read=3
15. 0.953 0.953 ↑ 1.5 2 1

Index Scan using ixdfhchildid on departmentflathierarchy departmentflathierarchy11 (cost=0.14..2.19 rows=3 width=8) (actual time=0.952..0.953 rows=2 loops=1)

  • Index Cond: (childid = departmentusers12.departmentid)
  • Buffers: shared read=2
16. 18.882 254.907 ↑ 238.0 2 9,441

Hash (cost=61.60..61.60 rows=476 width=20) (actual time=0.027..0.027 rows=2 loops=9,441)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=41623 read=443
17. 236.025 236.025 ↑ 238.0 2 9,441

Index Only Scan using uix2projectteammembertaskassignment on taskteammemberassignment taskteammemberassignment10 (cost=0.43..61.60 rows=476 width=20) (actual time=0.022..0.025 rows=2 loops=9,441)

  • Index Cond: (taskid = task8.id)
  • Heap Fetches: 14183
  • Buffers: shared hit=41623 read=443
18. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=86,106.91..87,822.62 rows=6,187 width=4) (never executed)

  • Hash Cond: (task8_1.id = taskteammemberassignment10_1.taskid)
19. 0.000 0.000 ↓ 0.0 0

Seq Scan on task task8_1 (cost=0.00..1,456.55 rows=52,608 width=8) (never executed)

  • Filter: (istimeentryallowed AND (NOT isclosed) AND (timeentrystartdate <= '2018-11-30'::date) AND (timeentryenddate >= '2018-11-16'::date))
20. 0.000 0.000 ↓ 0.0 0

Hash (cost=86,027.48..86,027.48 rows=6,355 width=4) (never executed)

21. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=85,963.93..86,027.48 rows=6,355 width=4) (never executed)

  • Group Key: taskteammemberassignment10_1.taskid
22. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=217.15..82,177.41 rows=1,514,607 width=4) (never executed)

  • Hash Cond: (taskteammemberassignment10_1.projectteammemberid = projectteammember9_1.id)
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on taskteammemberassignment taskteammemberassignment10_1 (cost=0.00..55,468.41 rows=3,025,541 width=20) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Hash (cost=170.75..170.75 rows=3,712 width=16) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Seq Scan on projectteammember projectteammember9_1 (cost=4.53..170.75 rows=3,712 width=16) (never executed)

  • Filter: ((userid = 965) OR (hashed SubPlan 5))
26.          

SubPlan (forSeq Scan)

27. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..4.52 rows=3 width=4) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Index Scan using ixduuserid on departmentusers departmentusers12_1 (cost=0.28..2.29 rows=1 width=4) (never executed)

  • Index Cond: (userid = 965)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using ixdfhchildid on departmentflathierarchy departmentflathierarchy11_1 (cost=0.14..2.19 rows=3 width=8) (never executed)

  • Index Cond: (childid = departmentusers12_1.departmentid)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using uix3ptm_projectrole on projectteammember projectteammember13 (cost=4.81..20.26 rows=18 width=0) (never executed)

  • Index Cond: (projectid = project0.id)
  • Filter: ((userid = 965) OR (hashed SubPlan 7))
31.          

SubPlan (forIndex Scan)

32. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..4.52 rows=3 width=4) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Index Scan using ixduuserid on departmentusers departmentusers15 (cost=0.28..2.29 rows=1 width=4) (never executed)

  • Index Cond: (userid = 965)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using ixdfhchildid on departmentflathierarchy departmentflathierarchy14 (cost=0.14..2.19 rows=3 width=8) (never executed)

  • Index Cond: (childid = departmentusers15.departmentid)
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on projectteammember projectteammember13_1 (cost=4.53..170.75 rows=3,712 width=4) (never executed)

  • Filter: ((userid = 965) OR (hashed SubPlan 9))
36.          

SubPlan (forSeq Scan)

37. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..4.52 rows=3 width=4) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Index Scan using ixduuserid on departmentusers departmentusers15_1 (cost=0.28..2.29 rows=1 width=4) (never executed)

  • Index Cond: (userid = 965)
39. 0.000 0.000 ↓ 0.0 0

Index Scan using ixdfhchildid on departmentflathierarchy departmentflathierarchy14_1 (cost=0.14..2.19 rows=3 width=8) (never executed)

  • Index Cond: (childid = departmentusers15_1.departmentid)
40. 0.143 1.998 ↑ 1.0 1 74

Materialize (cost=0.00..1.14 rows=1 width=1,048) (actual time=0.026..0.027 rows=1 loops=74)

  • Buffers: shared read=1
41. 1.855 1.855 ↑ 1.0 1 1

Seq Scan on program program6 (cost=0.00..1.14 rows=1 width=1,048) (actual time=1.854..1.855 rows=1 loops=1)

  • Filter: (id = 'faf506b2-6d9a-439e-93a4-7c3e8f495f6a'::uuid)
  • Rows Removed by Filter: 10
  • Buffers: shared read=1
42.          

SubPlan (forNested Loop Left Join)

43. 0.740 48,518.544 ↑ 1.0 1 74

Aggregate (cost=51,318.03..51,318.04 rows=1 width=0) (actual time=655.656..655.656 rows=1 loops=74)

  • Buffers: shared hit=2374990 read=1051
44. 51.472 48,517.804 ↑ 2.0 15 74

Nested Loop Semi Join (cost=72.36..51,317.95 rows=30 width=0) (actual time=152.166..655.646 rows=15 loops=74)

  • Buffers: shared hit=2374990 read=1051
45. 115.440 115.440 ↓ 2.2 553 74

Index Scan using ixtkprojectid on task task1 (cost=0.29..91.36 rows=257 width=4) (actual time=0.008..1.560 rows=553 loops=74)

  • Index Cond: (projectid = project0.id)
  • Filter: (istimeentryallowed AND (NOT isclosed) AND (timeentrystartdate <= '2018-11-30'::date) AND (timeentryenddate >= '2018-11-16'::date))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=2731 read=521
46. 4,116.376 48,350.892 ↓ 0.0 0 40,906

Hash Join (cost=72.07..259.24 rows=3,712 width=4) (actual time=1.182..1.182 rows=0 loops=40,906)

  • Hash Cond: (projectteammember2.id = taskteammemberassignment3.projectteammemberid)
  • Buffers: shared hit=2372259 read=530
47. 43,907.251 43,907.268 ↑ 9.9 376 40,882

Seq Scan on projectteammember projectteammember2 (cost=4.53..170.75 rows=3,712 width=16) (actual time=0.002..1.074 rows=376 loops=40,882)

  • Filter: ((userid = 965) OR (hashed SubPlan 1))
  • Rows Removed by Filter: 6869
  • Buffers: shared hit=2197374
48.          

SubPlan (forSeq Scan)

49. 0.005 0.017 ↑ 1.5 2 1

Nested Loop (cost=0.42..4.52 rows=3 width=4) (actual time=0.011..0.017 rows=2 loops=1)

  • Buffers: shared hit=5
50. 0.007 0.007 ↑ 1.0 1 1

Index Scan using ixduuserid on departmentusers departmentusers5 (cost=0.28..2.29 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (userid = 965)
  • Buffers: shared hit=3
51. 0.005 0.005 ↑ 1.5 2 1

Index Scan using ixdfhchildid on departmentflathierarchy departmentflathierarchy4 (cost=0.14..2.19 rows=3 width=8) (actual time=0.003..0.005 rows=2 loops=1)

  • Index Cond: (childid = departmentusers5.departmentid)
  • Buffers: shared hit=2
52. 81.812 327.248 ↑ 476.0 1 40,906

Hash (cost=61.60..61.60 rows=476 width=20) (actual time=0.008..0.008 rows=1 loops=40,906)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=174885 read=530
53. 245.436 245.436 ↑ 476.0 1 40,906

Index Only Scan using uix2projectteammembertaskassignment on taskteammemberassignment taskteammemberassignment3 (cost=0.43..61.60 rows=476 width=20) (actual time=0.005..0.006 rows=1 loops=40,906)

  • Index Cond: (taskid = task1.id)
  • Heap Fetches: 56923
  • Buffers: shared hit=174885 read=530