explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SJqD

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

Sort (cost=667.52..667.52 rows=1 width=3,773) (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

Nested Loop Left Join (cost=68.59..666.50 rows=1 width=3,773) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=68.45..666.33 rows=1 width=3,671) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=68.31..665.68 rows=1 width=3,643) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=68.03..664.77 rows=1 width=3,627) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=67.89..664.47 rows=1 width=3,602) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=67.76..663.91 rows=1 width=3,090) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=67.62..663.22 rows=4 width=3,094) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=67.48..658.70 rows=26 width=2,574) (actual rows= loops=)

  • Join Filter: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=67.48..655.96 rows=26 width=2,578) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=67.34..648.24 rows=26 width=2,543) (actual rows= loops=)

  • Join Filter: (cl.id = COALESCE(clients17.id, pj.clientid))
14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=67.34..645.63 rows=26 width=2,031) (actual rows= loops=)

  • Join Filter: (("substring"(timeentrymetadata16.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:client:(.*)$'::text))::integer = clients17.id)
15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=67.34..642.24 rows=26 width=2,098) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash Join (cost=67.06..618.42 rows=26 width=2,027) (actual rows= loops=)

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

Hash Left Join (cost=65.38..615.85 rows=170 width=754) (actual rows= loops=)

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

Hash Left Join (cost=62.82..612.44 rows=170 width=100) (actual rows= loops=)

  • Hash Cond: (("substring"(timeentrymetadata14.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:task:(.*)$'::text))::integer = task15.id)
19. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=57.98..605.26 rows=170 width=163) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=57.70..449.52 rows=170 width=92) (actual rows= loops=)

  • Hash Cond: (("substring"(timeentrymetadata12.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:project:(.*)$'::text))::integer = project13.id)
21. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=55.14..445.47 rows=170 width=159) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash Join (cost=54.86..289.73 rows=170 width=88) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".userid = ts.userid)
  • Join Filter: (("*SELECT* 1".entrydate >= ts.startdate) AND ("*SELECT* 1".entrydate <= ts.enddate))
23. 0.000 0.000 ↓ 0.0

Append (cost=9.49..183.39 rows=1,381 width=72) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=9.49..178.88 rows=1,380 width=72) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9.49..165.08 rows=1,380 width=262) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

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

27. 0.000 0.000 ↓ 0.0

Materialize (cost=9.49..61.94 rows=2 width=230) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=9.49..61.93 rows=2 width=230) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=9.20..40.90 rows=2 width=246) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=4.87..32.76 rows=1 width=230) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.56..17.63 rows=1 width=159) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..9.32 rows=1 width=88) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

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

34. 0.000 0.000 ↓ 0.0

Index Scan using ixtemp_teentrydate on timeentry te (cost=0.28..8.30 rows=1 width=56) (actual rows= loops=)

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

Index Scan using ixtemdkey2 on timeentrymetadata tembillingrate (cost=0.28..8.30 rows=1 width=87) (actual rows= loops=)

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

Bitmap Heap Scan on timeentrymetadata temdbreaktype (cost=4.31..15.11 rows=1 width=87) (actual rows= loops=)

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

Bitmap Index Scan on ixtemtimeentryid (cost=0.00..4.30 rows=3 width=0) (actual rows= loops=)

  • Index Cond: (timeentryid = te.id)
38. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on timesheet ts_1 (cost=4.34..8.13 rows=2 width=28) (actual rows= loops=)

  • Recheck Cond: ((userid = te.userid) AND (te.entrydate >= startdate) AND (te.entrydate <= enddate))
39. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ixtsuseridstartdateenddate (cost=0.00..4.33 rows=2 width=0) (actual rows= loops=)

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

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot tspolicy (cost=0.29..10.50 rows=1 width=16) (actual rows= loops=)

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

Subquery Scan on *SELECT* 2 (cost=1.85..4.51 rows=1 width=72) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.85..4.50 rows=1 width=24) (actual rows= loops=)

  • Join Filter: ((toe.entrydate >= ts_2.startdate) AND (toe.entrydate <= ts_2.enddate))
43. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.70..3.17 rows=1 width=24) (actual rows= loops=)

  • Hash Cond: (toff.id = toe.timeoffid)
