explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VqxG

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=431,979.38..432,152.29 rows=69,163 width=3,295) (actual rows= loops=)

  • Sort Key: ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", ui.duplicatename, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", (CASE WHEN ui.disabled THEN 0 ELSE 1 END), ((ui.externalid)::character varying(255)) COLLATE "en_US", ((employeetype2.name)::character varying(50)) COLLATE "en_US", ui.startdate, pi.entrydate, ((paycode4.name)::character varying(50)) COLLATE "en_US", ((paycode4.code)::character varying(50)) COLLATE "en_US", ts.startdate, ts.enddate, ta.timestamputc, tslist.timesheetstatus, tah.timestamputc, ((tah.approvalcomments)::text) COLLATE "en_US", ((userinfo11.displayname)::text) COLLATE "en_US", ((dep.name)::character varying(255)) COLLATE "en_US", ((dep.code)::character varying(50)) COLLATE "en_US", ((ui.email)::character varying(255)) COLLATE "en_US", ((location14.name)::character varying(100)) COLLATE "en_US
2. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=203,125.92..426,419.47 rows=69,163 width=3,295) (actual rows= loops=)

  • Hash Cond: (userlocation17.locationid = location14.id)
3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=203,124.81..426,082.91 rows=69,163 width=829) (actual rows= loops=)

  • Hash Cond: (pi.userid = userlocation17.userid)
  • Join Filter: ((pi.entrydate >= userlocation17.startdate) AND (pi.entrydate <= userlocation17.enddate))
4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=202,991.11..424,565.17 rows=69,163 width=813) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=202,990.68..264,174.03 rows=69,163 width=752) (actual rows= loops=)

  • Hash Cond: (ui.id = userhierarchy16.userid)
6. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=202,723.11..263,099.72 rows=69,163 width=736) (actual rows= loops=)

  • Merge Cond: ((dm_attendancetimeallocation_facts.userid = pi.userid) AND (dm_attendancetimeallocation_facts.entrydate = pi.entrydate))
7. 0.000 0.000 ↓ 0.0

Index Scan using ixata2userid on dm_attendancetimeallocation_facts (cost=0.43..52,542.16 rows=1,403,382 width=24) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Sort (cost=202,722.69..202,895.59 rows=69,163 width=720) (actual rows= loops=)

  • Sort Key: pi.userid, pi.entrydate
9. 0.000 0.000 ↓ 0.0

Hash Join (cost=118,891.42..197,162.77 rows=69,163 width=720) (actual rows= loops=)

  • Hash Cond: (pi.userid = du.userid)
10. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=118,756.04..196,038.32 rows=71,933 width=690) (actual rows= loops=)

  • Hash Cond: (ts.id = timesheet15.id)
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=30,535.73..106,888.62 rows=63,440 width=174) (actual rows= loops=)

  • Hash Cond: (ts.id = tahls.timesheetid)
12. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=11,428.78..86,931.41 rows=63,440 width=166) (actual rows= loops=)

  • Hash Cond: (tah.timesheetid = ts.id)
13. 0.000 0.000 ↓ 0.0

Gather (cost=2,860.97..77,556.49 rows=60,400 width=154) (actual rows= loops=)

  • Workers Planned: 2
14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,860.97..70,516.49 rows=25,167 width=154) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,860.54..12,405.10 rows=25,167 width=77) (actual rows= loops=)

  • Hash Cond: (tah.timesheetid = tahls_1.timesheetid)
  • Filter: ((tahls_1.timesheetid IS NULL) OR (tah.serialnumber > tahls_1.lastsubmitserialnumber))
16. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timesheetapprovalhistory tah (cost=0.00..10,346.36 rows=75,500 width=81) (actual rows= loops=)

  • Filter: (((systemprocessidentifier IS NULL) OR (systemprocessidentifier <> 'urn:replicon:approval-system-process:timesheet-submit-script-data-and-validation'::text)) AND (action = ANY ('{2,3,6}'::integer[])))
17. 0.000 0.000 ↓ 0.0

Hash (cost=1,056.35..1,056.35 rows=64,335 width=20) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on timesheetapprovalhistorylastsubmit tahls_1 (cost=0.00..1,056.35 rows=64,335 width=20) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue9 (cost=0.43..2.30 rows=1 width=93) (actual rows= loops=)

  • Index Cond: (tah.id = timesheetapprovalhistoryid)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-ACTUAL-USER'::text)
20. 0.000 0.000 ↓ 0.0

Hash (cost=7,774.81..7,774.81 rows=63,440 width=28) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,270.87..7,774.81 rows=63,440 width=28) (actual rows= loops=)

  • Hash Cond: (tslist.timesheetid = ts.id)
22. 0.000 0.000 ↓ 0.0

Seq Scan on dm_timesheetlist_facts tslist (cost=0.00..5,337.40 rows=63,440 width=20) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=1,445.94..1,445.94 rows=65,994 width=24) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts (cost=0.00..1,445.94 rows=65,994 width=24) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash (cost=18,310.71..18,310.71 rows=63,700 width=24) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,288.25..18,310.71 rows=63,700 width=24) (actual rows= loops=)

  • Hash Cond: (tahls.timesheetid = ts_1.id)
27. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,860.54..15,714.10 rows=64,335 width=24) (actual rows= loops=)

  • Hash Cond: (ta.serialnumber = tahls.lastsubmitserialnumber)
28. 0.000 0.000 ↓ 0.0

