explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c3ok

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

Sort (cost=102,741.30..102,741.31 rows=1 width=2,449) (actual rows= loops=)

  • Sort Key: ((ui.externalid)::character varying(255)) 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.displayname)::text) COLLATE "en_US", ((pj_1.code)::character varying(50)) COLLATE "en_US", ((tk.code)::character varying(50)) COLLATE "en_US", ((expensetype4.name)::character varying(50)) COLLATE "en_US", ((ex.description)::character varying(255)) COLLATE "en_US", ee.entrydate, elf.expensestatus, (("*SELECT* 1".approvalcomments)::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

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

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=7.42..102,739.27 rows=1 width=2,449) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=7.14..102,738.93 rows=1 width=321) (actual rows= loops=)

  • Join Filter: (ee.currencyid = currencyinfo6.id)
7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=7.14..102,737.77 rows=1 width=322) (actual rows= loops=)

  • Join Filter: (ee.expensetypeid = expensetype4.id)
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=7.14..102,736.27 rows=1 width=314) (actual rows= loops=)

  • Join Filter: (tk.estimatedcostcurrencyid = exchangerate_1.fixedcurrencyid)
9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=7.14..102,733.84 rows=1 width=318) (actual rows= loops=)

  • Join Filter: (ex.id = "*SELECT* 1".expenseid)
10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3.12..17.93 rows=1 width=290) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.69..15.49 rows=1 width=274) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.41..13.19 rows=1 width=261) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.12..12.88 rows=1 width=243) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.84..12.58 rows=1 width=194) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.43..12.08 rows=1 width=194) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..10.09 rows=1 width=162) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.71..9.57 rows=3 width=174) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Index Scan using ixexeentrydate on expenseentry ee (cost=0.42..2.64 rows=3 width=139) (actual rows= loops=)

  • Index Cond: ((entrydate >= '2020-10-05'::date) AND (entrydate <= '2020-10-11'::date))
  • Filter: (expensetypeid = ANY ('{1,2,14,16,3,4,5,19,6,10,26,7,22,8,13,23,15,24,9,21,25}'::integer[]))
19. 0.000 0.000 ↓ 0.0

Index Scan using expense_pkey on expense ex (cost=0.29..2.31 rows=1 width=35) (actual rows= loops=)

  • Index Cond: (id = ee.expenseid)
20. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((variablecurrencyid = $0) AND (fixedcurrencyid = ex.reimbursementcurrencyid) AND (ex.expensedate >= effectivedate))
  • Filter: (ex.expensedate <= enddate)
21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1.98 rows=1 width=44) (actual rows= loops=)

  • Join Filter: ((ee.entrydate >= pc.effectivedate) AND (ee.entrydate <= pc.enddate))
22. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = ee.projectid)
23. 0.000 0.000 ↓ 0.0

Index Scan using uix4pc_projectclienteffectiveend on projectclient pc (cost=0.29..0.33 rows=1 width=44) (actual rows= loops=)

  • Index Cond: (projectid = pj.id)
24. 0.000 0.000 ↓ 0.0

Index Scan using dm_expenselist_facts_pkey on dm_expenselist_facts elf (cost=0.42..0.50 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (expenseid = ex.id)
  • Filter: (expensestatus = ANY ('{1,2}'::integer[]))
25. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = ex.userid)
26. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (userid = ex.userid)
27. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project pj_1 (cost=0.29..2.30 rows=1 width=17) (actual rows= loops=)

  • Index Cond: (ee.projectid = id)
28. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (ee.taskid = id)
29. 0.000 0.000 ↓ 0.0

Append (cost=4.02..102,350.48 rows=29,234 width=36) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=4.02..77.98 rows=41 width=36) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash Join (cost=4.02..77.57 rows=41 width=905) (actual rows= loops=)

  • Hash Cond: (ear.nodeid = ean.id)
