explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qpIK : baseline query + timeentry.entrydate + timeentrymetadata.upper(key) (explain only, killed after 4 mimutes)

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

Sort (cost=10,657,507,046.82..10,668,472,237.50 rows=4,386,076,274 width=408) (actual rows= loops=)

  • Sort Key: ((pj.code)::character varying(50)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((cl.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", ((tk.code)::character varying(50)) COLLATE "en_US", ((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.externalid)::character varying(255)) COLLATE "en_US", ((ui.info5)::character varying(255)) COLLATE "en_US", ((dep.name)::character varying(255)) COLLATE "en_US", "*SELECT* 1".entrydate, ts.startdate, ts.enddate, tslist.timesheetstatus, ((tdh.hierarchysorting)::text) COLLATE "en_US", ((tdh.hierarchytaskname)::text) COLLATE "en_US", ((timeentrymetadata9.text)::text) COLLATE "en_US", ((userinfo10.displayname)::text) COLLATE "en_US", "*SELECT* 1".timeentryid, ((billingrate11.name)::character varying(50)) COLLATE "en_US
2.          

Initplan (forSort)

3. 0.000 0.000 ↓ 0.0

Seq Scan on systeminformation (cost=0.00..1.01 rows=1 width=4) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,726,530,464.64..5,907,373,347.67 rows=4,386,076,274 width=408) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".billingrateid = billingrate11.id)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,726,530,452.16..5,847,064,786.42 rows=4,386,076,274 width=399) (actual rows= loops=)

  • Hash Cond: (pj.projectleaderapproverid = userinfo10.id)
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,726,530,411.79..5,786,866,956.79 rows=4,386,076,274 width=389) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".userid = login.userid)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,726,530,313.60..5,726,558,309.83 rows=4,386,076,274 width=371) (actual rows= loops=)

  • Hash Cond: (tk.id = tdh.taskid)
8. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=3,726,524,906.10..5,306,448,101.56 rows=4,386,076,274 width=312) (actual rows= loops=)

  • Merge Cond: ("*SELECT* 1".timeentryid = timeentrymetadata9.timeentryid)
9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3,726,447,291.28..5,293,442,174.31 rows=4,386,076,274 width=268) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=3,726,447,291.14..3,750,340,794.27 rows=4,386,076,274 width=260) (actual rows= loops=)

  • Merge Cond: ("*SELECT* 1".timeentryid = timeentrymetadata16.timeentryid)
11. 0.000 0.000 ↓ 0.0

Sort (cost=3,726,369,249.25..3,737,334,439.94 rows=4,386,076,274 width=256) (actual rows= loops=)

  • Sort Key: "*SELECT* 1".timeentryid
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,302,072.36..400,425,363.62 rows=4,386,076,274 width=256) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".userid = ui.id)
13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,302,032.00..340,116,774.49 rows=4,386,076,274 width=217) (actual rows= loops=)

  • Hash Cond: (task15.id = tk.id)
14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,295,884.79..65,637,434.51 rows=4,386,076,274 width=176) (actual rows= loops=)

  • Hash Cond: (COALESCE(project13.id, task15.projectid) = pj.id)
15. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=3,295,720.42..5,328,721.37 rows=4,386,076,274 width=100) (actual rows= loops=)

  • Merge Cond: ("*SELECT* 1".timeentryid = timeentrymetadata14.timeentryid)
16. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=618,661.31..628,649.70 rows=10,119,106 width=92) (actual rows= loops=)

  • Merge Cond: ("*SELECT* 1".timeentryid = timeentrymetadata12.timeentryid)
17. 0.000 0.000 ↓ 0.0

Sort (cost=478,021.46..479,846.70 rows=730,094 width=88) (actual rows= loops=)

  • Sort Key: "*SELECT* 1".timeentryid
18. 0.000 0.000 ↓ 0.0

Hash Join (cost=13,037.95..371,981.62 rows=730,094 width=88) (actual rows= loops=)

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

Nested Loop (cost=39.22..325,274.08 rows=731,965 width=100) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Append (cost=38.80..239,756.09 rows=43,969 width=72) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=38.80..239,748.50 rows=43,952 width=72) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=38.80..239,308.98 rows=43,952 width=237) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=37.95..94,144.70 rows=1,625 width=237) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=37.52..49,342.37 rows=1,625 width=179) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=37.08..4,564.41 rows=1,625 width=121) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..2.03 rows=1 width=65) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on systeminformation si (cost=0.00..1.01 rows=1 width=33) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on projectsysteminformation psi (cost=0.00..1.01 rows=1 width=32) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on timeentry te (cost=37.08..4,546.13 rows=1,625 width=56) (actual rows= loops=)

  • Recheck Cond: ((entrydate >= '2019-03-30'::date) AND (entrydate <= '2019-04-05'::date))
  • Filter: ((timeallocationtype <> 2) OR (timeallocationtype IS NULL))
30. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ixtemp_teentrydate (cost=0.00..36.68 rows=1,625 width=0) (actual rows= loops=)

  • Index Cond: ((entrydate >= '2019-03-30'::date) AND (entrydate <= '2019-04-05'::date))
31. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid on timeentrymetadata tembillingrate (cost=0.43..27.55 rows=1 width=74) (actual rows= loops=)

  • Index Cond: (timeentryid = te.id)
  • Filter: (key = 'urn:replicon:time-entry-metadata-key:billing-rate'::text)
32. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid on timeentrymetadata temdbreaktype (cost=0.43..27.56 rows=1 width=74) (actual rows= loops=)

  • Index Cond: (timeentryid = te.id)
  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text)
33. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.85..87.80 rows=18 width=12) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Index Scan using uix2tsuseridstartdate on timesheet ts_1 (cost=0.42..5.83 rows=17 width=28) (actual rows= loops=)

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

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot tspolicy (cost=0.43..4.80 rows=2 width=16) (actual rows= loops=)

  • Index Cond: (timesheetid = ts_1.id)
  • Filter: (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text)
36. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=0.42..7.59 rows=17 width=72) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.42..7.42 rows=17 width=24) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..2.04 rows=1 width=24) (actual rows= loops=)

  • Join Filter: (toe.timeoffid = toff.id)
39. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffentries toe (cost=0.00..1.01 rows=1 width=24) (actual rows= loops=)

  • Filter: ((entrydate >= '2019-03-30'::date) AND (entrydate <= '2019-04-05'::date))
40. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffs toff (cost=0.00..1.01 rows=1 width=8) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Index Only Scan using ixtsuseridstartdateenddate on timesheet ts_2 (cost=0.42..5.21 rows=17 width=12) (actual rows= loops=)

  • Index Cond: ((userid = toff.userid) AND (startdate <= toe.entrydate) AND (enddate >= toe.entrydate))
42. 0.000 0.000 ↓ 0.0

Index Scan using ixtsuseridstartdateenddate on timesheet ts (cost=0.42..1.77 rows=17 width=28) (actual rows= loops=)

  • Index Cond: ((userid = "*SELECT* 1".userid) AND ("*SELECT* 1".entrydate >= startdate) AND ("*SELECT* 1".entrydate <= enddate))
43. 0.000 0.000 ↓ 0.0

Hash (cost=10,526.64..10,526.64 rows=134,648 width=20) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on dm_timesheetlist_facts tslist (cost=0.00..10,526.64 rows=134,648 width=20) (actual rows= loops=)

  • Filter: (timesheetstatus = ANY ('{0,1,2,3,4}'::integer[]))
45. 0.000 0.000 ↓ 0.0

Materialize (cost=140,639.85..142,852.18 rows=442,467 width=20) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Sort (cost=140,639.85..141,746.02 rows=442,467 width=20) (actual rows= loops=)

  • Sort Key: timeentrymetadata12.timeentryid
47. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=77,855.19..90,071.54 rows=442,467 width=20) (actual rows= loops=)

  • Merge Cond: ((upper(('urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:project:'::text || COALESCE((project13.id)::text, ''::text)))) = (upper(timeentrymetadata12.uri)))
48. 0.000 0.000 ↓ 0.0

Sort (cost=240.37..247.30 rows=2,772 width=4) (actual rows= loops=)

  • Sort Key: (upper(('urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:project:'::text || COALESCE((project13.id)::text, ''::text))))
49. 0.000 0.000 ↓ 0.0

Index Only Scan using project_pkey on project project13 (cost=0.28..81.86 rows=2,772 width=4) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Sort (cost=77,614.81..77,694.62 rows=31,924 width=74) (actual rows= loops=)

  • Sort Key: (upper(timeentrymetadata12.uri))
51. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on timeentrymetadata timeentrymetadata12 (cost=1,339.97..75,226.52 rows=31,924 width=74) (actual rows= loops=)

  • Recheck Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:PROJECT'::text)
52. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on istemp_temdkey (cost=0.00..1,331.99 rows=31,924 width=0) (actual rows= loops=)

  • Index Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:PROJECT'::text)
53. 0.000 0.000 ↓ 0.0

Materialize (cost=2,677,059.10..2,746,245.59 rows=13,837,298 width=24) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Sort (cost=2,677,059.10..2,711,652.35 rows=13,837,298 width=24) (actual rows= loops=)

  • Sort Key: timeentrymetadata14.timeentryid
55. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=87,182.75..468,267.11 rows=13,837,298 width=24) (actual rows= loops=)

  • Merge Cond: ((upper(timeentrymetadata14.uri)) = (upper(('urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:task:'::text || COALESCE((task15.id)::text, ''::text)))))
56. 0.000 0.000 ↓ 0.0

