explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mqfe

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

Sort (cost=776,990,633.88..776,990,633.93 rows=20 width=2,385) (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", ((ui.email)::character varying(255)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", "*SELECT* 1".entrydate, "*SELECT* 1".timeentryid, ui.id, tk.id
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

Hash Left Join (cost=1,009.45..776,990,632.44 rows=20 width=2,385) (actual rows= loops=)

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

Nested Loop Left Join (cost=1,006.94..776,990,629.64 rows=20 width=207) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,006.65..776,990,622.98 rows=20 width=178) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,006.38..776,990,617.05 rows=20 width=150) (actual rows= loops=)

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

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..103.44 rows=1,944 width=63) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Materialize (cost=1,006.10..776,989,930.46 rows=20 width=87) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,006.10..776,989,930.36 rows=20 width=87) (actual rows= loops=)

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

Hash Join (cost=1,005.54..776,989,885.11 rows=20 width=95) (actual rows= loops=)

  • Hash Cond: (COALESCE(project6.id, task8.projectid) = pj.id)
12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,003.24..776,989,816.85 rows=25,039 width=60) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.93..776,981,766.44 rows=25,039 width=105) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.38..776,928,304.47 rows=23,682 width=52) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.09..776,920,726.77 rows=23,682 width=101) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Append (cost=1,001.53..776,867,264.80 rows=23,682 width=48) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=1,001.53..776,801,649.37 rows=23,680 width=48) (actual rows= loops=)

  • Filter: (SubPlan 2)
18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,001.53..115,645.30 rows=47,361 width=140) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

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

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,001.53..114,341.86 rows=47,361 width=60) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

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

22. 0.000 0.000 ↓ 0.0

Gather (cost=1,001.53..113,867.24 rows=47,361 width=60) (actual rows= loops=)

  • Workers Planned: 2
23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.54..108,131.14 rows=19,734 width=60) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.98..68,211.41 rows=19,734 width=60) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Parallel Index Scan using timeentry_pkey on timeentry te (cost=0.42..28,291.66 rows=19,734 width=60) (actual rows= loops=)

  • Filter: (((timeallocationtype <> 2) OR (timeallocationtype IS NULL)) AND (entrydate >= '2020-06-24'::date) AND (entrydate <= '2020-08-07'::date))
26. 0.000 0.000 ↓ 0.0

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

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

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

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

SubPlan (for Subquery Scan)

29. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..30,268.31 rows=1,012,087 width=16) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on timeentry timeentry10 (cost=0.00..20,265.87 rows=1,012,087 width=16) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=0.29..65,615.43 rows=2 width=48) (actual rows= loops=)

  • Filter: (SubPlan 2)
32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..18.33 rows=4 width=140) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Index Scan using uix2toe_timeoffidentrydate on timeoffentries toe (cost=0.15..12.59 rows=4 width=28) (actual rows= loops=)

  • Index Cond: ((entrydate >= '2020-06-24'::date) AND (entrydate <= '2020-08-07'::date))
34. 0.000 0.000 ↓ 0.0

Index Scan using timeoffs_pkey on timeoffs toff (cost=0.14..1.41 rows=1 width=8) (actual rows= loops=)

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

Index Scan using ixtem2timeentryid on timeentrymetadata timeentrymetadata5 (cost=0.56..2.25 rows=1 width=69) (actual rows= loops=)

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

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

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

Index Scan using ixtem2timeentryid on timeentrymetadata timeentrymetadata7 (cost=0.56..2.25 rows=1 width=69) (actual rows= loops=)

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

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

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

Hash (cost=2.29..2.29 rows=1 width=43) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = 2)
41. 0.000 0.000 ↓ 0.0

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

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

Index Scan using login_pkey on login (cost=0.28..0.30 rows=1 width=32) (actual rows= loops=)

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

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

  • Index Cond: (task8.id = id)
44. 0.000 0.000 ↓ 0.0

Hash (cost=2.40..2.40 rows=9 width=4) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Index Scan using uix3er_currencyideffectivedate on exchangerate (cost=0.15..2.40 rows=9 width=4) (actual rows= loops=)

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