32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..73.71 rows=41 width=16) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on expenseapprovalrequest ear (cost=0.00..3.41 rows=41 width=20) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Index Only Scan using userinfo_pkey on userinfo ui_1 (cost=0.28..1.71 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = ear.approverid)
35. 0.000 0.000 ↓ 0.0

Hash (cost=3.33..3.33 rows=33 width=20) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Seq Scan on expenseapprovalnodes ean (cost=0.00..3.33 rows=33 width=20) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=7,296.73..102,272.50 rows=29,193 width=36) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Gather (cost=7,296.73..101,980.57 rows=29,193 width=1,077) (actual rows= loops=)

  • Workers Planned: 1
39. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6,296.73..98,031.22 rows=17,172 width=1,077) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6,296.30..68,723.92 rows=16,988 width=32) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6,295.87..39,629.42 rows=16,988 width=32) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash Join (cost=6,295.44..10,534.93 rows=16,988 width=32) (actual rows= loops=)

  • Hash Cond: (eh.id = "ANY_subquery".id)
43. 0.000 0.000 ↓ 0.0

Parallel Index Scan using ixexhaction on exphistory eh (cost=7.79..3,969.11 rows=33,975 width=32) (actual rows= loops=)

  • Index Cond: (action = ANY ('{2,3,6}'::integer[]))
  • Filter: (NOT (hashed SubPlan 3))
44.          

SubPlan (for Parallel Index Scan)

45. 0.000 0.000 ↓ 0.0

Hash Join (cost=3.74..7.27 rows=41 width=4) (actual rows= loops=)

  • Hash Cond: (ear_1.nodeid = ean_1.id)
46. 0.000 0.000 ↓ 0.0

Seq Scan on expenseapprovalrequest ear_1 (cost=0.00..3.41 rows=41 width=16) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Hash (cost=3.33..3.33 rows=33 width=20) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Seq Scan on expenseapprovalnodes ean_1 (cost=0.00..3.33 rows=33 width=20) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Hash (cost=6,285.15..6,285.15 rows=200 width=4) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

HashAggregate (cost=6,283.15..6,285.15 rows=200 width=4) (actual rows= loops=)

  • Group Key: "ANY_subquery".id
51. 0.000 0.000 ↓ 0.0

Subquery Scan on ANY_subquery (cost=0.42..6,061.07 rows=88,831 width=4) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Unique (cost=0.42..5,172.76 rows=88,831 width=8) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Index Only Scan using ix2exhexpenseidid on exphistory (cost=0.42..4,548.28 rows=249,791 width=8) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Index Scan using ixexahkvexphistoryid on expenseapprovalhistorykeyvalue actual (cost=0.43..1.70 rows=1 width=64) (actual rows= loops=)

  • Index Cond: (exphistoryid = eh.id)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-ACTUAL-USER'::text)
55. 0.000 0.000 ↓ 0.0

Index Scan using ixexahkvexphistoryid on expenseapprovalhistorykeyvalue kvar (cost=0.43..1.70 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (exphistoryid = eh.id)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:COMPLETED-APPROVAL-REQUEST-APPROVAL-AGENT-DESCRIPTION-APPROVER-ROLE'::text)
56. 0.000 0.000 ↓ 0.0

Index Scan using ixexahkvexphistoryid on expenseapprovalhistorykeyvalue effective (cost=0.43..1.70 rows=2 width=64) (actual rows= loops=)

  • Index Cond: (exphistoryid = eh.id)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-EFFECTIVE-USER'::text)
57. 0.000 0.000 ↓ 0.0

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

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

Seq Scan on expensetype expensetype4 (cost=0.00..1.22 rows=22 width=16) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Seq Scan on currencyinfo currencyinfo6 (cost=0.00..1.07 rows=7 width=7) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Index Only Scan using departmentusers_pkey on departmentusers departmentusers8 (cost=0.28..0.30 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((departmentid = 227) AND (userid = ex.userid))