explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W5zn : pike_02Oct2019

Settings
# exclusive inclusive rows x rows loops node
1. 2.430 4,942,930.824 ↓ 320.0 320 1

Sort (cost=91,649.33..91,649.33 rows=1 width=3,399) (actual time=4,942,930.810..4,942,930.824 rows=320 loops=1)

  • 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", ((ex.trackingnumber)::character varying(50)) COLLATE "en_US", ex.expensedate, ((ex.description)::character varying(255)) COLLATE "en_US", ((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", 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
  • Sort Method: quicksort Memory: 154kB
  • Buffers: shared hit=2,967,970,075
2.          

Initplan (for Sort)

3. 0.003 0.003 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
4. 0.005 0.005 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
5. 6.396 4,942,928.386 ↓ 320.0 320 1

Nested Loop Semi Join (cost=6.72..91,647.30 rows=1 width=3,399) (actual time=188.986..4,942,928.386 rows=320 loops=1)

  • Join Filter: (ex.userid = userlocation9.userid)
  • Buffers: shared hit=2,967,970,061
6. 4.026 4,942,912.673 ↓ 1,331.0 1,331 1

Nested Loop Left Join (cost=6.44..91,646.92 rows=1 width=393) (actual time=188.949..4,942,912.673 rows=1,331 loops=1)

  • Buffers: shared hit=2,967,966,093
7. 450.797 4,942,879.365 ↓ 1,331.0 1,331 1

Nested Loop Left Join (cost=5.88..91,646.24 rows=1 width=381) (actual time=188.896..4,942,879.365 rows=1,331 loops=1)

  • Join Filter: (ee.projectid = pj_1.id)
  • Rows Removed by Join Filter: 5,584,900
  • Buffers: shared hit=2,967,958,973
8. 8,399.091 4,941,534.136 ↓ 1,331.0 1,331 1

Nested Loop Left Join (cost=5.88..91,148.71 rows=1 width=327) (actual time=188.768..4,941,534.136 rows=1,331 loops=1)

  • Join Filter: (ex.id = "*SELECT* 1".expenseid)
  • Rows Removed by Join Filter: 98,660,382
  • Buffers: shared hit=2,967,770,269
9. 2.887 124.281 ↓ 1,331.0 1,331 1

Nested Loop (cost=1.85..2,192.92 rows=1 width=277) (actual time=7.775..124.281 rows=1,331 loops=1)

  • Join Filter: (ex.reimbursementcurrencyid = currencyinfo5.id)
  • Rows Removed by Join Filter: 7,986
  • Buffers: shared hit=27,009
10. 3.834 120.063 ↓ 1,331.0 1,331 1

Nested Loop (cost=1.85..2,191.77 rows=1 width=282) (actual time=7.773..120.063 rows=1,331 loops=1)

  • Join Filter: (ee.currencyid = currencyinfo4.id)
  • Rows Removed by Join Filter: 7,986
  • Buffers: shared hit=25,678
11. 7.407 113.567 ↓ 1,331.0 1,331 1

Nested Loop (cost=1.85..2,190.61 rows=1 width=283) (actual time=7.768..113.567 rows=1,331 loops=1)

  • Join Filter: (ee.expensetypeid = expensetype3.id)
  • Rows Removed by Join Filter: 21,379
  • Buffers: shared hit=24,347
12. 8.830 102.167 ↓ 1,331.0 1,331 1

Nested Loop (cost=1.85..2,189.11 rows=1 width=275) (actual time=7.762..102.167 rows=1,331 loops=1)

  • Join Filter: (ex.userid = ui.id)
  • Buffers: shared hit=23,016
13. 6.302 86.682 ↓ 1,331.0 1,331 1

Nested Loop (cost=1.57..2,188.78 rows=1 width=256) (actual time=7.752..86.682 rows=1,331 loops=1)

  • Buffers: shared hit=19,023
14. 6.442 73.725 ↓ 1,331.0 1,331 1

