explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7oP

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

Sort (cost=105,756.16..105,756.16 rows=1 width=2,947) (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", ((ex.trackingnumber)::character varying(50)) COLLATE "en_US", ex.expensedate, ((ee.description)::character varying(255)) COLLATE "en_US", ((expensetype3.name)::character varying(50)) COLLATE "en_US", ee.billtoclient, ee.requestreimbursement, (("*SELECT* 1".approverlastname)::character varying(50)) COLLATE "en_US", (("*SELECT* 1".approverfirstname)::character varying(50)) COLLATE "en_US", (("*SELECT* 1".approverdisplayname)::text) COLLATE "en_US", elf.expensestatus, "*SELECT* 1".effectivedateutc, (("*SELECT* 1".approvalcomments)::text) COLLATE "en_US", ((pj_1.code)::character varying(50)) COLLATE "en_US", ((pj_1.name)::character varying(255)) COLLATE "en_US", ((tk.code)::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

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 Semi Join (cost=7.14..105,754.13 rows=1 width=2,947) (actual rows= loops=)

  • Join Filter: (ex.userid = userlocation9.userid)
6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6.86..105,753.76 rows=1 width=412) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6.29..105,752.95 rows=1 width=400) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6.00..105,752.64 rows=1 width=346) (actual rows= loops=)

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

Nested Loop (cost=1.98..2,643.52 rows=1 width=279) (actual rows= loops=)

  • Join Filter: (ex.reimbursementcurrencyid = currencyinfo5.id)
10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.98..2,642.36 rows=1 width=284) (actual rows= loops=)

  • Join Filter: (ee.currencyid = currencyinfo4.id)
11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.98..2,641.21 rows=1 width=285) (actual rows= loops=)

  • Join Filter: (ee.expensetypeid = expensetype3.id)
12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.98..2,639.71 rows=1 width=277) (actual rows= loops=)

  • Join Filter: (ex.userid = login.userid)
13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.69..2,639.37 rows=1 width=259) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.41..2,637.07 rows=1 width=223) (actual rows= loops=)

  • Join Filter: (ex.id = elf.expenseid)
15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.99..2,636.59 rows=1 width=223) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..2,635.94 rows=1 width=199) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..2,633.27 rows=1 width=31) (actual rows= loops=)

  • Join Filter: ((ex.expensedate >= exchangerate.effectivedate) AND (ex.expensedate <= exchangerate.enddate) AND (ex.reimbursementcurrencyid = exchangerate.fixedcurrencyid))
18. 0.000 0.000 ↓ 0.0

Seq Scan on expense ex (cost=0.00..2,631.53 rows=1 width=27) (actual rows= loops=)

  • Filter: ((expensedate >= '2020-10-05'::date) AND (expensedate <= '2020-10-11'::date))
19. 0.000 0.000 ↓ 0.0

Seq Scan on exchangerate (cost=0.00..1.61 rows=7 width=12) (actual rows= loops=)

  • Filter: (variablecurrencyid = $0)
20. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (expenseid = ex.id)
  • Filter: (expensetypeid = ANY ('{1,2,14,16,3,4,5,19,6,10,26,7,22,8,18,28,27,13,23,15,24,9,21,25}'::integer[]))
21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..0.64 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..0.30 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.46 rows=1 width=8) (actual rows= loops=)

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

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..2.30 rows=1 width=36) (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.33 rows=1 width=18) (actual rows= loops=)

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

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

28. 0.000 0.000 ↓ 0.0

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

29. 0.000 0.000 ↓ 0.0

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

30. 0.000 0.000 ↓ 0.0

Append (cost=4.02..102,743.87 rows=29,220 width=75) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=4.02..53.50 rows=24 width=75) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash Join (cost=4.02..53.26 rows=24 width=668) (actual rows= loops=)

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

Nested Loop (cost=0.28..49.44 rows=24 width=47) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

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

35. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo ui_1 (cost=0.28..1.92 rows=1 width=35) (actual rows= loops=)

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

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

37. 0.000 0.000 ↓ 0.0

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

38. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=7,658.17..102,690.38 rows=29,196 width=75) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Gather (cost=7,658.17..102,398.42 rows=29,196 width=840) (actual rows= loops=)

  • Workers Planned: 1
40. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=6,658.17..98,448.76 rows=17,174 width=840) (actual rows= loops=)

  • Hash Cond: ((split_part(effective.uri, ':'::text, 5))::integer = ui_2.id)
41. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6,296.65..97,992.74 rows=17,174 width=96) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6,296.22..68,726.86 rows=16,988 width=40) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6,295.79..39,630.86 rows=16,988 width=40) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Hash Join (cost=6,295.36..10,534.85 rows=16,988 width=40) (actual rows= loops=)

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

Parallel Index Scan using ixexhaction on exphistory eh (cost=7.71..3,969.03 rows=33,975 width=40) (actual rows= loops=)

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

SubPlan (for Parallel Index Scan)

47. 0.000 0.000 ↓ 0.0

Hash Join (cost=3.54..7.23 rows=24 width=4) (actual rows= loops=)

  • Hash Cond: (ean_1.id = ear_1.nodeid)
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=3.24..3.24 rows=24 width=16) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

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

51. 0.000 0.000 ↓ 0.0

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

52. 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
53. 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=)

54. 0.000 0.000 ↓ 0.0

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

55. 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=)

56. 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)
57. 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)
58. 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)
59. 0.000 0.000 ↓ 0.0

Hash (cost=276.23..276.23 rows=6,823 width=35) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui_2 (cost=0.00..276.23 rows=6,823 width=35) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

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

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

Nested Loop Left Join (cost=0.57..0.79 rows=1 width=16) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

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

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

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

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

Index Only Scan using ix4ul_userlocationstartend on userlocation userlocation9 (cost=0.28..0.32 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((userid = ui.id) AND (locationid = '3c0de1be-c61b-4893-b080-5b2879103e97'::uuid) AND (startdate <= '2020-10-13'::date) AND (enddate >= '2020-10-13'::date))