explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fGjp

Settings
# exclusive inclusive rows x rows loops node
1. 241.051 53,218.304 ↑ 3.8 8 1

Nested Loop Semi Join (cost=1.13..78,427,510.89 rows=30 width=122) (actual time=52,802.876..53,218.304 rows=8 loops=1)

  • Join Filter: (departments0.id = departmentusers1.departmentid)
  • Rows Removed by Join Filter: 3,406,703
  • Buffers: shared hit=59,734,472
2. 0.037 0.037 ↑ 1.0 64 1

Seq Scan on departments departments0 (cost=0.00..1.64 rows=64 width=9) (actual time=0.004..0.037 rows=64 loops=1)

  • Buffers: shared hit=1
3. 196.473 52,977.216 ↑ 27.6 53,230 64

Materialize (cost=1.13..77,682,495.60 rows=1,467,954 width=4) (actual time=2.314..827.769 rows=53,230 loops=64)

  • Buffers: shared hit=59,734,471
4. 480.459 52,780.743 ↑ 26.8 54,862 1

Nested Loop (cost=1.13..77,675,155.83 rows=1,467,954 width=4) (actual time=148.103..52,780.743 rows=54,862 loops=1)

  • Buffers: shared hit=59,734,471
5. 267.838 790.896 ↑ 1.0 1,514,982 1

Nested Loop (cost=0.71..74,702.62 rows=1,514,982 width=20) (actual time=0.058..790.896 rows=1,514,982 loops=1)

  • Buffers: shared hit=52,238
6. 1.498 1.498 ↑ 1.0 1,534 1

Index Only Scan using departmentusers_pkey on departmentusers departmentusers1 (cost=0.28..39.29 rows=1,534 width=8) (actual time=0.038..1.498 rows=1,534 loops=1)

  • Heap Fetches: 1,534
  • Buffers: shared hit=76
7. 521.560 521.560 ↑ 1.1 988 1,534

Index Scan using ixata2userid on dm_attendancetimeallocation_facts ttendancetimeallocation_facts2 (cost=0.43..37.64 rows=1,103 width=20) (actual time=0.011..0.340 rows=988 loops=1,534)

  • Index Cond: (userid = departmentusers1.userid)
  • Buffers: shared hit=52,162
8. 6,200.959 51,509.388 ↓ 0.0 0 1,514,982

Index Scan using dm_attendancetimeallocation_facts_pkey on dm_attendancetimeallocation_facts ttendancetimeallocation_facts3 (cost=0.43..51.22 rows=1 width=16) (actual time=0.034..0.034 rows=0 loops=1,514,982)

  • Index Cond: (id = ttendancetimeallocation_facts2.id)
  • Filter: ((userid = 3,766) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (SubPlan 3) OR (SubPlan 4) OR (SubPlan 5) OR (SubPlan 6))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=59,682,233
9.          

SubPlan (for Index Scan)

10. 1,504.829 1,504.829 ↓ 0.0 0 1,504,829

Index Scan using project_pkey on project project4 (cost=0.28..2.30 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1,504,829)

  • Index Cond: (id = ttendancetimeallocation_facts3.projectid)
  • Filter: (projectleaderapproverid = 3,766)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4,221,965
11. 0.000 0.000 ↓ 0.0 0

Index Scan using ixprjprojectleaderapproverid on project project4_1 (cost=0.28..34.32 rows=46 width=4) (never executed)

  • Index Cond: (projectleaderapproverid = 3,766)
12. 0.000 7,300.600 ↓ 0.0 0 1,460,120

Nested Loop (cost=0.70..9.07 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=1,460,120)

  • Join Filter: (tsapprovalrequestkeyvalue5.parentid = tsapprovalrequestkeyvalue9.id)
  • Buffers: shared hit=7,316,418
13. 1,460.120 7,300.600 ↓ 0.0 0 1,460,120

Nested Loop (cost=0.56..6.90 rows=1 width=48) (actual time=0.005..0.005 rows=0 loops=1,460,120)

  • Join Filter: (tsapprovalrequest8.id = tsapprovalrequestkeyvalue5.tsapprovalrequestid)
  • Buffers: shared hit=7,316,418
14. 0.000 5,840.480 ↓ 0.0 0 1,460,120