Seq Scan on timesheetapprovalhistory ta (cost=0.00..11,671.43 rows=410,343 width=12) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=1,056.35..1,056.35 rows=64,335 width=20) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on timesheetapprovalhistorylastsubmit tahls (cost=0.00..1,056.35 rows=64,335 width=20) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash (cost=1,610.93..1,610.93 rows=65,343 width=16) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts_1 (cost=0.00..1,610.93 rows=65,343 width=16) (actual rows= loops=)

  • Filter: (approvalstatus = ANY ('{1,2}'::integer[]))
33. 0.000 0.000 ↓ 0.0

Hash (cost=87,321.15..87,321.15 rows=71,933 width=532) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=80,360.67..87,321.15 rows=71,933 width=532) (actual rows= loops=)

  • Hash Cond: ((pi.userid = ntpaydetails_durationbyday_g45.userid) AND (pi.entrydate = ntpaydetails_durationbyday_g45.entrydate))
35. 0.000 0.000 ↓ 0.0

Hash Join (cost=23,316.20..30,169.02 rows=20,503 width=500) (actual rows= loops=)

  • Hash Cond: (pi.userid = login.userid)
36. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=23,122.00..29,921.65 rows=20,238 width=477) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Hash Join (cost=23,121.71..24,413.05 rows=12,329 width=461) (actual rows= loops=)

  • Hash Cond: (ui.employeetypeid = employeetype2.id)
38. 0.000 0.000 ↓ 0.0

Hash Join (cost=23,120.37..24,370.20 rows=12,329 width=347) (actual rows= loops=)

  • Hash Cond: (pi.userid = ui.id)
39. 0.000 0.000 ↓ 0.0

Hash Join (cost=22,911.47..24,128.90 rows=12,329 width=272) (actual rows= loops=)

  • Hash Cond: (pi.paycodeid = paycode4.id)
40. 0.000 0.000 ↓ 0.0

HashAggregate (cost=22,909.32..23,513.67 rows=48,348 width=64) (actual rows= loops=)

  • Group Key: pi.entrydate, pi.userid, pi.paycodeid, pi.paycurrencyid
  • Filter: ((sum(pi.duration) <> '00:00:00'::interval) OR (sum(pi.payamount) <> '0'::numeric))
41. 0.000 0.000 ↓ 0.0

Index Scan using ixpri_entrydate2paycodeid on payrollitem pi (cost=0.42..20,971.35 rows=110,741 width=35) (actual rows= loops=)

  • Index Cond: ((entrydate >= '2020-02-01'::date) AND (entrydate <= '2020-08-31'::date))
42. 0.000 0.000 ↓ 0.0

Hash (cost=1.51..1.51 rows=51 width=240) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on paycode paycode4 (cost=0.00..1.51 rows=51 width=240) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Hash (cost=153.40..153.40 rows=4,440 width=75) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui (cost=0.00..153.40 rows=4,440 width=75) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Hash (cost=1.15..1.15 rows=15 width=122) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Seq Scan on employeetype employeetype2 (cost=0.00..1.15 rows=15 width=122) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Index Scan using uix2tsuseridstartdate on timesheet timesheet15 (cost=0.29..0.43 rows=2 width=28) (actual rows= loops=)

  • Index Cond: ((pi.userid = userid) AND (pi.entrydate >= startdate))
  • Filter: (pi.entrydate <= enddate)
49. 0.000 0.000 ↓ 0.0

Hash (cost=137.98..137.98 rows=4,498 width=23) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Seq Scan on login (cost=0.00..137.98 rows=4,498 width=23) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Hash (cost=54,939.40..54,939.40 rows=140,338 width=40) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Subquery Scan on ntpaydetails_durationbyday_g45 (cost=52,132.64..54,939.40 rows=140,338 width=40) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

HashAggregate (cost=52,132.64..53,536.02 rows=140,338 width=56) (actual rows= loops=)

  • Group Key: dm_attendancetimeallocation_facts_1.userid, dm_attendancetimeallocation_facts_1.entrydate
54. 0.000 0.000 ↓ 0.0

Seq Scan on dm_attendancetimeallocation_facts dm_attendancetimeallocation_facts_1 (cost=0.00..38,098.82 rows=1,403,382 width=44) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Hash (cost=82.01..82.01 rows=4,269 width=38) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.28..82.01 rows=4,269 width=38) (actual rows= loops=)

  • Hash Cond: (du.departmentid = dep.id)
57. 0.000 0.000 ↓ 0.0

Seq Scan on departmentusers du (cost=0.00..67.69 rows=4,269 width=8) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Hash (cost=1.57..1.57 rows=57 width=38) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Seq Scan on departments dep (cost=0.00..1.57 rows=57 width=38) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Hash (cost=251.39..251.39 rows=1,294 width=20) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=208.90..251.39 rows=1,294 width=20) (actual rows= loops=)

  • Hash Cond: (userhierarchy16.supervisorid = userinfo11.id)
62. 0.000 0.000 ↓ 0.0

Seq Scan on userhierarchy userhierarchy16 (cost=0.00..39.09 rows=1,294 width=8) (actual rows= loops=)

  • Filter: (('2020-07-30'::date >= startdate) AND ('2020-07-30'::date <= enddate))
63. 0.000 0.000 ↓ 0.0

Hash (cost=153.40..153.40 rows=4,440 width=20) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo userinfo11 (cost=0.00..153.40 rows=4,440 width=20) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue10 (cost=0.43..2.30 rows=2 width=93) (actual rows= loops=)

  • Index Cond: (tah.id = timesheetapprovalhistoryid)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-EFFECTIVE-USER'::text)
66. 0.000 0.000 ↓ 0.0

Hash (cost=83.31..83.31 rows=4,031 width=28) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Seq Scan on userlocation userlocation17 (cost=0.00..83.31 rows=4,031 width=28) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=234) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Seq Scan on location location14 (cost=0.00..1.05 rows=5 width=234) (actual rows= loops=)