explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aG4U

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

Sort (cost=720,060.93..720,060.94 rows=1 width=2,856) (actual rows= loops=)

  • Sort Key: ((cl.name)::character varying(255)) COLLATE "en_US", ((pj.code)::character varying(50)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", ((ui.info5)::character varying(255)) COLLATE "en_US", ((ui.externalid)::character varying(255)) COLLATE "en_US", "*SELECT* 1".entrydate, ((billingrate5.name)::character varying(50)) 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 Semi Join (cost=258,575.76..720,059.91 rows=1 width=2,856) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=258,575.62..720,058.74 rows=1 width=186) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=258,575.34..720,056.43 rows=1 width=186) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=258,574.78..720,052.81 rows=1 width=186) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=258,574.50..720,051.50 rows=1 width=186) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=258,574.23..720,051.21 rows=1 width=177) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=258,573.95..720,050.91 rows=1 width=156) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=258,573.80..720,050.73 rows=1 width=160) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=258,573.39..720,050.24 rows=1 width=134) (actual rows= loops=)

  • Filter: (cl.id = COALESCE(clients12.id, pj.clientid))
13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=258,573.23..720,049.05 rows=1 width=201) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=258,573.09..720,046.88 rows=1 width=177) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=258,572.65..720,045.90 rows=1 width=130) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=258,572.23..720,043.46 rows=1 width=146) (actual rows= loops=)

  • Filter: (pj.id = COALESCE(project7.id, task9.projectid))
17. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=258,571.80..720,017.98 rows=55 width=209) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=258,571.37..719,963.65 rows=55 width=146) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=258,571.08..719,946.60 rows=55 width=205) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=258,570.64..719,892.27 rows=55 width=142) (actual rows= loops=)

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

Nested Loop (cost=0.70..2,347.82 rows=1 width=94) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Index Scan using uix2tsuseridstartdate on timesheet ts (cost=0.42..2,345.51 rows=1 width=28) (actual rows= loops=)

  • Index Cond: ((startdate >= '2020-08-15'::date) AND (startdate <= '2020-08-21'::date))
  • Filter: ((enddate >= '2020-08-15'::date) AND (enddate <= '2020-08-21'::date))
23. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project pj (cost=0.28..2.30 rows=1 width=66) (actual rows= loops=)

  • Index Cond: (id = 2,805)
24. 0.000 0.000 ↓ 0.0

Append (cost=258,569.94..708,805.21 rows=499,385 width=56) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=258,569.94..662,103.83 rows=497,115 width=56) (actual rows= loops=)

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

Nested Loop (cost=212,092.23..605,683.83 rows=662,820 width=140) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

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

28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=212,092.23..577,512.97 rows=662,820 width=215) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

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

30. 0.000 0.000 ↓ 0.0

Gather (cost=212,092.23..570,883.76 rows=662,820 width=182) (actual rows= loops=)

  • Workers Planned: 2
31. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=211,092.23..503,601.76 rows=276,175 width=182) (actual rows= loops=)

  • Hash Cond: (te.id = temdbreaktype.timeentryid)
32. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.43..291,474.29 rows=276,175 width=119) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timeentry te (cost=0.00..49,505.66 rows=276,175 width=56) (actual rows= loops=)

  • Filter: (((timeallocationtype <> 2) OR (timeallocationtype IS NULL)) AND (CASE WHEN (hours IS NOT NULL) THEN hours WHEN ((intime IS NULL) OR (outtime IS NULL)) THEN NULL::interval WHEN ((outtime - intime) >= '00:00:00'::interval) THEN (outtime - intime) ELSE (('24:00:00'::interval + (outtime)::interval) - (intime)::interval) END >= '00:00:00.036'::interval))
34. 0.000 0.000 ↓ 0.0

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

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

Hash (cost=211,091.79..211,091.79 rows=1 width=79) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2timeentryid on timeentrymetadata temdbreaktype (cost=0.56..211,091.79 rows=1 width=79) (actual rows= loops=)

  • Index Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text)
37.          

SubPlan (for Subquery Scan)

38. 0.000 0.000 ↓ 0.0

Index Only Scan using timeentry_pkey on timeentry timeentry18 (cost=0.43..41,432.32 rows=1,988,726 width=16) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffs timeoffs19 (cost=0.00..67.86 rows=2,286 width=4) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=46,574.14..46,701.38 rows=2,270 width=56) (actual rows= loops=)

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

Hash Join (cost=96.44..178.27 rows=3,027 width=140) (actual rows= loops=)

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

Seq Scan on timeoffentries toe (cost=0.00..73.88 rows=3,027 width=8) (actual rows= loops=)

  • Filter: (duration >= '00:00:00.036'::interval)
43. 0.000 0.000 ↓ 0.0

Hash (cost=67.86..67.86 rows=2,286 width=8) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffs toff (cost=0.00..67.86 rows=2,286 width=8) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid on timeentrymetadata timeentrymetadata6 (cost=0.43..0.98 rows=1 width=79) (actual rows= loops=)

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

Index Only Scan using project_pkey on project project7 (cost=0.29..0.31 rows=1 width=4) (actual rows= loops=)

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

Index Scan using ixtemtimeentryid on timeentrymetadata timeentrymetadata8 (cost=0.43..0.98 rows=1 width=79) (actual rows= loops=)

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

Index Scan using task_pkey on task task9 (cost=0.43..0.45 rows=1 width=8) (actual rows= loops=)

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

Index Scan using dm_timesheetlist_facts_pkey on dm_timesheetlist_facts tslist (cost=0.42..2.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (timesheetid = ts.id)
  • Filter: (timesheetstatus = 2)
50. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid on timeentrymetadata timeentrymetadata11 (cost=0.43..0.98 rows=1 width=79) (actual rows= loops=)

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

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

  • Index Cond: (id = 60)
52. 0.000 0.000 ↓ 0.0

Index Only Scan using clients_pkey on clients clients12 (cost=0.15..1.17 rows=1 width=4) (actual rows= loops=)

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

Index Scan using task_pkey on task tk (cost=0.42..0.49 rows=1 width=30) (actual rows= loops=)

  • Index Cond: (task9.id = id)
54. 0.000 0.000 ↓ 0.0

Index Scan using uix3er_currencyideffectivedate on exchangerate (cost=0.14..0.18 rows=1 width=4) (actual rows= loops=)

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

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..0.30 rows=1 width=25) (actual rows= loops=)

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

Index Scan using billingrate_pkey on billingrate billingrate5 (cost=0.27..0.29 rows=1 width=41) (actual rows= loops=)

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

Index Only Scan using project_pkey on project project13 (cost=0.28..1.30 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = 2,805)
58. 0.000 0.000 ↓ 0.0

Unique (cost=0.56..3.61 rows=1 width=4) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..3.61 rows=1 width=4) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Index Only Scan using project_pkey on project project14 (cost=0.28..1.30 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = 2,805)
61. 0.000 0.000 ↓ 0.0

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient projectclient15 (cost=0.28..2.30 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((projectid = 2,805) AND (clientid = 70))
62. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project project16 (cost=0.28..2.30 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = 2,805)
  • Filter: (projectleaderapproverid = 503)
63. 0.000 0.000 ↓ 0.0

Index Only Scan using clients_pkey on clients clients17 (cost=0.14..1.16 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = 60)