explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fRZB

Settings
# exclusive inclusive rows x rows loops node
1. 5.215 1,251,550.563 ↓ 833.0 833 1

Sort (cost=214,873.51..214,873.51 rows=1 width=6,868) (actual time=1,251,550.504..1,251,550.563 rows=833 loops=1)

  • Sort Key: ((ex.trackingnumber)::character varying(50)) COLLATE "en_US", ((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", ((expensetype3.name)::character varying(50)) COLLATE "en_US", ((ex.description)::character varying(255)) COLLATE "en_US", ee.entrydate, elf.expensestatus, ((pj_1.name)::character varying(255)) COLLATE "en_US", ((pj_1.code)::character varying(50)) COLLATE "en_US", ex.paid, expensereceipts6.expenseentryid, ((ee.info2)::character varying(255)) COLLATE "en_US", ((paymentmethod7.name)::character varying(50)) COLLATE "en_US", ((ee.info4)::character varying(255)) COLLATE "en_US", ((ee.info5)::character varying(255)) COLLATE "en_US", ex.expensedate, projectstatuslabel8.projectstatustype, ((expensetype3.description)::character varying(255)) COLLATE "en_US", ((ee.description)::character varying(255)) COLLATE "en_US", ((userinfo9.displayname)::text) COLLATE "en_US", (("*SELECT* 1".approverlastname)::character varying(50)) COLLATE "en_US", (("*SELECT* 1".approverfirstname)::character varying(50)) COLLATE "en_US", "*SELECT* 1".effectivedateutc, ((ui.info9)::character varying(255)) COLLATE "en_US", ((cl.name)::character varying(255)) COLLATE "en_US", ex.submittedonutc
  • Sort Method: quicksort Memory: 458kB
  • Buffers: shared hit=7929897 read=27209
2.          

Initplan (for Sort)

3. 1.188 1.188 ↑ 1.0 1 1

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

  • Buffers: shared read=1
4. 10.650 1,251,544.160 ↓ 833.0 833 1

Nested Loop Left Join (cost=4,243.18..214,872.49 rows=1 width=6,868) (actual time=25,506.364..1,251,544.160 rows=833 loops=1)

  • Buffers: shared hit=7929883 read=27209
5. 11,303.159 1,251,526.013 ↓ 833.0 833 1

Nested Loop Left Join (cost=4,243.04..214,872.31 rows=1 width=737) (actual time=25,506.330..1,251,526.013 rows=833 loops=1)

  • Join Filter: (ex.id = "*SELECT* 1".expenseid)
  • Rows Removed by Join Filter: 112883827
  • Buffers: shared hit=7928218 read=27208
6. 4.624 2,197.429 ↓ 833.0 833 1

Nested Loop Left Join (cost=3,782.64..8,126.32 rows=1 width=720) (actual time=1,618.723..2,197.429 rows=833 loops=1)

  • Buffers: shared hit=637868 read=2793
7. 3.234 2,190.306 ↓ 833.0 833 1

Nested Loop Semi Join (cost=3,782.36..8,125.95 rows=1 width=709) (actual time=1,618.711..2,190.306 rows=833 loops=1)

  • Buffers: shared hit=635375 read=2793
8. 4.943 2,077.116 ↓ 833.0 833 1

Nested Loop (cost=4.89..4,201.12 rows=1 width=717) (actual time=1,615.246..2,077.116 rows=833 loops=1)

  • Buffers: shared hit=624773 read=2768
9. 6.678 2,057.575 ↓ 1,216.5 2,433 1

Nested Loop Left Join (cost=4.60..4,200.50 rows=2 width=721) (actual time=1,615.227..2,057.575 rows=2,433 loops=1)

  • Buffers: shared hit=617470 read=2768
10. 2.105 2,033.866 ↓ 1,216.5 2,433 1

Nested Loop Left Join (cost=4.32..4,199.77 rows=2 width=717) (actual time=1,615.204..2,033.866 rows=2,433 loops=1)

  • Join Filter: (pj_1.projectstatuslabelid = projectstatuslabel8.id)
  • Rows Removed by Join Filter: 2454
  • Buffers: shared hit=608647 read=2765
11. 2.913 2,029.328 ↓ 1,216.5 2,433 1

Nested Loop Left Join (cost=4.32..4,198.47 rows=2 width=729) (actual time=1,614.049..2,029.328 rows=2,433 loops=1)

  • Join Filter: (ee.paymentmethodid = paymentmethod7.id)
  • Rows Removed by Join Filter: 6985
  • Buffers: shared hit=608647 read=2764
12. 5.514 2,023.982 ↓ 1,216.5 2,433 1

Nested Loop Left Join (cost=4.32..4,197.26 rows=2 width=615) (actual time=1,613.242..2,023.982 rows=2,433 loops=1)

  • Buffers: shared hit=608647 read=2763
13. 7.427 1,913.849 ↓ 1,216.5 2,433 1

Nested Loop Left Join (cost=3.90..4,196.28 rows=2 width=611) (actual time=1,612.297..1,913.849 rows=2,433 loops=1)

  • Buffers: shared hit=601021 read=2678
14. 3.453 1,899.123 ↓ 1,216.5 2,433 1

Nested Loop (cost=3.61..4,195.60 rows=2 width=563) (actual time=1,612.278..1,899.123 rows=2,433 loops=1)

  • Join Filter: (ee.currencyid = currencyinfo4.id)
  • Rows Removed by Join Filter: 14598
  • Buffers: shared hit=593722 read=2678
15. 0.007 0.007 ↑ 1.0 7 1

Seq Scan on currencyinfo currencyinfo4 (cost=0.00..1.07 rows=7 width=122) (actual time=0.004..0.007 rows=7 loops=1)

  • Buffers: shared hit=1
16. 6.091 1,895.663 ↓ 1,216.5 2,433 7

Materialize (cost=3.61..4,194.32 rows=2 width=449) (actual time=230.325..270.809 rows=2,433 loops=7)

  • Buffers: shared hit=593721 read=2678
17. 203.580 1,889.572 ↓ 1,216.5 2,433 1

Nested Loop (cost=3.61..4,194.31 rows=2 width=449) (actual time=1,612.263..1,889.572 rows=2,433 loops=1)

  • Join Filter: (ee.expensetypeid = expensetype3.id)
  • Rows Removed by Join Filter: 1637409
  • Buffers: shared hit=593721 read=2678
18. 6.384 6.384 ↑ 1.0 674 1

Seq Scan on expensetype expensetype3 (cost=0.00..14.74 rows=674 width=43) (actual time=0.004..6.384 rows=674 loops=1)

  • Buffers: shared hit=2 read=6
19. 96.609 1,679.608 ↓ 1,216.5 2,433 674

Materialize (cost=3.61..4,159.36 rows=2 width=414) (actual time=0.126..2.492 rows=2,433 loops=674)

  • Buffers: shared hit=593719 read=2672
20. 3.389 1,582.999 ↓ 1,216.5 2,433 1

Nested Loop (cost=3.61..4,159.35 rows=2 width=414) (actual time=85.161..1,582.999 rows=2,433 loops=1)

  • Buffers: shared hit=593719 read=2672
21. 2.869 1,569.878 ↓ 1,216.5 2,433 1

Nested Loop (cost=3.33..4,158.75 rows=2 width=402) (actual time=85.150..1,569.878 rows=2,433 loops=1)

  • Buffers: shared hit=586405 read=2665
22. 2.515 1,562.143 ↓ 1,216.5 2,433 1

Nested Loop Left Join (cost=3.04..4,158.14 rows=2 width=373) (actual time=85.135..1,562.143 rows=2,433 loops=1)

  • Buffers: shared hit=579106 read=2665
23. 2.660 1,520.700 ↓ 1,216.5 2,433 1

Nested Loop (cost=2.46..4,156.77 rows=2 width=337) (actual time=85.090..1,520.700 rows=2,433 loops=1)

  • Buffers: shared hit=564530 read=2642
24. 110.546 475.891 ↓ 455.3 3,187 1

Nested Loop (cost=2.04..4,141.36 rows=7 width=60) (actual time=58.716..475.891 rows=3,187 loops=1)

  • Buffers: shared hit=552619 read=1699
25. 49.526 89.771 ↓ 441.6 137,787 1

Hash Join (cost=1.62..3,992.38 rows=312 width=52) (actual time=2.201..89.771 rows=137,787 loops=1)

  • Hash Cond: (ex.reimbursementcurrencyid = exchangerate.fixedcurrencyid)
  • Join Filter: ((ex.expensedate >= exchangerate.effectivedate) AND (ex.expensedate <= exchangerate.enddate))
  • Buffers: shared hit=357 read=1699
26. 38.058 38.058 ↑ 1.0 137,787 1

Seq Scan on expense ex (cost=0.00..3,431.87 rows=137,787 width=56) (actual time=0.004..38.058 rows=137,787 loops=1)

  • Buffers: shared hit=357 read=1697
27. 0.005 2.187 ↓ 7.0 7 1

Hash (cost=1.61..1.61 rows=1 width=12) (actual time=2.186..2.187 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=2
28. 2.182 2.182 ↓ 7.0 7 1

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

  • Filter: (variablecurrencyid = $0)
  • Rows Removed by Filter: 42
  • Buffers: shared read=2
29. 275.574 275.574 ↓ 0.0 0 137,787

Index Scan using dm_expenselist_facts_pkey on dm_expenselist_facts elf (cost=0.42..0.48 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=137,787)

  • Index Cond: (expenseid = ex.id)
  • Filter: (expensestatus = ANY ('{0,3}'::integer[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=552262
30. 1,042.149 1,042.149 ↑ 1.0 1 3,187

Index Scan using ixeeexpenseid on expenseentry ee (cost=0.42..2.19 rows=1 width=277) (actual time=0.319..0.327 rows=1 loops=3,187)

  • Index Cond: (expenseid = ex.id)
  • Filter: ((entrydate >= '2019-01-01'::date) AND (entrydate <= '2020-05-12'::date) AND (expensetypeid = ANY ('{35,36,37,38,39,40,66,41,42,43,24,67,418,44,45,825,830,25,826,824,65,829,827,828,26,27,28,29,30,32,33,34,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,821,393,768,301,302,306,313,314,413,394,85,84,457,458,137,138,459,424,460,269,392,270,461,462,279,463,288,464,524,259,260,330,331,128,415,465,466,241,223,224,164,351,395,420,359,494,495,496,366,367,727,368,369,378,147,396,181,182,397,398,624,625,626,627,628,629,630,631,632,633,733,634,635,636,379,307,308,106,637,114,638,86,315,639,388,191,640,94,641,68,76,642,643,120,139,644,645,233,271,649,650,280,651,652,653,654,289,324,295,250,726,261,332,333,334,129,655,656,657,242,215,225,165,100,660,352,360,661,662,663,728,370,148,664,156,173,183,725,666,667,380,309,107,668,115,669,87,316,670,321,192,671,95,672,69,77,673,674,121,140,675,676,234,272,678,679,281,680,681,682,683,290,325,296,251,262,723,335,336,337,130,684,685,243,686,216,226,166,101,688,353,361,689,690,691,371,149,692,157,174,184,694,695,381,310,108,116,88,317,322,193,96,70,78,467,468,122,141,469,470,235,423,273,471,472,282,473,399,326,291,297,252,263,338,339,340,131,244,217,227,167,102,354,386,497,498,499,729,372,150,158,422,400,175,185,401,559,560,612,696,562,563,564,565,566,697,769,567,568,569,613,698,558,722,770,699,700,570,701,614,615,702,730,703,571,704,705,588,589,509,510,506,507,523,522,521,519,518,517,516,514,513,512,508,515,721,511,572,584,585,382,402,109,117,89,318,323,194,97,71,79,123,142,236,274,283,292,327,298,254,264,341,342,132,403,245,218,228,168,103,355,362,373,151,159,404,176,186,405,383,110,118,90,319,389,195,98,72,80,124,143,237,275,284,293,328,299,255,265,343,344,133,246,219,229,169,104,356,363,374,152,160,177,187,777,384,111,417,603,119,573,91,619,320,390,196,99,575,73,81,586,587,125,416,144,577,578,238,421,414,276,579,580,285,581,618,583,294,329,300,256,266,345,346,347,134,247,220,230,170,105,617,357,364,616,375,153,161,178,188,303,311,312,74,82,474,476,126,145,475,477,239,277,478,479,286,480,481,493,257,267,348,349,135,482,483,248,221,231,171,358,365,500,501,502,376,162,154,406,179,189,407,304,305,408,409,83,75,484,485,146,127,487,486,240,278,488,489,287,490,491,525,268,258,350,136,410,492,249,232,222,172,385,387,503,504,505,731,377,163,155,411,190,180,412,706,707,620,708,709,710,592,593,594,595,596,711,767,597,598,599,621,712,600,724,713,714,601,715,622,766,623,716,717,732,718,602,719,720,779,1,820,782,783,784,785,419,786,15,2,31,20,787,788,789,790,18,21,791,792,818,3,793,19,822,8,794,795,16,23,823,17,796,4,10,815,7,798,799,800,801,5,802,22,14,6,819,13,817,12,804,806,805,807,808,809,780,781,9,11,811,812,813}'::integer[])))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=11911 read=943
31. 2.433 38.928 ↑ 1.0 1 2,433

Nested Loop (cost=0.58..0.68 rows=1 width=48) (actual time=0.016..0.016 rows=1 loops=2,433)

  • Join Filter: ((ee.entrydate >= pc.effectivedate) AND (ee.entrydate <= pc.enddate))
  • Buffers: shared hit=14576 read=23
32. 4.866 4.866 ↑ 1.0 1 2,433

Index Only Scan using project_pkey on project pj (cost=0.29..0.34 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2,433)

  • Index Cond: (id = ee.projectid)
  • Heap Fetches: 2433
  • Buffers: shared hit=7300
33. 31.629 31.629 ↑ 1.0 1 2,433

Index Scan using uix4pc_projectclienteffectiveend on projectclient pc (cost=0.29..0.32 rows=1 width=48) (actual time=0.013..0.013 rows=1 loops=2,433)

  • Index Cond: (projectid = pj.id)
  • Buffers: shared hit=7276 read=23
34. 4.866 4.866 ↑ 1.0 1 2,433

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..0.30 rows=1 width=29) (actual time=0.002..0.002 rows=1 loops=2,433)

  • Index Cond: (id = ex.userid)
  • Buffers: shared hit=7299
35. 9.732 9.732 ↑ 1.0 1 2,433

Index Scan using login_pkey on login (cost=0.28..0.30 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=2,433)

  • Index Cond: (userid = ex.userid)
  • Buffers: shared hit=7314 read=7
36. 7.299 7.299 ↑ 1.0 1 2,433

Index Scan using project_pkey on project pj_1 (cost=0.29..0.34 rows=1 width=52) (actual time=0.003..0.003 rows=1 loops=2,433)

  • Index Cond: (ee.projectid = id)
  • Buffers: shared hit=7299
37. 104.619 104.619 ↓ 0.0 0 2,433

Index Only Scan using expensereceipts_pkey on expensereceipts expensereceipts6 (cost=0.42..0.49 rows=1 width=4) (actual time=0.043..0.043 rows=0 loops=2,433)

  • Index Cond: (expenseentryid = ee.id)
  • Heap Fetches: 392
  • Buffers: shared hit=7626 read=85
38. 1.634 2.433 ↑ 1.2 4 2,433

Materialize (cost=0.00..1.07 rows=5 width=122) (actual time=0.001..0.001 rows=4 loops=2,433)

  • Buffers: shared read=1
39. 0.799 0.799 ↑ 1.0 5 1

Seq Scan on paymentmethod paymentmethod7 (cost=0.00..1.05 rows=5 width=122) (actual time=0.797..0.799 rows=5 loops=1)

  • Buffers: shared read=1
40. 1.285 2.433 ↑ 3.5 2 2,433

Materialize (cost=0.00..1.10 rows=7 width=20) (actual time=0.001..0.001 rows=2 loops=2,433)

  • Buffers: shared read=1
41. 1.148 1.148 ↑ 2.3 3 1

Seq Scan on projectstatuslabel projectstatuslabel8 (cost=0.00..1.07 rows=7 width=20) (actual time=1.148..1.148 rows=3 loops=1)

  • Buffers: shared read=1
42. 17.031 17.031 ↑ 1.0 1 2,433

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy12 (cost=0.29..0.36 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=2,433)

  • Index Cond: ((ui.id = userid) AND ('2020-05-12'::date >= startdate))
  • Filter: ('2020-05-12'::date <= enddate)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=8823 read=3
43. 14.598 14.598 ↓ 0.0 0 2,433

Index Scan using userinfo_pkey on userinfo ui_1 (cost=0.28..0.31 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=2,433)

  • Index Cond: (id = ex.userid)
  • Filter: (upper((info5)::text) = 'ROSS KRUCHTEN'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=7303
44. 43.328 109.956 ↑ 17.0 1 833

Nested Loop Left Join (cost=3,777.47..3,924.81 rows=17 width=4) (actual time=0.132..0.132 rows=1 loops=833)

  • Filter: ((expense14.userid = 1266) OR ((userhierarchy15.supervisorid = 1266) AND ((userhierarchy15.startdate IS NULL) OR (userhierarchy15.startdate <= '2020-05-12'::date)) AND ((userhierarchy15.enddate IS NULL) OR (userhierarchy15.enddate >= '2020-05-12'::date))) OR (alternatives: SubPlan 2 or hashed SubPlan 3) OR (exphistory16.userid = 1266) OR (hashed SubPlan 4))
  • Buffers: shared hit=10602 read=25
45. 5.831 44.149 ↑ 5.0 1 833

Nested Loop Left Join (cost=0.84..1.10 rows=5 width=12) (actual time=0.053..0.053 rows=1 loops=833)

  • Buffers: shared hit=5986 read=25
46. 32.487 32.487 ↑ 1.0 1 833

Index Scan using expense_pkey on expense expense14 (cost=0.42..0.46 rows=1 width=8) (actual time=0.039..0.039 rows=1 loops=833)

  • Index Cond: (id = ex.id)
  • Buffers: shared hit=3314 read=24
47. 5.831 5.831 ↓ 0.0 0 833

Index Scan using ix2exhexpenseidid on exphistory exphistory16 (cost=0.42..0.59 rows=6 width=8) (actual time=0.007..0.007 rows=0 loops=833)

  • Index Cond: (expenseid = expense14.id)
  • Buffers: shared hit=2672 read=1
48. 1.666 1.666 ↑ 2.0 1 833

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy15 (cost=0.29..0.32 rows=2 width=24) (actual time=0.002..0.002 rows=1 loops=833)

  • Index Cond: (userid = expense14.userid)
  • Buffers: shared hit=2499
49.          

SubPlan (for Nested Loop Left Join)

50. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..14.55 rows=1 width=0) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Index Scan using ixeanexpenseid on expenseapprovalnodes expenseapprovalnodes18 (cost=0.28..5.35 rows=4 width=16) (never executed)

  • Index Cond: (expenseid = expense14.id)
52. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix2earnidaid on expenseapprovalrequest expenseapprovalrequest17 (cost=0.28..2.30 rows=1 width=16) (never executed)

  • Index Cond: ((nodeid = expenseapprovalnodes18.id) AND (approverid = 1266))
  • Heap Fetches: 0
53. 0.621 1.343 ↑ 1.0 408 1

Hash Join (cost=40.65..159.86 rows=408 width=4) (actual time=0.267..1.343 rows=408 loops=1)

  • Hash Cond: (expenseapprovalnodes18_1.id = expenseapprovalrequest17_1.nodeid)
  • Buffers: shared hit=63
54. 0.504 0.504 ↑ 1.0 5,956 1

Seq Scan on expenseapprovalnodes expenseapprovalnodes18_1 (cost=0.00..103.56 rows=5,956 width=20) (actual time=0.006..0.504 rows=5,956 loops=1)

  • Buffers: shared hit=44
55. 0.058 0.218 ↑ 1.0 408 1

Hash (cost=35.55..35.55 rows=408 width=16) (actual time=0.218..0.218 rows=408 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
  • Buffers: shared hit=16
56. 0.160 0.160 ↑ 1.0 408 1

Seq Scan on expenseapprovalrequest expenseapprovalrequest17_1 (cost=0.00..35.55 rows=408 width=16) (actual time=0.006..0.160 rows=408 loops=1)

  • Filter: (approverid = 1266)
  • Rows Removed by Filter: 1156
  • Buffers: shared hit=16
57. 19.470 19.470 ↑ 1.0 137,787 1

Seq Scan on expense expense19 (cost=0.00..3,431.87 rows=137,787 width=4) (actual time=0.008..19.470 rows=137,787 loops=1)

  • Buffers: shared hit=2054
58. 2.499 2.499 ↑ 1.0 1 833

Index Scan using userinfo_pkey on userinfo userinfo9 (cost=0.28..0.36 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=833)

  • Index Cond: (userhierarchy12.supervisorid = id)
  • Buffers: shared hit=2493
59. 11,738.636 1,238,025.425 ↑ 1.0 135,515 833

Append (cost=460.40..205,012.46 rows=138,682 width=25) (actual time=0.017..1,486.225 rows=135,515 loops=833)

  • Buffers: shared hit=7290350 read=24415
60. 153.272 1,684.326 ↑ 1.0 1,564 833

Subquery Scan on *SELECT* 1 (cost=460.40..621.68 rows=1,564 width=25) (actual time=0.017..2.022 rows=1,564 loops=833)

  • Buffers: shared hit=36963
61. 299.732 1,531.054 ↑ 1.0 1,564 833

Hash Join (cost=460.40..606.04 rows=1,564 width=650) (actual time=0.016..1.838 rows=1,564 loops=833)

  • Hash Cond: (ear.approverid = ui_2.id)
  • Buffers: shared hit=36963
62. 777.583 1,229.508 ↑ 1.0 1,564 833

Hash Join (cost=51.19..192.73 rows=1,564 width=8) (actual time=0.011..1.476 rows=1,564 loops=833)

  • Hash Cond: (ean.id = ear.nodeid)
  • Buffers: shared hit=36668
63. 451.486 451.486 ↑ 1.0 5,956 833

Seq Scan on expenseapprovalnodes ean (cost=0.00..103.56 rows=5,956 width=20) (actual time=0.007..0.542 rows=5,956 loops=833)

  • Buffers: shared hit=36652
64. 0.232 0.439 ↑ 1.0 1,564 1

Hash (cost=31.64..31.64 rows=1,564 width=20) (actual time=0.439..0.439 rows=1,564 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 96kB
  • Buffers: shared hit=16
65. 0.207 0.207 ↑ 1.0 1,564 1

Seq Scan on expenseapprovalrequest ear (cost=0.00..31.64 rows=1,564 width=20) (actual time=0.005..0.207 rows=1,564 loops=1)

  • Buffers: shared hit=16
66. 0.900 1.814 ↑ 1.0 5,076 1

Hash (cost=345.76..345.76 rows=5,076 width=17) (actual time=1.814..1.814 rows=5,076 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 321kB
  • Buffers: shared hit=295
67. 0.914 0.914 ↑ 1.0 5,076 1

Seq Scan on userinfo ui_2 (cost=0.00..345.76 rows=5,076 width=17) (actual time=0.005..0.914 rows=5,076 loops=1)

  • Buffers: shared hit=295
68. 13,991.901 1,224,602.463 ↑ 1.0 133,951 833

Subquery Scan on *SELECT* 2 (cost=151,370.51..204,390.78 rows=137,118 width=25) (actual time=27.862..1,470.111 rows=133,951 loops=833)

  • Buffers: shared hit=7253387 read=24415
69. 171,200.143 1,210,610.562 ↑ 1.0 133,951 833

Hash Right Join (cost=151,370.51..203,019.60 rows=137,118 width=822) (actual time=27.862..1,453.314 rows=133,951 loops=833)

  • Hash Cond: (effective.exphistoryid = eh.id)
  • Buffers: shared hit=7253387 read=24415
70. 749,075.897 1,016,384.950 ↓ 13.3 723,802 833

Hash Left Join (cost=409.77..51,751.77 rows=54,480 width=17) (actual time=0.062..1,220.150 rows=723,802 loops=833)

  • Hash Cond: ((split_part(effective.uri, ':'::text, 5))::integer = ui_3.id)
  • Buffers: shared hit=6940851
71. 267,307.201 267,307.201 ↓ 13.3 723,802 833

Index Scan using ixexahkvkey on expenseapprovalhistorykeyvalue effective (cost=0.56..51,178.96 rows=54,480 width=68) (actual time=0.053..320.897 rows=723,802 loops=833)

  • Index Cond: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-EFFECTIVE-USER'::text)
  • Buffers: shared hit=6940556
72. 0.897 1.852 ↑ 1.0 5,076 1

Hash (cost=345.76..345.76 rows=5,076 width=17) (actual time=1.852..1.852 rows=5,076 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 321kB
  • Buffers: shared hit=295
73. 0.955 0.955 ↑ 1.0 5,076 1

Seq Scan on userinfo ui_3 (cost=0.00..345.76 rows=5,076 width=17) (actual time=0.008..0.955 rows=5,076 loops=1)

  • Buffers: shared hit=295
74. 43.836 23,025.469 ↑ 1.0 133,951 1

Hash (cost=149,246.77..149,246.77 rows=137,118 width=16) (actual time=23,025.469..23,025.469 rows=133,951 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 8851kB
  • Buffers: shared hit=312536 read=24415
75. 182.215 22,981.633 ↑ 1.0 133,951 1

Hash Right Join (cost=97,761.28..149,246.77 rows=137,118 width=16) (actual time=7,686.197..22,981.633 rows=133,951 loops=1)

  • Hash Cond: (kvar.exphistoryid = eh.id)
  • Buffers: shared hit=312536 read=24415
76. 15,117.966 15,117.966 ↓ 9.5 516,372 1

Index Scan using ixexahkvkey on expenseapprovalhistorykeyvalue kvar (cost=0.56..51,178.96 rows=54,480 width=4) (actual time=1.085..15,117.966 rows=516,372 loops=1)

  • Index Cond: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:COMPLETED-APPROVAL-REQUEST-APPROVAL-AGENT-DESCRIPTION-APPROVER-ROLE'::text)
  • Buffers: shared hit=18 read=15655
77. 39.011 7,681.452 ↑ 1.0 133,951 1

Hash (cost=96,046.74..96,046.74 rows=137,118 width=16) (actual time=7,681.452..7,681.452 rows=133,951 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 8851kB
  • Buffers: shared hit=312518 read=8760
78. 207.525 7,642.441 ↑ 1.0 133,951 1

Hash Right Join (cost=44,561.25..96,046.74 rows=137,118 width=16) (actual time=2,435.570..7,642.441 rows=133,951 loops=1)

  • Hash Cond: (actual.exphistoryid = eh.id)
  • Buffers: shared hit=312518 read=8760
79. 5,002.080 5,002.080 ↓ 12.7 692,127 1

Index Scan using ixexahkvkey on expenseapprovalhistorykeyvalue actual (cost=0.56..51,178.96 rows=54,480 width=68) (actual time=1.749..5,002.080 rows=692,127 loops=1)

  • Index Cond: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-ACTUAL-USER'::text)
  • Buffers: shared hit=8149 read=7241
80. 35.960 2,432.836 ↑ 1.0 133,951 1

Hash (cost=42,846.72..42,846.72 rows=137,118 width=16) (actual time=2,432.836..2,432.836 rows=133,951 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 8851kB
  • Buffers: shared hit=304369 read=1519
81. 151.240 2,396.876 ↑ 1.0 133,951 1

Hash Join (cost=22,823.56..42,846.72 rows=137,118 width=16) (actual time=519.965..2,396.876 rows=133,951 loops=1)

  • Hash Cond: (eh.id = "ANY_subquery".id)
  • Buffers: shared hit=304369 read=1519
82. 1,727.955 1,747.179 ↓ 2.0 545,636 1

Index Scan using ixexhaction on exphistory eh (cost=247.14..18,024.98 rows=274,236 width=16) (actual time=20.387..1,747.179 rows=545,636 loops=1)

  • Index Cond: (action = ANY ('{2,3,6}'::integer[]))
  • Filter: (NOT (hashed SubPlan 5))
  • Rows Removed by Filter: 3317
  • Buffers: shared hit=6212 read=1513
83.          

SubPlan (for Index Scan)

84. 0.889 19.224 ↑ 1.0 1,564 1

Merge Join (cost=0.68..242.80 rows=1,564 width=4) (actual time=0.077..19.224 rows=1,564 loops=1)

  • Merge Cond: (ear_1.nodeid = ean_1.id)
  • Buffers: shared hit=6191 read=13
85. 8.100 8.100 ↑ 1.0 1,564 1

Index Only Scan using uix2earnidaid on expenseapprovalrequest ear_1 (cost=0.28..49.74 rows=1,564 width=16) (actual time=0.047..8.100 rows=1,564 loops=1)

  • Heap Fetches: 1564
  • Buffers: shared hit=1350 read=6
86. 10.235 10.235 ↑ 1.0 5,956 1

Index Scan using expenseapprovalnodes_pkey on expenseapprovalnodes ean_1 (cost=0.28..158.62 rows=5,956 width=20) (actual time=0.024..10.235 rows=5,956 loops=1)

  • Buffers: shared hit=4841 read=7
87. 19.839 498.457 ↓ 677.3 135,454 1

Hash (cost=22,573.93..22,573.93 rows=200 width=4) (actual time=498.456..498.457 rows=135,454 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 6811kB
  • Buffers: shared hit=298157 read=6
88. 73.204 478.618 ↓ 677.3 135,454 1

HashAggregate (cost=22,571.93..22,573.93 rows=200 width=4) (actual time=450.276..478.618 rows=135,454 loops=1)

  • Group Key: "ANY_subquery".id
  • Buffers: shared hit=298157 read=6
89. 15.705 405.414 ↓ 1.0 135,454 1

Subquery Scan on ANY_subquery (cost=0.42..22,242.11 rows=131,928 width=4) (actual time=0.056..405.414 rows=135,454 loops=1)

  • Buffers: shared hit=298157 read=6
90. 60.610 389.709 ↓ 1.0 135,454 1

Unique (cost=0.42..20,922.83 rows=131,928 width=8) (actual time=0.055..389.709 rows=135,454 loops=1)

  • Buffers: shared hit=298157 read=6
91. 329.099 329.099 ↑ 1.0 726,766 1

Index Only Scan using ix2exhexpenseidid on exphistory (cost=0.42..19,105.91 rows=726,766 width=8) (actual time=0.054..329.099 rows=726,766 loops=1)

  • Heap Fetches: 726766
  • Buffers: shared hit=298157 read=6
92. 7.497 7.497 ↑ 1.0 1 833

Index Scan using clients_pkey on clients cl (cost=0.15..0.17 rows=1 width=22) (actual time=0.009..0.009 rows=1 loops=833)

  • Index Cond: (pc.clientid = id)
  • Buffers: shared hit=1665 read=1
Planning time : 228.134 ms
Execution time : 1,251,556.343 ms