Nested Loop (cost=0.42..4.73 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1,460,120)

  • Join Filter: (timesheetapprovalnodes7.id = tsapprovalrequest8.nodeid)
  • Buffers: shared hit=7,316,418
15. 0.262 5,840.480 ↓ 0.0 0 1,460,120

Nested Loop (cost=0.42..3.66 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1,460,120)

  • Buffers: shared hit=7,316,244
16. 2,920.240 2,920.240 ↑ 1.0 1 1,460,120

Index Only Scan using timesheet_pkey on timesheet timesheet6 (cost=0.42..2.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,460,120)

  • Index Cond: (id = ttendancetimeallocation_facts3.timesheetid)
  • Heap Fetches: 1,459,989
  • Buffers: shared hit=5,856,255
17. 2,919.978 2,919.978 ↓ 0.0 0 1,459,989

Seq Scan on timesheetapprovalnodes timesheetapprovalnodes7 (cost=0.00..1.21 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1,459,989)

  • Filter: (timesheetid = ttendancetimeallocation_facts3.timesheetid)
  • Rows Removed by Filter: 17
  • Buffers: shared hit=1,459,989
18. 0.174 0.174 ↓ 0.0 0 174

Seq Scan on tsapprovalrequest tsapprovalrequest8 (cost=0.00..1.06 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=174)

  • Filter: (userid = 3,766)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=174
19. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtsarkvkey on tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue5 (cost=0.14..2.16 rows=1 width=52) (never executed)

  • Index Cond: (upper(key) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-DATA-ACCESS-LEVELS'::text)
20. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtsarkvparentid on tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue9 (cost=0.14..2.16 rows=1 width=16) (never executed)

  • Index Cond: (parentid IS NULL)
21. 0.000 14,601.200 ↓ 0.0 0 1,460,120

Nested Loop (cost=3.87..34.63 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=1,460,120)

  • Buffers: shared hit=17,375,146
22. 1,460.120 14,601.200 ↓ 0.0 0 1,460,120

Nested Loop (cost=3.44..34.10 rows=1 width=16) (actual time=0.010..0.010 rows=0 loops=1,460,120)

  • Buffers: shared hit=17,375,146
23. 0.000 13,141.080 ↓ 0.0 0 1,460,120

Nested Loop (cost=3.00..30.93 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=1,460,120)

  • Buffers: shared hit=17,375,146
24. 0.000 13,141.080 ↓ 0.0 0 1,460,120

Nested Loop (cost=2.57..30.41 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=1,460,120)

  • Buffers: shared hit=17,179,523
25. 1,004.189 13,141.080 ↓ 0.0 0 1,460,120

Nested Loop (cost=2.14..29.30 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=1,460,120)

  • Buffers: shared hit=16,918,301
26. 0.000 11,680.960 ↓ 0.0 0 1,460,120

Nested Loop (cost=1.71..28.08 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=1,460,120)

  • Buffers: shared hit=16,542,195
27. 770.851 11,680.960 ↓ 0.0 0 1,460,120

Nested Loop (cost=1.27..25.83 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=1,460,120)

  • Buffers: shared hit=16,280,733
28. 1,460.513 8,760.720 ↓ 0.0 0 1,460,120

Nested Loop (cost=0.84..7.95 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=1,460,120)

  • Buffers: shared hit=15,586,520
29. 2,920.240 2,920.240 ↑ 1.0 1 1,460,120

Index Only Scan using timesheet_pkey on timesheet timesheet11 (cost=0.42..2.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,460,120)

  • Index Cond: (id = ttendancetimeallocation_facts3.timesheetid)
  • Heap Fetches: 1,459,989
  • Buffers: shared hit=5,856,255
30. 4,379.967 4,379.967 ↓ 0.0 0 1,459,989

Index Scan using ixtah2timesheetid on timesheetapprovalhistory timesheetapprovalhistory12 (cost=0.42..5.50 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1,459,989)

  • Index Cond: (timesheetid = ttendancetimeallocation_facts3.timesheetid)
  • Filter: (userid = 3,766)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=9,730,265
31. 2,149.389 2,149.389 ↑ 1.0 1 65,133

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue13 (cost=0.43..17.87 rows=1 width=32) (actual time=0.033..0.033 rows=1 loops=65,133)

  • Index Cond: (timesheetapprovalhistoryid = timesheetapprovalhistory12.id)
  • Filter: ((upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-NODES'::text) AND (upper(uri) = 'URN:REPLICON-TENANT:DC60C82A7C2D4BACB1BD8B082DC08C5B:USER:3,766'::text))
  • Rows Removed by Filter: 16
  • Buffers: shared hit=694,213
32. 195.399 195.399 ↑ 1.0 1 65,133

Index Scan using timesheetapprovalhistorykeyvalue_pkey on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue14 (cost=0.43..2.25 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=65,133)

  • Index Cond: (id = sheetapprovalhistorykeyvalue13.parentid)
  • Filter: (upper(uri) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-EXPECTED-APPROVAL-AGENT-USER'::text)
  • Buffers: shared hit=261,462
33. 455.931 455.931 ↑ 1.0 1 65,133

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue15 (cost=0.43..1.20 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=65,133)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue14.parentid)
  • Filter: (upper(uri) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-METADATA'::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=376,106
34. 130.266 130.266 ↑ 2.0 1 65,133

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue16 (cost=0.43..1.09 rows=2 width=32) (actual time=0.002..0.002 rows=1 loops=65,133)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue15.id)
  • Buffers: shared hit=261,222
35. 130.266 130.266 ↓ 0.0 0 65,133

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue17 (cost=0.43..0.51 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=65,133)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue16.id)
  • Filter: (upper(uri) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-DATA-ACCESS-LEVELS'::text)
  • Buffers: shared hit=195,623
36. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue18 (cost=0.43..3.15 rows=2 width=32) (never executed)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue17.id)
37. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue10 (cost=0.43..0.50 rows=2 width=93) (never executed)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue18.id)
38. 1,460.120 7,300.600 ↓ 0.0 0 1,460,120

Nested Loop (cost=0.70..9.07 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=1,460,120)

  • Join Filter: (tsapprovalrequestkeyvalue19.parentid = tsapprovalrequestkeyvalue23.id)
  • Buffers: shared hit=7,316,418
39. 0.000 5,840.480 ↓ 0.0 0 1,460,120

Nested Loop (cost=0.56..6.90 rows=1 width=48) (actual time=0.004..0.004 rows=0 loops=1,460,120)

  • Join Filter: (tsapprovalrequest22.id = tsapprovalrequestkeyvalue19.tsapprovalrequestid)
  • Buffers: shared hit=7,316,418
40. 0.000 5,840.480 ↓ 0.0 0 1,460,120

Nested Loop (cost=0.42..4.73 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1,460,120)

  • Join Filter: (timesheetapprovalnodes21.id = tsapprovalrequest22.nodeid)
  • Buffers: shared hit=7,316,418
41. 0.262 5,840.480 ↓ 0.0 0 1,460,120

Nested Loop (cost=0.42..3.66 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1,460,120)

  • Buffers: shared hit=7,316,244
42. 2,920.240 2,920.240 ↑ 1.0 1 1,460,120

Index Only Scan using timesheet_pkey on timesheet timesheet20 (cost=0.42..2.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,460,120)

  • Index Cond: (id = ttendancetimeallocation_facts3.timesheetid)
  • Heap Fetches: 1,459,989
  • Buffers: shared hit=5,856,255
43. 2,919.978 2,919.978 ↓ 0.0 0 1,459,989

Seq Scan on timesheetapprovalnodes timesheetapprovalnodes21 (cost=0.00..1.21 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1,459,989)

  • Filter: (timesheetid = ttendancetimeallocation_facts3.timesheetid)
  • Rows Removed by Filter: 17
  • Buffers: shared hit=1,459,989
44. 0.174 0.174 ↓ 0.0 0 174

Seq Scan on tsapprovalrequest tsapprovalrequest22 (cost=0.00..1.06 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=174)

  • Filter: (userid = 3,766)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=174
45. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtsarkvkey on tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue19 (cost=0.14..2.16 rows=1 width=52) (never executed)

  • Index Cond: (upper(key) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-ACCESSIBLE-OBJECTS'::text)
46. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtsarkvparentid on tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue23 (cost=0.14..2.16 rows=1 width=16) (never executed)

  • Index Cond: (parentid IS NULL)
47. 1,460.120 14,601.200 ↓ 0.0 0 1,460,120

Nested Loop (cost=3.87..34.94 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=1,460,120)

  • Buffers: shared hit=17,375,146
48. 0.000 13,141.080 ↓ 0.0 0 1,460,120

Nested Loop (cost=3.44..34.41 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=1,460,120)

  • Buffers: shared hit=17,375,146
49. 0.000 13,141.080 ↓ 0.0 0 1,460,120

Nested Loop (cost=3.00..30.93 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=1,460,120)

  • Buffers: shared hit=17,375,146
50. 0.000 13,141.080 ↓ 0.0 0 1,460,120

Nested Loop (cost=2.57..30.41 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=1,460,120)

  • Buffers: shared hit=17,179,523
51. 1,004.189 13,141.080 ↓ 0.0 0 1,460,120

Nested Loop (cost=2.14..29.30 rows=1 width=16) (actual time=0.008..0.009 rows=0 loops=1,460,120)

  • Buffers: shared hit=16,918,301
52. 0.000 11,680.960 ↓ 0.0 0 1,460,120

Nested Loop (cost=1.71..28.08 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=1,460,120)

  • Buffers: shared hit=16,542,195
53. 770.851 11,680.960 ↓ 0.0 0 1,460,120

Nested Loop (cost=1.27..25.83 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=1,460,120)

  • Buffers: shared hit=16,280,733
54. 1,460.513 8,760.720 ↓ 0.0 0 1,460,120

Nested Loop (cost=0.84..7.95 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=1,460,120)

  • Buffers: shared hit=15,586,520
55. 2,920.240 2,920.240 ↑ 1.0 1 1,460,120

Index Only Scan using timesheet_pkey on timesheet timesheet25 (cost=0.42..2.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,460,120)

  • Index Cond: (id = ttendancetimeallocation_facts3.timesheetid)
  • Heap Fetches: 1,459,989
  • Buffers: shared hit=5,856,255
56. 4,379.967 4,379.967 ↓ 0.0 0 1,459,989

Index Scan using ixtah2timesheetid on timesheetapprovalhistory timesheetapprovalhistory26 (cost=0.42..5.50 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1,459,989)

  • Index Cond: (timesheetid = ttendancetimeallocation_facts3.timesheetid)
  • Filter: (userid = 3,766)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=9,730,265
57. 2,149.389 2,149.389 ↑ 1.0 1 65,133

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue27 (cost=0.43..17.87 rows=1 width=32) (actual time=0.033..0.033 rows=1 loops=65,133)

  • Index Cond: (timesheetapprovalhistoryid = timesheetapprovalhistory26.id)
  • Filter: ((upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-NODES'::text) AND (upper(uri) = 'URN:REPLICON-TENANT:DC60C82A7C2D4BACB1BD8B082DC08C5B:USER:3,766'::text))
  • Rows Removed by Filter: 16
  • Buffers: shared hit=694,213
58. 195.399 195.399 ↑ 1.0 1 65,133

Index Scan using timesheetapprovalhistorykeyvalue_pkey on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue28 (cost=0.43..2.25 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=65,133)

  • Index Cond: (id = sheetapprovalhistorykeyvalue27.parentid)
  • Filter: (upper(uri) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-EXPECTED-APPROVAL-AGENT-USER'::text)
  • Buffers: shared hit=261,462
59. 455.931 455.931 ↑ 1.0 1 65,133

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue29 (cost=0.43..1.20 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=65,133)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue28.parentid)
  • Filter: (upper(uri) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-METADATA'::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=376,106
60. 130.266 130.266 ↑ 2.0 1 65,133

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue30 (cost=0.43..1.09 rows=2 width=32) (actual time=0.002..0.002 rows=1 loops=65,133)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue29.id)
  • Buffers: shared hit=261,222
61. 130.266 130.266 ↓ 0.0 0 65,133

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue31 (cost=0.43..0.51 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=65,133)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue30.id)
  • Filter: (upper(uri) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-ACCESSIBLE-OBJECTS'::text)
  • Buffers: shared hit=195,623
62. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue32 (cost=0.43..3.46 rows=2 width=32) (never executed)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue31.id)
63. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue24 (cost=0.43..0.50 rows=2 width=93) (never executed)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue32.id)
Planning time : 10.019 ms
Execution time : 53,219.283 ms