Nested Loop (cost=1.29..2,186.48 rows=1 width=238) (actual time=7.742..73.725 rows=1,331 loops=1)

  • Join Filter: (ex.id = elf.expenseid)
  • Buffers: shared hit=15,030
15. 4.467 57.966 ↓ 1,331.0 1,331 1

Nested Loop Left Join (cost=0.99..2,186.14 rows=1 width=238) (actual time=7.732..57.966 rows=1,331 loops=1)

  • Buffers: shared hit=11,037
16. 2.895 21.555 ↓ 1,331.0 1,331 1

Nested Loop (cost=0.42..2,185.49 rows=1 width=214) (actual time=7.705..21.555 rows=1,331 loops=1)

  • Buffers: shared hit=2,789
17. 1.407 11.928 ↓ 306.0 306 1

Nested Loop (cost=0.00..2,182.80 rows=1 width=46) (actual time=7.691..11.928 rows=306 loops=1)

  • Join Filter: ((ex.expensedate >= exchangerate.effectivedate) AND (ex.expensedate <= exchangerate.enddate) AND (ex.reimbursementcurrencyid = exchangerate.fixedcurrencyid))
  • Rows Removed by Join Filter: 1,836
  • Buffers: shared hit=1,370
18. 8.073 8.073 ↓ 306.0 306 1

Seq Scan on expense ex (cost=0.00..2,181.07 rows=1 width=42) (actual time=7.678..8.073 rows=306 loops=1)

  • Filter: ((expensedate >= '2019-09-23'::date) AND (expensedate <= '2019-09-29'::date))
  • Rows Removed by Filter: 74,116
  • Buffers: shared hit=1,063
19. 2.448 2.448 ↑ 1.0 7 306

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

  • Filter: (variablecurrencyid = $0)
  • Rows Removed by Filter: 42
  • Buffers: shared hit=307
20. 6.732 6.732 ↑ 1.0 4 306

Index Scan using ixeeexpenseid on expenseentry ee (cost=0.42..2.65 rows=4 width=168) (actual time=0.008..0.022 rows=4 loops=306)

  • Index Cond: (expenseid = ex.id)
  • 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[]))
  • Buffers: shared hit=1,419
21. 6.655 31.944 ↑ 1.0 1 1,331

Nested Loop (cost=0.57..0.64 rows=1 width=44) (actual time=0.019..0.024 rows=1 loops=1,331)

  • Join Filter: ((ee.entrydate >= pc.effectivedate) AND (ee.entrydate <= pc.enddate))
  • Buffers: shared hit=8,248
22. 10.648 10.648 ↑ 1.0 1 1,331

Index Only Scan using project_pkey on project pj (cost=0.29..0.30 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=1,331)

  • Index Cond: (id = ee.projectid)
  • Heap Fetches: 1,407
  • Buffers: shared hit=4,047
23. 14.641 14.641 ↑ 1.0 1 1,331

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

  • Index Cond: (projectid = pj.id)
  • Buffers: shared hit=4,201
24. 9.317 9.317 ↑ 1.0 1 1,331

Index Scan using dm_expenselist_facts_pkey on dm_expenselist_facts elf (cost=0.29..0.33 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1,331)

  • Index Cond: (expenseid = ee.expenseid)
  • Filter: (expensestatus = ANY ('{0,1,2,3}'::integer[]))
  • Buffers: shared hit=3,993
25. 6.655 6.655 ↑ 1.0 1 1,331

Index Scan using login_pkey on login (cost=0.28..2.30 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=1,331)

  • Index Cond: (userid = ex.userid)
  • Buffers: shared hit=3,993
26. 6.655 6.655 ↑ 1.0 1 1,331

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..0.32 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=1,331)

  • Index Cond: (id = login.userid)
  • Buffers: shared hit=3,993
27. 3.993 3.993 ↑ 1.3 17 1,331

Seq Scan on expensetype expensetype3 (cost=0.00..1.22 rows=22 width=16) (actual time=0.002..0.003 rows=17 loops=1,331)

  • Buffers: shared hit=1,331
28. 2.662 2.662 ↑ 1.0 7 1,331

