explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vMTg

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

Sort (cost=1,186,353,617.58..1,186,353,617.70 rows=47 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.21..1,186,353,615.26 rows=47 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.70..1,186,353,612.10 rows=47 width=207) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,006.41..1,186,353,596.53 rows=47 width=178) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,006.13..1,186,353,582.49 rows=47 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..141.18 rows=2,660 width=63) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Materialize (cost=1,005.85..1,186,351,566.12 rows=47 width=87) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,005.85..1,186,351,565.89 rows=47 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.29..1,186,351,472.09 rows=47 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,002.99..1,186,351,337.92 rows=50,035 width=60) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.68..1,186,335,250.51 rows=50,035 width=105) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.13..1,186,241,947.56 rows=46,870 width=52) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,001.84..1,186,227,470.37 rows=46,870 width=101) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Append (cost=1,001.28..1,186,134,167.42 rows=46,870 width=48) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=1,001.28..1,186,083,540.50 rows=46,868 width=48) (actual rows= loops=)

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

Nested Loop (cost=1,001.28..134,494.59 rows=93,735 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.28..131,915.87 rows=93,735 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.28..130,977.51 rows=93,735 width=60) (actual rows= loops=)

  • Workers Planned: 2
23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.28..120,604.01 rows=39,056 width=60) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.85..62,481.24 rows=39,056 width=60) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Parallel Index Scan using ixte2entrydate on timeentry te (cost=0.42..4,358.47 rows=39,056 width=60) (actual rows= loops=)

  • Index Cond: ((entrydate >= '2020-02-28'::date) AND (entrydate <= '2020-08-08'::date))
  • Filter: ((timeallocationtype <> 2) OR (timeallocationtype IS NULL))
26. 0.000 0.000 ↓ 0.0

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

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

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

  • Index Cond: (timeentryid = te.id)
  • Filter: (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..23,278.66 rows=810,244 width=16) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on timeentry timeentry10 (cost=0.00..15,270.44 rows=810,244 width=16) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=0.29..50,626.92 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-02-28'::date) AND (entrydate <= '2020-08-08'::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..1.98 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.31 rows=1 width=4) (actual rows= loops=)

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

Index Scan using ixtem2timeentryid on timeentrymetadata timeentrymetadata7 (cost=0.56..1.98 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:fbbaabb1ad8841ab91b04373a06b8c61:task:(.*)$'::text) ~ '^\s*[-]?[0-9]+\s*$'::text) THEN ("substring"(timeentrymetadata7.uri, '^urn:replicon-tenant:fbbaabb1ad8841ab91b04373a06b8c61: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 = 3)
41. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2timeentryid on timeentrymetadata timeentrymetadata9 (cost=0.56..1.98 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)