explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Tz0pu

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

Sort (cost=1,669,809,124.77..1,669,809,210.56 rows=34,316 width=3,055) (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", ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", "*SELECT* 1".entrydate, ((billingrate5.name)::character varying(50)) COLLATE "en_US", ((timeoffcode6.name)::character varying(255)) COLLATE "en_US", ((cl.name)::character varying(255)) COLLATE "en_US", ((userinfo8.displayname)::text) COLLATE "en_US
2.          

Initplan (for Sort)

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=1,007.65..1,669,754,926.64 rows=34,316 width=3,055) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,007.38..1,669,743,140.61 rows=34,316 width=200) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=1,007.09..1,669,729,751.02 rows=34,316 width=247) (actual rows= loops=)

  • Merge Cond: (userhierarchy16.userid = ui.id)
7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.57..2,425.18 rows=6,362 width=20) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy16 (cost=0.29..325.63 rows=6,362 width=8) (actual rows= loops=)

  • Index Cond: ('2020-07-24'::date >= startdate)
  • Filter: ('2020-07-24'::date <= enddate)
9. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo userinfo8 (cost=0.28..0.33 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (userhierarchy16.supervisorid = id)
10. 0.000 0.000 ↓ 0.0

Materialize (cost=1,006.52..1,669,726,882.22 rows=34,316 width=231) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,006.52..1,669,726,796.43 rows=34,316 width=231) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,005.96..1,669,646,001.06 rows=34,316 width=196) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,005.81..1,669,640,331.72 rows=34,316 width=174) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,005.67..1,669,634,926.68 rows=34,316 width=174) (actual rows= loops=)

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

Nested Loop Left Join (cost=1,005.38..1,669,622,368.89 rows=34,316 width=178) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,005.09..1,669,611,181.58 rows=34,316 width=150) (actual rows= loops=)

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

Nested Loop Left Join (cost=1,004.81..1,669,610,476.61 rows=34,316 width=136) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,004.52..1,669,597,928.83 rows=34,316 width=123) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,004.22..1,669,586,933.52 rows=34,316 width=166) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,003.66..1,669,509,780.49 rows=32,769 width=115) (actual rows= loops=)

  • Join Filter: ("*SELECT* 1".userid = ui.id)
21. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..287.06 rows=6,385 width=35) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Materialize (cost=1,003.38..1,666,371,124.38 rows=32,769 width=80) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,003.38..1,666,370,960.54 rows=32,769 width=80) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,003.08..1,666,360,418.17 rows=32,769 width=127) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.52..1,666,283,265.13 rows=32,769 width=76) (actual rows= loops=)

  • Filter: (((timeentrymetadata13.uri IS NULL) AND ("*SELECT* 1".timeoffentryid IS NULL) AND (("*SELECT* 1".timeallocationtype = 1) OR ("*SELECT* 1".timeallocationtype IS NULL))) OR (timeentrymetadata13.uri IS NOT NULL) OR ("*SELECT* 1".timeoffentryid IS NOT NULL) OR ((timeentrymetadata13.uri IS NULL) AND ("*SELECT* 1".timeoffentryid IS NULL) AND ("*SELECT* 1".timeallocationtype = 0)))
26. 0.000 0.000 ↓ 0.0

Append (cost=1,001.96..1,666,205,773.66 rows=32,843 width=84) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=1,001.96..1,431,633,673.29 rows=28,219 width=84) (actual rows= loops=)

  • Filter: ((SubPlan 2) OR (SubPlan 3))
28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,001.54..111,347.24 rows=37,625 width=140) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

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

30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,001.54..109,747.17 rows=37,625 width=133) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

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

32. 0.000 0.000 ↓ 0.0

Gather (cost=1,001.54..109,369.91 rows=37,625 width=111) (actual rows= loops=)

  • Workers Planned: 1
33. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.54..104,607.41 rows=22,132 width=111) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.99..53,150.77 rows=22,132 width=111) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Parallel Index Scan using ixte2entrydate on timeentry te (cost=0.43..1,694.13 rows=22,132 width=60) (actual rows= loops=)

  • Index Cond: ((entrydate >= '2020-07-01'::date) AND (entrydate <= '2020-07-31'::date))
  • Filter: ((timeallocationtype <> 2) OR (timeallocationtype IS NULL))
36. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2timeentryid on timeentrymetadata tembillingrate (cost=0.56..2.31 rows=1 width=67) (actual rows= loops=)

  • Index Cond: ((timeentryid = te.id) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BILLING-RATE'::text))
37. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2timeentryid on timeentrymetadata temdbreaktype (cost=0.56..2.31 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((timeentryid = te.id) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text))
38.          

SubPlan (for Subquery Scan)

39. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..65,464.39 rows=2,223,226 width=16) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on timeentry timeentry17 (cost=0.00..43,492.26 rows=2,223,226 width=16) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Materialize (cost=0.42..4,649.62 rows=169,010 width=4) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Index Only Scan using timeoffs_pkey on timeoffs timeoffs18 (cost=0.42..3,143.57 rows=169,010 width=4) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=1.26..234,572,100.37 rows=4,624 width=84) (actual rows= loops=)

  • Filter: ((SubPlan 2) OR (SubPlan 3))
44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..11,697.62 rows=6,165 width=140) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Index Scan using uix2toe_timeoffidentrydate on timeoffentries toe (cost=0.42..5,075.44 rows=6,165 width=28) (actual rows= loops=)

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

Index Scan using timeoffs_pkey on timeoffs toff (cost=0.42..1.07 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = toe.timeoffid)
47. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2timeentryid on timeentrymetadata timeentrymetadata13 (cost=0.56..2.34 rows=1 width=67) (actual rows= loops=)

  • Index Cond: (("*SELECT* 1".timeentryid = timeentryid) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text))
48. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2timeentryid on timeentrymetadata timeentrymetadata9 (cost=0.56..2.34 rows=1 width=67) (actual rows= loops=)

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

Index Only Scan using project_pkey on project project10 (cost=0.30..0.32 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = CASE WHEN ("substring"(timeentrymetadata9.uri, '^urn:replicon-tenant:ns-cglobalnewinstance:project:(.*)$'::text) ~ '^\s*[-]?[0-9]+\s*$'::text) THEN ("substring"(timeentrymetadata9.uri, '^urn:replicon-tenant:ns-cglobalnewinstance:project:(.*)$'::text))::integer ELSE NULL::integer END)
50. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2timeentryid on timeentrymetadata timeentrymetadata11 (cost=0.56..2.34 rows=1 width=67) (actual rows= loops=)

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

Index Scan using task_pkey on task task12 (cost=0.30..0.32 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (CASE WHEN ("substring"(timeentrymetadata11.uri, '^urn:replicon-tenant:ns-cglobalnewinstance:task:(.*)$'::text) ~ '^\s*[-]?[0-9]+\s*$'::text) THEN ("substring"(timeentrymetadata11.uri, '^urn:replicon-tenant:ns-cglobalnewinstance:task:(.*)$'::text))::integer ELSE NULL::integer END = id)
52. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project pj (cost=0.29..0.37 rows=1 width=21) (actual rows= loops=)

  • Index Cond: (id = COALESCE(project10.id, task12.projectid))
53. 0.000 0.000 ↓ 0.0

Index Scan using login_pkey on login (cost=0.28..260.06 rows=6,385 width=18) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (task12.id = id)
55. 0.000 0.000 ↓ 0.0

Materialize (cost=0.29..204.09 rows=24 width=4) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Index Scan using uix3er_currencyideffectivedate on exchangerate (cost=0.29..203.97 rows=24 width=4) (actual rows= loops=)

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

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

  • Index Cond: ("*SELECT* 1".billingrateid = id)
58. 0.000 0.000 ↓ 0.0

Index Scan using timeoffcode_pkey on timeoffcode timeoffcode6 (cost=0.15..0.17 rows=1 width=30) (actual rows= loops=)

  • Index Cond: ("*SELECT* 1".timeoffcodeid = id)
59. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2timeentryid on timeentrymetadata timeentrymetadata14 (cost=0.56..2.34 rows=1 width=67) (actual rows= loops=)

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

Index Only Scan using clients_pkey on clients clients15 (cost=0.29..0.39 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = CASE WHEN ("substring"(timeentrymetadata14.uri, '^urn:replicon-tenant:ns-cglobalnewinstance:client:(.*)$'::text) ~ '^\s*[-]?[0-9]+\s*$'::text) THEN ("substring"(timeentrymetadata14.uri, '^urn:replicon-tenant:ns-cglobalnewinstance:client:(.*)$'::text))::integer ELSE NULL::integer END)
61. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = COALESCE(clients15.id, pj.clientid))