Seq Scan on currencyinfo currencyinfo4 (cost=0.00..1.07 rows=7 width=7) (actual time=0.001..0.002 rows=7 loops=1,331)

  • Buffers: shared hit=1,331
29. 1.331 1.331 ↑ 1.0 7 1,331

Seq Scan on currencyinfo currencyinfo5 (cost=0.00..1.07 rows=7 width=7) (actual time=0.000..0.001 rows=7 loops=1,331)

  • Buffers: shared hit=1,331
30. 9,116.019 4,933,010.764 ↓ 3.1 74,126 1,331

Append (cost=4.02..88,656.90 rows=23,911 width=58) (actual time=0.013..3,706.244 rows=74,126 loops=1,331)

  • Buffers: shared hit=2,967,743,260
31. 5.324 122.452 ↑ 1.1 28 1,331

Subquery Scan on *SELECT* 1 (cost=4.02..60.43 rows=30 width=58) (actual time=0.012..0.092 rows=28 loops=1,331)

  • Buffers: shared hit=115,803
32. 15.956 117.128 ↑ 1.1 28 1,331

Hash Join (cost=4.02..60.13 rows=30 width=651) (actual time=0.012..0.088 rows=28 loops=1,331)

  • Hash Cond: (ear.nodeid = ean.id)
  • Buffers: shared hit=115,803
33. 14.641 101.156 ↑ 1.1 28 1,331

Nested Loop (cost=0.28..56.30 rows=30 width=30) (actual time=0.010..0.076 rows=28 loops=1,331)

  • Buffers: shared hit=115,797
34. 11.979 11.979 ↑ 1.1 28 1,331

Seq Scan on expenseapprovalrequest ear (cost=0.00..3.30 rows=30 width=20) (actual time=0.003..0.009 rows=28 loops=1,331)

  • Buffers: shared hit=3,993
35. 74.536 74.536 ↑ 1.0 1 37,268

Index Scan using userinfo_pkey on userinfo ui_1 (cost=0.28..1.77 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=37,268)

  • Index Cond: (id = ear.approverid)
  • Buffers: shared hit=111,804
36. 0.008 0.016 ↓ 1.1 37 1

