explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xi4Q

Settings
# exclusive inclusive rows x rows loops node
1. 5.586 44,845.047 ↑ 3.8 8 1

Merge Semi Join (cost=1.34..73,103,255.80 rows=30 width=122) (actual time=130.563..44,845.047 rows=8 loops=1)

  • Merge Cond: (departments0.id = departmentusers1.departmentid)
  • Buffers: shared hit=49,251,893
2. 0.033 0.033 ↑ 1.6 39 1

Index Scan using departments_pkey on departments departments0 (cost=0.14..4.10 rows=64 width=9) (actual time=0.018..0.033 rows=39 loops=1)

  • Buffers: shared hit=2
3. 1,651.788 44,839.428 ↑ 26.8 54,862 1

Nested Loop (cost=1.13..73,084,902.12 rows=1,467,954 width=4) (actual time=130.540..44,839.428 rows=54,862 loops=1)

  • Buffers: shared hit=49,251,891
4. 262.017 768.144 ↑ 1.0 1,514,982 1

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

  • Buffers: shared hit=52,238
5. 1.441 1.441 ↑ 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.030..1.441 rows=1,534 loops=1)

  • Heap Fetches: 1,534
  • Buffers: shared hit=76
6. 504.686 504.686 ↑ 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.329 rows=988 loops=1,534)

  • Index Cond: (userid = departmentusers1.userid)
  • Buffers: shared hit=52,162
7. 7,331.907 42,419.496 ↓ 0.0 0 1,514,982

Index Scan using dm_attendancetimeallocation_facts_pkey on dm_attendancetimeallocation_facts ttendancetimeallocation_facts3 (cost=0.43..48.19 rows=1 width=16) (actual time=0.028..0.028 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=49,199,653
8.          

SubPlan (for Index Scan)

9. 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
10. 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)
11. 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: (tsapprovalrequestkeyvalue5.parentid = tsapprovalrequestkeyvalue9.id)
  • Buffers: shared hit=7,316,418
12. 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: (tsapprovalrequest8.id = tsapprovalrequestkeyvalue5.tsapprovalrequestid)
  • Buffers: shared hit=7,316,418
13. 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
14. 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
15. 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
16. 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
17. 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
18. 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)
19. 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)
20. 0.000 10,220.840 ↓ 0.0 0 1,460,120

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

  • Buffers: shared hit=12,133,856
21. 0.000 10,220.840 ↓ 0.0 0 1,460,120

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

  • Buffers: shared hit=12,133,856
22. 0.000 10,220.840 ↓ 0.0 0 1,460,120

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

  • Buffers: shared hit=12,133,856
23. 0.000 10,220.840 ↓ 0.0 0 1,460,120

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

  • Buffers: shared hit=11,938,233
24. 1,069.322 10,220.840 ↓ 0.0 0 1,460,120

Nested Loop (cost=2.14..26.27 rows=1 width=16) (actual time=0.006..0.007 rows=0 loops=1,460,120)

  • Buffers: shared hit=11,677,011
25. 0.000 8,760.720 ↓ 0.0 0 1,460,120

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

  • Buffers: shared hit=11,300,905
26. 901.117 8,760.720 ↓ 0.0 0 1,460,120

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

  • Buffers: shared hit=11,039,443
27. 0.262 5,840.480 ↓ 0.0 0 1,460,120

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

  • Buffers: shared hit=10,345,230
28. 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
29. 2,919.978 2,919.978 ↓ 0.0 0 1,459,989

Index Scan using test on timesheetapprovalhistory timesheetapprovalhistory12 (cost=0.42..2.47 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1,459,989)

  • Index Cond: ((timesheetid = ttendancetimeallocation_facts3.timesheetid) AND (userid = 3,766))
  • Buffers: shared hit=4,488,975
30. 2,019.123 2,019.123 ↑ 1.0 1 65,133

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue13 (cost=0.43..17.87 rows=1 width=32) (actual time=0.031..0.031 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
31. 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
32. 390.798 390.798 ↑ 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.006 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
33. 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
34. 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
35. 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)
36. 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)
37. 0.000 5,840.480 ↓ 0.0 0 1,460,120

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

  • Join Filter: (tsapprovalrequestkeyvalue19.parentid = tsapprovalrequestkeyvalue23.id)
  • Buffers: shared hit=7,316,418
38. 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
39. 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
40. 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
41. 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
42. 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
43. 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
44. 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)
45. 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)
46. 0.000 10,220.840 ↓ 0.0 0 1,460,120

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

  • Buffers: shared hit=12,133,856
47. 0.000 10,220.840 ↓ 0.0 0 1,460,120

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

  • Buffers: shared hit=12,133,856
48. 0.000 10,220.840 ↓ 0.0 0 1,460,120

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

  • Buffers: shared hit=12,133,856
49. 1,329.854 10,220.840 ↓ 0.0 0 1,460,120

Nested Loop (cost=2.57..27.38 rows=1 width=16) (actual time=0.006..0.007 rows=0 loops=1,460,120)

  • Buffers: shared hit=11,938,233
50. 0.000 8,760.720 ↓ 0.0 0 1,460,120

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

  • Buffers: shared hit=11,677,011
51. 0.000 8,760.720 ↓ 0.0 0 1,460,120

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

  • Buffers: shared hit=11,300,905
52. 901.117 8,760.720 ↓ 0.0 0 1,460,120

Nested Loop (cost=1.27..22.80 rows=1 width=16) (actual time=0.005..0.006 rows=0 loops=1,460,120)

  • Buffers: shared hit=11,039,443
53. 0.262 5,840.480 ↓ 0.0 0 1,460,120

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

  • Buffers: shared hit=10,345,230
54. 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
55. 2,919.978 2,919.978 ↓ 0.0 0 1,459,989

Index Scan using test on timesheetapprovalhistory timesheetapprovalhistory26 (cost=0.42..2.47 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1,459,989)

  • Index Cond: ((timesheetid = ttendancetimeallocation_facts3.timesheetid) AND (userid = 3,766))
  • Buffers: shared hit=4,488,975
56. 2,019.123 2,019.123 ↑ 1.0 1 65,133

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue27 (cost=0.43..17.87 rows=1 width=32) (actual time=0.031..0.031 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
57. 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
58. 390.798 390.798 ↑ 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.006 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
59. 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
60. 65.133 65.133 ↓ 0.0 0 65,133

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue31 (cost=0.43..0.51 rows=1 width=32) (actual time=0.001..0.001 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
61. 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)
62. 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 : 9.622 ms
Execution time : 44,845.467 ms