Sort (cost=77,614.81..77,694.62 rows=31,924 width=74) (actual rows= loops=)

  • Sort Key: (upper(timeentrymetadata14.uri))
57. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on timeentrymetadata timeentrymetadata14 (cost=1,339.97..75,226.52 rows=31,924 width=74) (actual rows= loops=)

  • Recheck Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TASK'::text)
58. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on istemp_temdkey (cost=0.00..1,331.99 rows=31,924 width=0) (actual rows= loops=)

  • Index Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TASK'::text)
59. 0.000 0.000 ↓ 0.0

Sort (cost=9,567.93..9,784.65 rows=86,689 width=8) (actual rows= loops=)

  • Sort Key: (upper(('urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:task:'::text || COALESCE((task15.id)::text, ''::text))))
60. 0.000 0.000 ↓ 0.0

Seq Scan on task task15 (cost=0.00..2,457.89 rows=86,689 width=8) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Hash (cost=129.72..129.72 rows=2,772 width=84) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Seq Scan on project pj (cost=0.00..129.72 rows=2,772 width=84) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Hash (cost=4,301.60..4,301.60 rows=86,689 width=45) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2.44..4,301.60 rows=86,689 width=45) (actual rows= loops=)

  • Hash Cond: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
65. 0.000 0.000 ↓ 0.0

Seq Scan on task tk (cost=0.00..2,457.89 rows=86,689 width=49) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Hash (cost=2.35..2.35 rows=7 width=4) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Seq Scan on exchangerate (cost=0.00..2.35 rows=7 width=4) (actual rows= loops=)

  • Filter: ((variablecurrencyid = $0) AND (('now'::cstring)::date >= effectivedate) AND (('now'::cstring)::date <= enddate))
68. 0.000 0.000 ↓ 0.0

Hash (cost=27.94..27.94 rows=994 width=39) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui (cost=0.00..27.94 rows=994 width=39) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Sort (cost=78,041.89..78,121.70 rows=31,924 width=20) (actual rows= loops=)

  • Sort Key: timeentrymetadata16.timeentryid
71. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,342.45..75,653.60 rows=31,924 width=20) (actual rows= loops=)

  • Hash Cond: (upper(timeentrymetadata16.uri) = upper(('urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:client:'::text || COALESCE((clients17.id)::text, ''::text))))
72. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on timeentrymetadata timeentrymetadata16 (cost=1,339.97..75,226.52 rows=31,924 width=74) (actual rows= loops=)

  • Recheck Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:CLIENT'::text)
73. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on istemp_temdkey (cost=0.00..1,331.99 rows=31,924 width=0) (actual rows= loops=)

  • Index Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:CLIENT'::text)
74. 0.000 0.000 ↓ 0.0

Hash (cost=1.66..1.66 rows=66 width=4) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Seq Scan on clients clients17 (cost=0.00..1.66 rows=66 width=4) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Index Scan using clients_pkey on clients cl (cost=0.14..0.34 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = COALESCE(clients17.id, pj.clientid))
77. 0.000 0.000 ↓ 0.0

Sort (cost=77,614.81..77,694.62 rows=31,924 width=60) (actual rows= loops=)

  • Sort Key: timeentrymetadata9.timeentryid
78. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on timeentrymetadata timeentrymetadata9 (cost=1,339.97..75,226.52 rows=31,924 width=60) (actual rows= loops=)

  • Recheck Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:COMMENTS'::text)
79. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on istemp_temdkey (cost=0.00..1,331.99 rows=31,924 width=0) (actual rows= loops=)

  • Index Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:COMMENTS'::text)
80. 0.000 0.000 ↓ 0.0

Hash (cost=3,391.89..3,391.89 rows=86,689 width=63) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Seq Scan on taskdenormalizedhierarchy tdh (cost=0.00..3,391.89 rows=86,689 width=63) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Hash (cost=85.76..85.76 rows=994 width=30) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Hash Join (cost=30.49..85.76 rows=994 width=30) (actual rows= loops=)

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

Hash Join (cost=28.37..69.97 rows=994 width=20) (actual rows= loops=)

  • Hash Cond: (login.userid = du.userid)
85. 0.000 0.000 ↓ 0.0

Seq Scan on login (cost=0.00..27.94 rows=994 width=12) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Hash (cost=15.94..15.94 rows=994 width=8) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Seq Scan on departmentusers du (cost=0.00..15.94 rows=994 width=8) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Hash (cost=1.50..1.50 rows=50 width=18) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Seq Scan on departments dep (cost=0.00..1.50 rows=50 width=18) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Hash (cost=27.94..27.94 rows=994 width=18) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo userinfo10 (cost=0.00..27.94 rows=994 width=18) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Hash (cost=7.77..7.77 rows=377 width=41) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Seq Scan on billingrate billingrate11 (cost=0.00..7.77 rows=377 width=41) (actual rows= loops=)