Hash (cost=3.33..3.33 rows=33 width=20) (actual time=0.016..0.016 rows=37 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=3
37. 0.008 0.008 ↓ 1.1 37 1

Seq Scan on expenseapprovalnodes ean (cost=0.00..3.33 rows=33 width=20) (actual time=0.002..0.008 rows=37 loops=1)

  • Buffers: shared hit=3
38. 11,735.427 4,923,772.293 ↓ 3.1 74,098 1,331

Subquery Scan on *SELECT* 2 (cost=8,122.33..88,596.47 rows=23,881 width=58) (actual time=1.914..3,699.303 rows=74,098 loops=1,331)

  • Buffers: shared hit=2,967,627,457
39. 25,093.343 4,912,036.866 ↓ 3.1 74,098 1,331

Gather (cost=8,122.33..88,357.66 rows=23,881 width=823) (actual time=1.914..3,690.486 rows=74,098 loops=1,331)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=2,967,627,457
40. 18,294.595 4,886,943.523 ↓ 2.6 37,049 2,662 / 2

Nested Loop Left Join (cost=7,122.33..84,944.98 rows=14,048 width=823) (actual time=88.517..3,671.633 rows=37,049 loops=2,662)

  • Buffers: shared hit=2,967,627,454
41. 60,501.575 3,290,657.920 ↓ 2.6 37,049 2,662 / 2

Hash Left Join (cost=7,121.91..60,153.87 rows=14,048 width=54) (actual time=88.482..2,472.320 rows=37,049 loops=2,662)

  • Hash Cond: ((split_part(effective.uri, ':'::text, 5))::integer = ui_2.id)
  • Buffers: shared hit=2,033,136,347
42. 21,072.392 3,228,789.047 ↓ 2.6 37,049 2,662 / 2

Nested Loop Left Join (cost=6,869.33..59,859.12 rows=14,048 width=100) (actual time=87.441..2,425.837 rows=37,049 loops=2,662)

  • Buffers: shared hit=2,032,975,175
43. 24,724.656 1,728,350.085 ↓ 2.6 37,049 2,662 / 2

Nested Loop Left Join (cost=6,868.90..35,103.13 rows=14,048 width=40) (actual time=87.409..1,298.535 rows=37,049 loops=2,662)

  • Buffers: shared hit=1,098,484,068
44. 29,011.184 174,946.640 ↓ 2.6 37,049 2,662 / 2

Hash Join (cost=6,868.47..10,347.15 rows=14,048 width=40) (actual time=87.335..131.440 rows=37,049 loops=2,662)

  • Hash Cond: (eh.id = "ANY_subquery".id)
  • Buffers: shared hit=163,991,630
45. 29,760.414 29,867.640 ↓ 1.7 47,716 2,662 / 2

Parallel Index Scan using ixexhaction on exphistory eh (cost=7.63..3,256.27 rows=28,095 width=40) (actual time=0.124..22.440 rows=47,716 loops=2,662)

  • Index Cond: (action = ANY ('{2,3,6}'::integer[]))
  • Filter: (NOT (hashed SubPlan 3))
  • Rows Removed by Filter: 4
  • Buffers: shared hit=419,367
46.          

SubPlan (for Parallel Index Scan)

47. 25.308 107.226 ↑ 1.1 28 1,332 / 2

Hash Join (cost=3.74..7.13 rows=30 width=4) (actual time=0.145..0.161 rows=28 loops=1,332)

  • Hash Cond: (ear_1.nodeid = ean_1.id)
  • Buffers: shared hit=21,302
48. 38.628 38.628 ↑ 1.1 28 1,332 / 2

Seq Scan on expenseapprovalrequest ear_1 (cost=0.00..3.30 rows=30 width=16) (actual time=0.048..0.058 rows=28 loops=1,332)

  • Buffers: shared hit=3,996
49. 7.326 43.290 ↓ 1.1 37 1,332 / 2

Hash (cost=3.33..3.33 rows=33 width=20) (actual time=0.065..0.065 rows=37 loops=1,332)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=3,996
50. 35.964 35.964 ↓ 1.1 37 1,332 / 2

Seq Scan on expenseapprovalnodes ean_1 (cost=0.00..3.33 rows=33 width=20) (actual time=0.045..0.054 rows=37 loops=1,332)

  • Buffers: shared hit=3,996
51. 6,926.400 116,067.816 ↓ 370.9 74,173 1,332 / 2

Hash (cost=6,858.35..6,858.35 rows=200 width=4) (actual time=174.276..174.276 rows=74,173 loops=1,332)

  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3,632kB
  • Buffers: shared hit=163,572,263
52. 22,237.740 109,141.416 ↓ 370.9 74,173 1,332 / 2

HashAggregate (cost=6,856.35..6,858.35 rows=200 width=4) (actual time=152.406..163.876 rows=74,173 loops=1,332)

  • Group Key: "ANY_subquery".id
  • Buffers: shared hit=163,572,263
53. 5,331.330 86,903.676 ↑ 1.0 74,173 1,332 / 2

Subquery Scan on ANY_subquery (cost=0.42..6,670.67 rows=74,269 width=4) (actual time=0.069..130.486 rows=74,173 loops=1,332)

  • Buffers: shared hit=163,572,263
54. 15,120.198 81,572.346 ↑ 1.0 74,173 1,332 / 2

Unique (cost=0.42..5,927.98 rows=74,269 width=8) (actual time=0.069..122.481 rows=74,173 loops=1,332)

  • Buffers: shared hit=163,572,263
55. 66,452.148 66,452.148 ↑ 1.0 205,822 1,332 / 2

Index Only Scan using ix2exhexpenseidid on exphistory (cost=0.42..5,413.05 rows=205,975 width=8) (actual time=0.068..99.778 rows=205,822 loops=1,332)

  • Heap Fetches: 205,861
  • Buffers: shared hit=163,572,263
56. 1,528,678.789 1,528,678.789 ↑ 1.0 1 98,624,438 / 2

Index Scan using ixexahkvexphistoryid on expenseapprovalhistorykeyvalue actual (cost=0.43..1.75 rows=1 width=64) (actual time=0.008..0.031 rows=1 loops=98,624,438)

  • Index Cond: (exphistoryid = eh.id)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-ACTUAL-USER'::text)
  • Rows Removed by Filter: 16
  • Buffers: shared hit=934,492,438
57. 1,479,366.570 1,479,366.570 ↑ 1.0 1 98,624,438 / 2

Index Scan using ixexahkvexphistoryid on expenseapprovalhistorykeyvalue effective (cost=0.43..1.75 rows=1 width=64) (actual time=0.005..0.030 rows=1 loops=98,624,438)

  • Index Cond: (exphistoryid = eh.id)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-EFFECTIVE-USER'::text)
  • Rows Removed by Filter: 16
  • Buffers: shared hit=934,491,107
58. 679.986 1,367.298 ↑ 1.0 5,787 1,332 / 2

Hash (cost=179.48..179.48 rows=5,848 width=18) (actual time=2.052..2.053 rows=5,787 loops=1,332)

  • Buckets: 8,192 Batches: 1 Memory Usage: 360kB
  • Buffers: shared hit=161,172
59. 687.312 687.312 ↑ 1.0 5,787 1,332 / 2

Seq Scan on userinfo ui_2 (cost=0.00..179.48 rows=5,848 width=18) (actual time=0.066..1.032 rows=5,787 loops=1,332)

  • Buffers: shared hit=161,172
60. 1,577,991.008 1,577,991.008 ↑ 1.0 1 98,624,438 / 2

Index Scan using ixexahkvexphistoryid on expenseapprovalhistorykeyvalue kvar (cost=0.43..1.75 rows=1 width=4) (actual time=0.009..0.032 rows=1 loops=98,624,438)

  • Index Cond: (exphistoryid = eh.id)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:COMPLETED-APPROVAL-REQUEST-APPROVAL-AGENT-DESCRIPTION-APPROVER-ROLE'::text)
  • Rows Removed by Filter: 16
  • Buffers: shared hit=934,491,107
61. 894.432 894.432 ↑ 2.1 4,197 1,331

Seq Scan on project pj_1 (cost=0.00..386.68 rows=8,868 width=58) (actual time=0.003..0.672 rows=4,197 loops=1,331)

  • Buffers: shared hit=188,704
62. 14.078 29.282 ↑ 1.0 1 1,331

Nested Loop Left Join (cost=0.57..0.67 rows=1 width=16) (actual time=0.022..0.022 rows=1 loops=1,331)

  • Buffers: shared hit=7,120
63. 7.986 7.986 ↑ 1.0 1 1,331

Index Scan using task_pkey on task tk (cost=0.42..0.48 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=1,331)

  • Index Cond: (ee.taskid = id)
  • Buffers: shared hit=4,869
64. 7.218 7.218 ↑ 1.0 1 1,203

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

  • Index Cond: ((variablecurrencyid = $1) AND (tk.estimatedcostcurrencyid = fixedcurrencyid) AND (('now'::cstring)::date >= effectivedate))
  • Filter: (('now'::cstring)::date <= enddate)
  • Buffers: shared hit=2,250
65. 9.317 9.317 ↓ 0.0 0 1,331

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

  • Index Cond: ((userid = ui.id) AND (startdate <= '2019-10-02'::date) AND (enddate >= '2019-10-02'::date))
  • Filter: (locationid = ANY ('{3c0de1be-c61b-4893-b080-5b2879103e97,f13f81bb-504c-4163-9646-af4be056fea1}'::uuid[]))
  • Rows Removed by Filter: 1
  • Heap Fetches: 1,282
  • Buffers: shared hit=3,968
Planning time : 15.799 ms
Execution time : 4,942,932.670 ms