44. 0.000 0.000 ↓ 0.0

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

45. 0.000 0.000 ↓ 0.0

Hash (cost=1.69..1.69 rows=1 width=24) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

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

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

Index Scan using uix2tsuseridstartdate on timesheet ts_2 (cost=0.15..1.12 rows=14 width=12) (actual rows= loops=)

  • Index Cond: (userid = toff.userid)
48. 0.000 0.000 ↓ 0.0

Hash (cost=42.59..42.59 rows=222 width=16) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Hash Join (cost=32.29..42.59 rows=222 width=16) (actual rows= loops=)

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

Seq Scan on timesheet ts (cost=0.00..6.97 rows=297 width=28) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Hash (cost=29.27..29.27 rows=242 width=20) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Seq Scan on dm_timesheetlist_facts tslist (cost=0.00..29.27 rows=242 width=20) (actual rows= loops=)

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

Index Scan using ixtemtimeentryid on timeentrymetadata timeentrymetadata12 (cost=0.28..0.91 rows=1 width=87) (actual rows= loops=)

  • Index Cond: ("*SELECT* 1".timeentryid = timeentryid)
  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:PROJECT'::text)
54. 0.000 0.000 ↓ 0.0

Hash (cost=2.25..2.25 rows=25 width=4) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Seq Scan on project project13 (cost=0.00..2.25 rows=25 width=4) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid on timeentrymetadata timeentrymetadata14 (cost=0.28..0.91 rows=1 width=87) (actual rows= loops=)

  • Index Cond: ("*SELECT* 1".timeentryid = timeentryid)
  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TASK'::text)
57. 0.000 0.000 ↓ 0.0

Hash (cost=3.26..3.26 rows=126 width=8) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Seq Scan on task task15 (cost=0.00..3.26 rows=126 width=8) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Hash (cost=2.25..2.25 rows=25 width=662) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Seq Scan on project pj (cost=0.00..2.25 rows=25 width=662) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Hash (cost=1.30..1.30 rows=30 width=1,273) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui (cost=0.00..1.30 rows=30 width=1,273) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid on timeentrymetadata timeentrymetadata16 (cost=0.28..0.91 rows=1 width=87) (actual rows= loops=)

  • Index Cond: ("*SELECT* 1".timeentryid = timeentryid)
  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:CLIENT'::text)
64. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.06 rows=4 width=4) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

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

66. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.06 rows=4 width=520) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Seq Scan on clients cl (cost=0.00..1.04 rows=4 width=520) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Index Scan using task_pkey on task tk (cost=0.14..0.29 rows=1 width=39) (actual rows= loops=)

  • Index Cond: (task15.id = id)
69. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..2.35 rows=1 width=4) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

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

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

Index Scan using login_pkey on login (cost=0.14..0.16 rows=1 width=520) (actual rows= loops=)

  • Index Cond: (userid = "*SELECT* 1".userid)
72. 0.000 0.000 ↓ 0.0

Index Scan using ixduuserid on departmentusers du (cost=0.14..0.16 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (userid = "*SELECT* 1".userid)
73. 0.000 0.000 ↓ 0.0

Index Scan using departments_pkey on departments dep (cost=0.13..0.55 rows=1 width=520) (actual rows= loops=)

  • Index Cond: (id = du.departmentid)
74. 0.000 0.000 ↓ 0.0

Index Scan using taskdenormalizedhierarchy_pkey on taskdenormalizedhierarchy tdh (cost=0.14..0.29 rows=1 width=29) (actual rows= loops=)

  • Index Cond: (tk.id = taskid)
75. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid on timeentrymetadata timeentrymetadata9 (cost=0.28..0.91 rows=1 width=32) (actual rows= loops=)

  • Index Cond: ("*SELECT* 1".timeentryid = timeentryid)
  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:COMMENTS'::text)
76. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo userinfo10 (cost=0.14..0.64 rows=1 width=36) (actual rows= loops=)

  • Index Cond: (pj.projectleaderapproverid = id)
77. 0.000 0.000 ↓ 0.0

Index Scan using billingrate_pkey on billingrate billingrate11 (cost=0.14..0.16 rows=1 width=134) (actual rows= loops=)

  • Index Cond: ("*SELECT* 1".billingrateid = id)