explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0PiR

Settings
# exclusive inclusive rows x rows loops node
1. 286.057 46,080.072 ↑ 3.8 8 1

Nested Loop Semi Join (cost=1.13..93,189,095.36 rows=30 width=122) (actual time=45,591.824..46,080.072 rows=8 loops=1)

  • Join Filter: (departments0.id = departmentusers1.departmentid)
  • Rows Removed by Join Filter: 3,407,132
  • Buffers: shared hit=51,268,637
2. 0.031 0.031 ↑ 1.0 64 1

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

  • Buffers: shared hit=1
3. 225.540 45,793.984 ↑ 27.6 53,237 64

Materialize (cost=1.13..92,444,078.03 rows=1,467,958 width=4) (actual time=1.949..715.531 rows=53,237 loops=64)

  • Buffers: shared hit=51,268,636
4. 841.466 45,568.444 ↑ 26.8 54,869 1

Nested Loop (cost=1.13..92,436,738.24 rows=1,467,958 width=4) (actual time=124.722..45,568.444 rows=54,869 loops=1)

  • Buffers: shared hit=51,268,636
5. 266.670 789.861 ↓ 1.0 1,515,073 1

Nested Loop (cost=0.71..76,441.32 rows=1,514,982 width=20) (actual time=0.051..789.861 rows=1,515,073 loops=1)

  • Buffers: shared hit=53,340
6. 1.631 1.631 ↓ 1.0 1,534 1

Index Only Scan using departmentusers_pkey on departmentusers departmentusers1 (cost=0.28..36.09 rows=1,521 width=8) (actual time=0.031..1.631 rows=1,534 loops=1)

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

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

  • Index Cond: (userid = departmentusers1.userid)
  • Buffers: shared hit=53,248
8. 7,387.301 43,937.117 ↓ 0.0 0 1,515,073

Index Scan using dm_attendancetimeallocation_facts_pkey on dm_attendancetimeallocation_facts ttendancetimeallocation_facts3 (cost=0.43..60.96 rows=1 width=16) (actual time=0.029..0.029 rows=0 loops=1,515,073)

  • 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=51,215,296
9.          

SubPlan (for Index Scan)

10. 1,504.920 1,504.920 ↓ 0.0 0 1,504,920

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,920)

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

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

  • Index Cond: (projectleaderapproverid = 3,766)
12. 0.000 2,920.408 ↓ 0.0 0 1,460,204

Nested Loop (cost=1.48..24.38 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1,460,204)

  • Buffers: shared hit=4,380,612
13. 0.000 2,920.408 ↓ 0.0 0 1,460,204

Nested Loop (cost=1.21..20.65 rows=6 width=84) (actual time=0.002..0.002 rows=0 loops=1,460,204)

  • Buffers: shared hit=4,380,612
14. 1,460.204 2,920.408 ↓ 0.0 0 1,460,204

Nested Loop (cost=0.94..8.02 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1,460,204)

  • Buffers: shared hit=4,380,612
15. 0.000 1,460.204 ↓ 0.0 0 1,460,204

Nested Loop (cost=0.52..5.57 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=1,460,204)

  • Buffers: shared hit=4,380,612
16. 1,460.204 1,460.204 ↓ 0.0 0 1,460,204

Index Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest8 (cost=0.26..2.28 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=1,460,204)

  • Index Cond: (userid = 3,766)
  • Buffers: shared hit=4,380,612
17. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on timesheetapprovalnodes timesheetapprovalnodes7 (cost=0.26..2.28 rows=1 width=32) (never executed)

  • Index Cond: (id = tsapprovalrequest8.nodeid)
  • Filter: (timesheetid = ttendancetimeallocation_facts3.timesheetid)
18. 0.000 0.000 ↓ 0.0 0

Index Only Scan using timesheet_pkey on timesheet timesheet6 (cost=0.42..2.44 rows=1 width=16) (never executed)

  • Index Cond: (id = ttendancetimeallocation_facts3.timesheetid)
  • Heap Fetches: 0
19. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtsarkvtsapprovalrequestid on tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue5 (cost=0.27..12.52 rows=12 width=88) (never executed)

  • Index Cond: (tsapprovalrequestid = tsapprovalrequest8.id)
  • Filter: (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 tsapprovalrequestkeyvalue_pkey on tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue9 (cost=0.27..0.61 rows=1 width=16) (never executed)

  • Index Cond: (id = tsapprovalrequestkeyvalue5.parentid)
  • Filter: (parentid IS NULL)
21. 0.000 14,602.040 ↓ 0.0 0 1,460,204

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

  • Buffers: shared hit=16,061,018
22. 0.000 14,602.040 ↓ 0.0 0 1,460,204

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

  • Buffers: shared hit=16,061,018
23. 1,329.938 14,602.040 ↓ 0.0 0 1,460,204

Nested Loop (cost=3.00..30.74 rows=1 width=16) (actual time=0.010..0.010 rows=0 loops=1,460,204)

  • Buffers: shared hit=16,061,018
24. 0.000 13,141.836 ↓ 0.0 0 1,460,204

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

  • Buffers: shared hit=15,865,376
25. 1,004.273 13,141.836 ↓ 0.0 0 1,460,204

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

  • Buffers: shared hit=15,603,577
26. 0.000 11,681.632 ↓ 0.0 0 1,460,204

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

  • Buffers: shared hit=15,228,713
27. 705.886 11,681.632 ↓ 0.0 0 1,460,204

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

  • Buffers: shared hit=14,967,887
28. 1,460.618 8,761.224 ↓ 0.0 0 1,460,204

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

  • Buffers: shared hit=14,295,446
29. 2,920.408 2,920.408 ↑ 1.0 1 1,460,204

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,204)

  • Index Cond: (id = ttendancetimeallocation_facts3.timesheetid)
  • Heap Fetches: 155,924
  • Buffers: shared hit=4,552,057
