explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mj2s

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

Sort (cost=101,683.52..101,683.52 rows=1 width=1,721) (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", ((expensetype2.name)::character varying(50)) COLLATE "en_US", ee.entrydate, elf.expensestatus, (("*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", ((location7.name)::character varying(100)) COLLATE "en_US", ex.expensedate
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 Left Join (cost=6.43..101,682.50 rows=1 width=1,721) (actual rows= loops=)

  • Join Filter: (userlocation8.locationid = location7.id)
5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6.43..101,679.53 rows=1 width=285) (actual rows= loops=)

  • Join Filter: (ui.id = userlocation8.userid)
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6.43..101,437.80 rows=1 width=269) (actual rows= loops=)

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

Nested Loop (cost=6.43..101,436.64 rows=1 width=274) (actual rows= loops=)

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

Nested Loop (cost=6.43..101,435.48 rows=1 width=275) (actual rows= loops=)

  • Join Filter: (ee.expensetypeid = expensetype2.id)
9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6.43..101,433.99 rows=1 width=267) (actual rows= loops=)

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

Nested Loop (cost=2.41..10.75 rows=1 width=241) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.12..10.45 rows=1 width=227) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.84..10.14 rows=1 width=192) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.43..9.64 rows=1 width=192) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..7.50 rows=1 width=164) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.71..7.15 rows=2 width=160) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Index Scan using ixexeentrydate on expenseentry ee (cost=0.42..2.53 rows=2 width=140) (actual rows= loops=)

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

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

  • Index Cond: (id = ee.expenseid)
18. 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)
19. 0.000 0.000 ↓ 0.0

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

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

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

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

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

  • Index Cond: (projectid = pj.id)
22. 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 ('{0,1,2,3}'::integer[]))
23. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = ex.userid)
24. 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)
25. 0.000 0.000 ↓ 0.0

Append (cost=4.02..101,065.15 rows=28,647 width=34) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=4.02..58.79 rows=28 width=34) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash Join (cost=4.02..58.51 rows=28 width=667) (actual rows= loops=)

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

Nested Loop (cost=0.28..54.68 rows=28 width=46) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

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

30. 0.000 0.000 ↓ 0.0

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

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

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

32. 0.000 0.000 ↓ 0.0

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

33. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=7,429.81..101,006.36 rows=28,619 width=34) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Gather (cost=7,429.81..100,720.17 rows=28,619 width=839) (actual rows= loops=)

  • Workers Planned: 1
35. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=6,429.81..96,858.27 rows=16,835 width=839) (actual rows= loops=)

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

Nested Loop Left Join (cost=6,147.39..96,525.30 rows=16,835 width=64) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6,146.96..67,675.72 rows=16,686 width=8) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6,146.53..38,993.00 rows=16,686 width=8) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash Join (cost=6,146.10..10,310.29 rows=16,686 width=8) (actual rows= loops=)

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

Parallel Index Scan using ixexhaction on exphistory eh (cost=7.60..3,898.55 rows=33,373 width=8) (actual rows= loops=)

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

SubPlan (for Parallel Index Scan)

42. 0.000 0.000 ↓ 0.0

Hash Join (cost=3.74..7.11 rows=28 width=4) (actual rows= loops=)

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

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

44. 0.000 0.000 ↓ 0.0

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

45. 0.000 0.000 ↓ 0.0

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

46. 0.000 0.000 ↓ 0.0

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

47. 0.000 0.000 ↓ 0.0

HashAggregate (cost=6,134.00..6,136.00 rows=200 width=4) (actual rows= loops=)

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

Subquery Scan on ANY_subquery (cost=0.42..5,915.86 rows=87,257 width=4) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Unique (cost=0.42..5,043.29 rows=87,257 width=8) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Index Only Scan using ix2exhexpenseidid on exphistory (cost=0.42..4,429.88 rows=245,364 width=8) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Index Scan using ixexahkvexphistoryid on expenseapprovalhistorykeyvalue actual (cost=0.43..1.71 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)
52. 0.000 0.000 ↓ 0.0

Index Scan using ixexahkvexphistoryid on expenseapprovalhistorykeyvalue kvar (cost=0.43..1.71 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)
53. 0.000 0.000 ↓ 0.0

Index Scan using ixexahkvexphistoryid on expenseapprovalhistorykeyvalue effective (cost=0.43..1.71 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)
54. 0.000 0.000 ↓ 0.0

Hash (cost=204.41..204.41 rows=6,241 width=34) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui_2 (cost=0.00..204.41 rows=6,241 width=34) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

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

57. 0.000 0.000 ↓ 0.0

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

58. 0.000 0.000 ↓ 0.0

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

59. 0.000 0.000 ↓ 0.0

Seq Scan on userlocation userlocation8 (cost=0.00..173.39 rows=5,467 width=20) (actual rows= loops=)

  • Filter: (('2020-09-08'::date >= startdate) AND ('2020-09-08'::date <= enddate))
60. 0.000 0.000 ↓ 0.0

Seq Scan on location location7 (cost=0.00..1.86 rows=86 width=234) (actual rows= loops=)