30. 4,380.198 4,380.198 ↓ 0.0 0 1,460,066

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,460,066)

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

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue13 (cost=0.43..17.87 rows=1 width=32) (actual time=0.029..0.034 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:3AA5FB44564046B2AF076BA0EF294BA6:USER:3,766'::text))
  • Rows Removed by Filter: 16
  • Buffers: shared hit=672,441
32. 195.399 195.399 ↑ 1.0 1 65,133

Index Scan using timesheetapprovalhistorykeyvalue_pkey on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue14 (cost=0.43..2.23 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=260,826
33. 455.931 455.931 ↑ 1.0 1 65,133

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue15 (cost=0.43..1.11 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=374,864
34. 130.266 130.266 ↑ 2.0 1 65,133

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

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue15.id)
  • Buffers: shared hit=261,799
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,642
36. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue18 (cost=0.43..3.13 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. 0.000 2,920.408 ↓ 0.0 0 1,460,204

Nested Loop (cost=1.48..12.10 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1,460,204)

  • Buffers: shared hit=4,380,612
39. 0.000 2,920.408 ↓ 0.0 0 1,460,204

Nested Loop (cost=1.21..9.31 rows=1 width=84) (actual time=0.002..0.002 rows=0 loops=1,460,204)

  • Join Filter: (tsapprovalrequest22.id = tsapprovalrequestkeyvalue19.tsapprovalrequestid)
  • Buffers: shared hit=4,380,612
40. 1,460.204 2,920.408 ↓ 0.0 0 1,460,204

Nested Loop (cost=0.94..8.02 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1,460,204)

  • Buffers: shared hit=4,380,612
41. 0.000 1,460.204 ↓ 0.0 0 1,460,204

Nested Loop (cost=0.52..5.57 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=1,460,204)

  • Buffers: shared hit=4,380,612
42. 1,460.204 1,460.204 ↓ 0.0 0 1,460,204

Index Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest22 (cost=0.26..2.28 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=1,460,204)

  • Index Cond: (userid = 3,766)
  • Buffers: shared hit=4,380,612
43. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on timesheetapprovalnodes timesheetapprovalnodes21 (cost=0.26..2.28 rows=1 width=32) (never executed)

  • Index Cond: (id = tsapprovalrequest22.nodeid)
  • Filter: (timesheetid = ttendancetimeallocation_facts3.timesheetid)
44. 0.000 0.000 ↓ 0.0 0

Index Only Scan using timesheet_pkey on timesheet timesheet20 (cost=0.42..2.44 rows=1 width=16) (never executed)

  • Index Cond: (id = ttendancetimeallocation_facts3.timesheetid)
  • Heap Fetches: 0
45. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtsarkvkey on tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue19 (cost=0.27..1.29 rows=1 width=88) (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 tsapprovalrequestkeyvalue_pkey on tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue23 (cost=0.27..2.29 rows=1 width=16) (never executed)

  • Index Cond: (id = tsapprovalrequestkeyvalue19.parentid)
  • Filter: (parentid IS NULL)
47. 1,460.204 14,602.040 ↓ 0.0 0 1,460,204

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

  • Buffers: shared hit=16,061,018
48. 0.000 13,141.836 ↓ 0.0 0 1,460,204

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

  • Buffers: shared hit=16,061,018
49. 0.000 13,141.836 ↓ 0.0 0 1,460,204

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

  • Buffers: shared hit=16,061,018
50. 0.000 13,141.836 ↓ 0.0 0 1,460,204

Nested Loop (cost=2.57..30.22 rows=1 width=16) (actual time=0.008..0.009 rows=0 loops=1,460,204)

  • Buffers: shared hit=15,865,376
51. 1,004.273 13,141.836 ↓ 0.0 0 1,460,204

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

  • Buffers: shared hit=15,603,577
52. 0.000 11,681.632 ↓ 0.0 0 1,460,204

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

  • Buffers: shared hit=15,228,713
53. 771.019 11,681.632 ↓ 0.0 0 1,460,204

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

  • Buffers: shared hit=14,967,887
54. 1,460.618 8,761.224 ↓ 0.0 0 1,460,204

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

  • Buffers: shared hit=14,295,446
55. 2,920.408 2,920.408 ↑ 1.0 1 1,460,204

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

  • Index Cond: (id = ttendancetimeallocation_facts3.timesheetid)
  • Heap Fetches: 155,924
  • Buffers: shared hit=4,552,057
56. 4,380.198 4,380.198 ↓ 0.0 0 1,460,066

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,460,066)

  • Index Cond: (timesheetid = ttendancetimeallocation_facts3.timesheetid)
  • Filter: (userid = 3,766)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=9,743,389
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.029..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:3AA5FB44564046B2AF076BA0EF294BA6:USER:3,766'::text))
  • Rows Removed by Filter: 16
  • Buffers: shared hit=672,441
58. 195.399 195.399 ↑ 1.0 1 65,133

Index Scan using timesheetapprovalhistorykeyvalue_pkey on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue28 (cost=0.43..2.23 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=260,826
59. 455.931 455.931 ↑ 1.0 1 65,133

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue29 (cost=0.43..1.11 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=374,864
60. 130.266 130.266 ↑ 2.0 1 65,133

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

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue29.id)
  • Buffers: shared hit=261,799
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,642
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 : 12.135 ms
Execution time : 46,081.039 ms