explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AamR

Settings
# exclusive inclusive rows x rows loops node
1. 33.409 35,286.514 ↑ 17.2 123,644 1

Unique (cost=25,360,752.32..25,403,215.62 rows=2,123,165 width=84) (actual time=35,236.274..35,286.514 rows=123,644 loops=1)

2.          

CTE runids

3. 0.002 0.002 ↑ 1.0 1 1

Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

4.          

CTE discounttext

5. 0.019 0.314 ↑ 2.5 2 1

HashAggregate (cost=36.67..36.72 rows=5 width=29) (actual time=0.313..0.314 rows=2 loops=1)

6. 0.295 0.295 ↑ 1.1 18 1

Seq Scan on invprestemplateline (cost=0.00..36.62 rows=20 width=29) (actual time=0.020..0.295 rows=18 loops=1)

  • Filter: ((text)::text ~~ '==DISCOUNT_LABELS;%'::text)
  • Rows Removed by Filter: 1105
7.          

CTE rateitemtext

8. 0.016 0.387 ↑ 1.5 2 1

HashAggregate (cost=36.66..36.69 rows=3 width=29) (actual time=0.386..0.387 rows=2 loops=1)

9. 0.371 0.371 ↑ 1.2 10 1

Seq Scan on invprestemplateline invprestemplateline_1 (cost=0.00..36.62 rows=12 width=29) (actual time=0.020..0.371 rows=10 loops=1)

  • Filter: ((text)::text ~~ '==RATEITEM_LABELS;%'::text)
  • Rows Removed by Filter: 1113
10.          

CTE translation

11. 0.789 4.655 ↑ 1.9 693 1

HashAggregate (cost=69.97..83.03 rows=1,306 width=98) (actual time=4.454..4.655 rows=693 loops=1)

12. 0.119 3.866 ↑ 1.9 694 1

Append (cost=0.00..60.17 rows=1,306 width=98) (actual time=0.428..3.866 rows=694 loops=1)

13. 1.319 1.916 ↑ 2.5 274 1

Nested Loop (cost=0.00..22.94 rows=685 width=120) (actual time=0.427..1.916 rows=274 loops=1)

14. 0.319 0.319 ↑ 2.5 2 1

CTE Scan on discounttext t (cost=0.00..0.10 rows=5 width=32) (actual time=0.316..0.319 rows=2 loops=1)

15. 0.101 0.278 ↑ 1.0 137 2

Materialize (cost=0.00..6.05 rows=137 width=88) (actual time=0.045..0.139 rows=137 loops=2)

16. 0.177 0.177 ↑ 1.0 137 1

Seq Scan on discount d (cost=0.00..5.37 rows=137 width=88) (actual time=0.086..0.177 rows=137 loops=1)

17. 0.993 1.831 ↑ 1.5 420 1

Nested Loop (cost=0.00..24.17 rows=621 width=74) (actual time=0.525..1.831 rows=420 loops=1)

18. 0.390 0.390 ↑ 1.5 2 1

CTE Scan on rateitemtext t_1 (cost=0.00..0.06 rows=3 width=32) (actual time=0.388..0.390 rows=2 loops=1)

19. 0.140 0.448 ↓ 1.0 210 2

Materialize (cost=0.00..9.11 rows=207 width=42) (actual time=0.062..0.224 rows=210 loops=2)

20. 0.308 0.308 ↓ 1.0 210 1

Seq Scan on rateitem r (cost=0.00..8.07 rows=207 width=42) (actual time=0.120..0.308 rows=210 loops=1)

21.          

CTE templatelineids

22. 193.668 217.099 ↓ 15.2 194,000 1

Hash Join (cost=49.05..502.78 rows=12,748 width=78) (actual time=2.420..217.099 rows=194,000 loops=1)

  • Hash Cond: (p.invprestemplateid = tl.invprestemplateid)
23. 1.945 22.461 ↓ 10.0 2,000 1

Nested Loop (cost=0.72..231.32 rows=201 width=12) (actual time=1.437..22.461 rows=2,000 loops=1)

24. 0.001 1.400 ↑ 1.0 1 1

Nested Loop (cost=0.29..8.34 rows=1 width=4) (actual time=1.396..1.400 rows=1 loops=1)

25. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on runids ri (cost=0.00..0.02 rows=1 width=4) (actual time=0.003..0.005 rows=1 loops=1)

26. 1.394 1.394 ↑ 1.0 1 1

Index Scan using invpresrun_invdocrunid_index on invpresrun d_1 (cost=0.29..8.31 rows=1 width=8) (actual time=1.392..1.394 rows=1 loops=1)

  • Index Cond: (invdocrunid = ri.invdocrunid)
27. 19.116 19.116 ↓ 1.3 2,000 1

Index Scan using invpres_invpresrunid_index on invpres p (cost=0.43..207.48 rows=1,550 width=16) (actual time=0.038..19.116 rows=2,000 loops=1)

  • Index Cond: (invpresrunid = d_1.invpresrunid)
28. 0.285 0.970 ↑ 1.0 1,123 1

Hash (cost=33.70..33.70 rows=1,170 width=74) (actual time=0.970..0.970 rows=1,123 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 100kB
29. 0.685 0.685 ↑ 1.0 1,123 1

Seq Scan on invprestemplateline tl (cost=0.00..33.70 rows=1,170 width=74) (actual time=0.028..0.685 rows=1,123 loops=1)

  • Filter: active
30.          

CTE templateids

31. 132.038 31,774.972 ↑ 5.2 125,644 1

Nested Loop (cost=0.57..16,157,659.00 rows=653,554 width=164) (actual time=106.495..31,774.972 rows=125,644 loops=1)

32. 602.934 602.934 ↓ 15.2 194,000 1

CTE Scan on templatelineids tl_1 (cost=0.00..254.96 rows=12,748 width=140) (actual time=2.421..602.934 rows=194,000 loops=1)

33. 31,040.000 31,040.000 ↑ 51.0 1 194,000

Index Scan using invdocline_invdocid_index on invdocline l (cost=0.57..1,266.94 rows=51 width=64) (actual time=0.111..0.160 rows=1 loops=194,000)

  • Index Cond: (invdocid = tl_1.invdocid)
  • Filter: ((resultset)::text ~~* tl_1.resultfilter2)
  • Rows Removed by Filter: 129
34.          

CTE invoicedinids

35. 0.001 412.167 ↓ 0.0 0 1

Nested Loop (cost=21.06..9,612.44 rows=16 width=29) (actual time=412.167..412.167 rows=0 loops=1)

36. 0.001 412.166 ↓ 0.0 0 1

Nested Loop (cost=20.63..9,546.57 rows=8 width=25) (actual time=412.166..412.166 rows=0 loops=1)

37. 0.000 412.165 ↓ 0.0 0 1

Nested Loop Left Join (cost=20.20..9,513.63 rows=4 width=17) (actual time=412.165..412.165 rows=0 loops=1)

38. 0.002 412.165 ↓ 0.0 0 1

Nested Loop Left Join (cost=19.78..9,481.26 rows=4 width=12) (actual time=412.165..412.165 rows=0 loops=1)

39. 0.000 412.163 ↓ 0.0 0 1

Nested Loop (cost=19.35..9,448.81 rows=4 width=12) (actual time=412.163..412.163 rows=0 loops=1)

40. 0.393 412.163 ↓ 0.0 0 1

Nested Loop (cost=18.92..9,416.31 rows=4 width=8) (actual time=412.163..412.163 rows=0 loops=1)

41. 0.356 405.770 ↓ 1.3 1,000 1

Nested Loop (cost=18.48..2,858.82 rows=781 width=8) (actual time=2.247..405.770 rows=1,000 loops=1)

42. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on runids ri_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.003 rows=1 loops=1)

43. 403.595 405.411 ↓ 1.3 1,000 1

Bitmap Heap Scan on invdoc d1 (cost=18.48..2,850.99 rows=781 width=12) (actual time=2.234..405.411 rows=1,000 loops=1)

  • Recheck Cond: (invdocrunid = ri_1.invdocrunid)
44. 1.816 1.816 ↓ 2.0 1,530 1

Bitmap Index Scan on invdoc_invdocrunid (cost=0.00..18.29 rows=781 width=0) (actual time=1.816..1.816 rows=1,530 loops=1)

  • Index Cond: (invdocrunid = ri_1.invdocrunid)
45. 6.000 6.000 ↓ 0.0 0 1,000

Index Scan using index_invoicerelation_finalinvoiceid on invoicerelation (cost=0.43..8.39 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=1,000)

  • Index Cond: (finalinvoiceid = d1.invoiceid)
  • Filter: ((nature)::text = 'INVOICED_IN'::text)
  • Rows Removed by Filter: 1
46. 0.000 0.000 ↓ 0.0 0

Index Scan using invdoc_invoiceid on invdoc d2 (cost=0.43..8.12 rows=1 width=8) (never executed)

  • Index Cond: (invoiceid = invoicerelation.originalinvoiceid)
47. 0.000 0.000 ↓ 0.0 0

Index Scan using invoice_pkey on invoice i2 (cost=0.43..8.10 rows=1 width=8) (never executed)

  • Index Cond: (d2.invoiceid = invoiceid)
48. 0.000 0.000 ↓ 0.0 0

Index Scan using contract_pkey on contract c2 (cost=0.42..8.08 rows=1 width=13) (never executed)

  • Index Cond: (i2.contractid = contractid)
49. 0.000 0.000 ↓ 0.0 0

Index Scan using invpres_invdocid on invpres p1 (cost=0.43..8.21 rows=2 width=12) (never executed)

  • Index Cond: (invdocid = d1.invdocid)
50. 0.000 0.000 ↓ 0.0 0

Index Scan using invpres_invdocid on invpres p2 (cost=0.43..8.21 rows=2 width=8) (never executed)

  • Index Cond: (invdocid = d2.invdocid)
51.          

CTE tempchildinvpresline

52. 352.444 33,520.564 ↑ 17.2 123,644 1

WindowAgg (cost=1,619,207.72..6,602,884.49 rows=2,122,972 width=287) (actual time=33,135.127..33,520.564 rows=123,644 loops=1)

53. 415.900 33,168.120 ↑ 17.2 123,644 1

Sort (cost=1,619,207.72..1,624,515.15 rows=2,122,972 width=287) (actual time=33,135.066..33,168.120 rows=123,644 loops=1)

  • Sort Key: tis.invpresid, tis.rank, tis.invdoclineid
  • Sort Method: external merge Disk: 10232kB
54. 45.153 32,752.220 ↑ 17.2 123,644 1

Hash Left Join (cost=122.77..264,124.27 rows=2,122,972 width=287) (actual time=244.682..32,752.220 rows=123,644 loops=1)

  • Hash Cond: ((tis.text)::text = (tr.text)::text)
  • Join Filter: CASE WHEN ((tis.text)::text ~~ '==DISCOUNT_LABELS;%'::text) THEN ((tr.code)::text = split_part(split_part((tis.resultset)::text, '['::text, 2), ']'::text, 1)) ELSE ((tr.code)::text = split_part(split_part((tis.resultset)::text, '.'::text, 2), '['::text, 1)) END
  • Rows Removed by Join Filter: 2352
55. 185.815 32,701.413 ↑ 5.3 123,644 1

Hash Left Join (cost=80.33..39,219.15 rows=650,221 width=255) (actual time=239.009..32,701.413 rows=123,644 loops=1)

  • Hash Cond: ((split_part((tis.resultset)::text, '.'::text, 2) = (param.parametercategorycode)::text) AND (split_part((tis.resultset)::text, '.'::text, 3) = (param.valuecode)::text))
  • Join Filter: ((tis.text)::text ~~ '==PARAMVALUE_LABELS%'::text)
56. 32,383.144 32,383.144 ↑ 5.3 123,644 1

CTE Scan on templateids tis (cost=0.00..16,338.85 rows=650,221 width=176) (actual time=106.511..32,383.144 rows=123,644 loops=1)

  • Filter: (((COALESCE(cssclass, ''::character varying))::text !~~* '==controlBalance%'::text) AND ((COALESCE(cssclass, ''::character varying))::text !~~* '@%'::text))
  • Rows Removed by Filter: 2000
57. 0.826 132.454 ↑ 1.2 998 1

Hash (cost=62.13..62.13 rows=1,213 width=108) (actual time=132.454..132.454 rows=998 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 134kB
58. 131.628 131.628 ↑ 1.2 998 1

Seq Scan on parametervalue param (cost=0.00..62.13 rows=1,213 width=108) (actual time=0.913..131.628 rows=998 loops=1)

59. 0.379 5.654 ↑ 1.9 693 1

Hash (cost=26.12..26.12 rows=1,306 width=96) (actual time=5.654..5.654 rows=693 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 75kB
60. 5.275 5.275 ↑ 1.9 693 1

CTE Scan on translation tr (cost=0.00..26.12 rows=1,306 width=96) (actual time=4.458..5.275 rows=693 loops=1)

61.          

SubPlan (forWindowAgg)

62. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..2.27 rows=1 width=32) (never executed)

63. 0.000 0.000 ↓ 0.0 0

Subquery Scan on x_1 (cost=0.00..2.27 rows=1 width=32) (never executed)

  • Filter: ((x_1.vatrate)::numeric = tis.vatrate)
64. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.52 rows=100 width=0) (never executed)

65.          

CTE tempparentinvpresline

66. 0.002 412.223 ↓ 0.0 0 1

WindowAgg (cost=53,654.24..53,659.44 rows=189 width=116) (actual time=412.223..412.223 rows=0 loops=1)

67. 0.006 412.221 ↓ 0.0 0 1

Sort (cost=53,654.24..53,654.72 rows=189 width=116) (actual time=412.221..412.221 rows=0 loops=1)

  • Sort Key: p_1.originalinvpresid, l_1.invdoclineid
  • Sort Method: quicksort Memory: 25kB
68. 0.000 412.215 ↓ 0.0 0 1

Hash Join (cost=40.16..53,647.10 rows=189 width=116) (actual time=412.215..412.215 rows=0 loops=1)

  • Hash Cond: ((p_1.invprestemplateid = tl_2.invprestemplateid) AND ((l_1.cssclass)::text = replace((COALESCE(tl_2.cssclass, ''::character varying))::text, '@'::text, ''::text)))
69. 0.011 412.215 ↓ 0.0 0 1

Hash Join (cost=0.52..52,119.49 rows=169,838 width=132) (actual time=412.215..412.215 rows=0 loops=1)

  • Hash Cond: (l_1.invpresid = p_1.originalinvpresid)
70. 0.034 0.034 ↑ 2,122,972.0 1 1

CTE Scan on tempchildinvpresline l_1 (cost=0.00..42,459.44 rows=2,122,972 width=92) (actual time=0.034..0.034 rows=1 loops=1)

71. 0.001 412.170 ↓ 0.0 0 1

Hash (cost=0.32..0.32 rows=16 width=44) (actual time=412.170..412.170 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
72. 412.169 412.169 ↓ 0.0 0 1

CTE Scan on invoicedinids p_1 (cost=0.00..0.32 rows=16 width=44) (actual time=412.169..412.169 rows=0 loops=1)

73. 0.000 0.000 ↓ 0.0 0

Hash (cost=39.55..39.55 rows=6 width=24) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Seq Scan on invprestemplateline tl_2 (cost=0.00..39.55 rows=6 width=24) (never executed)

  • Filter: (active AND ((COALESCE(cssclass, ''::character varying))::text !~~* '==controlBalance%'::text) AND ((COALESCE(cssclass, ''::character varying))::text ~~ '@%'::text))
75.          

CTE tempinvpresline

76. 43.858 691.154 ↑ 17.2 123,644 1

Unique (cost=635,153.01..677,616.23 rows=2,123,161 width=104) (actual time=625.548..691.154 rows=123,644 loops=1)

77. 181.768 647.296 ↑ 17.2 123,644 1

Sort (cost=635,153.01..640,460.91 rows=2,123,161 width=104) (actual time=625.547..647.296 rows=123,644 loops=1)

  • Sort Key: tempparentinvpresline_1.invpresid, tempparentinvpresline_1.rank, tempparentinvpresline_1.rankseq, tempparentinvpresline_1.invdoclineid, tempparentinvpresline_1.text, tempparentinvpresline_1.cssclass, tempparentinvpresline_1.price
  • Sort Method: external sort Disk: 5856kB
78. 10.118 465.528 ↑ 17.2 123,644 1

Append (cost=0.00..63,694.83 rows=2,123,161 width=104) (actual time=412.231..465.528 rows=123,644 loops=1)

79. 412.225 412.225 ↓ 0.0 0 1

CTE Scan on tempparentinvpresline tempparentinvpresline_1 (cost=0.00..3.78 rows=189 width=104) (actual time=412.225..412.225 rows=0 loops=1)

80. 24.083 43.185 ↑ 17.2 123,644 1

Subquery Scan on *SELECT* 2_2 (cost=0.00..63,689.16 rows=2,122,972 width=104) (actual time=0.005..43.185 rows=123,644 loops=1)

81. 19.102 19.102 ↑ 17.2 123,644 1

CTE Scan on tempchildinvpresline tempchildinvpresline_1 (cost=0.00..42,459.44 rows=2,122,972 width=104) (actual time=0.003..19.102 rows=123,644 loops=1)

82.          

CTE tempinvpreslinecontrol

83. 149.014 149.014 ↓ 1.8 12,000 1

CTE Scan on templateids tis_1 (cost=0.00..14,738.76 rows=6,500 width=96) (actual time=0.514..149.014 rows=12,000 loops=1)

  • Filter: ((COALESCE(cssclass, ''::character varying))::text ~~* '==controlBalance%'::text)
  • Rows Removed by Filter: 123644
84.          

CTE tempinvprescontrol

85. 17.210 1,039.757 ↓ 500.0 2,000 1

HashAggregate (cost=69,187.69..69,187.73 rows=4 width=52) (actual time=1,039.153..1,039.757 rows=2,000 loops=1)

86. 47.480 1,022.547 ↓ 13.6 23,155 1

Hash Join (cost=146.73..69,166.45 rows=1,699 width=52) (actual time=834.219..1,022.547 rows=23,155 loops=1)

  • Hash Cond: ((pl.invpresid = plc.invpresid) AND (pl.cssclass = plc.value))
87. 766.446 766.446 ↑ 17.2 123,644 1

CTE Scan on tempinvpresline pl (cost=0.00..42,463.22 rows=2,123,161 width=56) (actual time=625.552..766.446 rows=123,644 loops=1)

88. 3.301 208.621 ↓ 312.5 10,000 1

Hash (cost=146.25..146.25 rows=32 width=64) (actual time=208.621..208.621 rows=10,000 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 635kB
89. 205.320 205.320 ↓ 312.5 10,000 1

CTE Scan on tempinvpreslinecontrol plc (cost=0.00..146.25 rows=32 width=64) (actual time=0.534..205.320 rows=10,000 loops=1)

  • Filter: (type = 'cssClass'::text)
  • Rows Removed by Filter: 2000
90. 166.639 35,253.105 ↑ 17.2 123,644 1

Sort (cost=1,774,735.00..1,780,042.91 rows=2,123,165 width=84) (actual time=35,236.273..35,253.105 rows=123,644 loops=1)

  • Sort Key: x.invpresid, (row_number() OVER (?)), x.rank, x.rankseq, x.invdoclineid, x.text, x.cssclass
  • Sort Method: external sort Disk: 7552kB
91. 9.829 35,086.466 ↑ 17.2 123,644 1

Append (cost=1,177,817.01..1,246,819.87 rows=2,123,165 width=84) (actual time=35,010.755..35,086.466 rows=123,644 loops=1)

92. 47.200 35,076.634 ↑ 17.2 123,644 1

WindowAgg (cost=1,177,817.01..1,225,583.97 rows=2,122,976 width=84) (actual time=35,010.755..35,076.634 rows=123,644 loops=1)

93. 159.836 35,029.434 ↑ 17.2 123,644 1

Sort (cost=1,177,817.01..1,183,124.45 rows=2,122,976 width=84) (actual time=35,010.741..35,029.434 rows=123,644 loops=1)

  • Sort Key: x.invpresid, x.rank, x.invdoclineid
  • Sort Method: external sort Disk: 6104kB
94. 18.478 34,869.598 ↑ 17.2 123,644 1

Subquery Scan on x (cost=591,562.26..649,944.10 rows=2,122,976 width=84) (actual time=34,802.095..34,869.598 rows=123,644 loops=1)

95. 31.241 34,851.120 ↑ 17.2 123,644 1

Unique (cost=591,562.26..628,714.34 rows=2,122,976 width=84) (actual time=34,802.093..34,851.120 rows=123,644 loops=1)

96. 145.483 34,819.879 ↑ 17.2 123,644 1

Sort (cost=591,562.26..596,869.70 rows=2,122,976 width=84) (actual time=34,802.093..34,819.879 rows=123,644 loops=1)

  • Sort Key: tempchildinvpresline.invpresid, tempchildinvpresline.rank, tempchildinvpresline.rankseq, tempchildinvpresline.invdoclineid, tempchildinvpresline.text, tempchildinvpresline.cssclass
  • Sort Method: external sort Disk: 5624kB
97. 11.739 34,674.396 ↑ 17.2 123,644 1

Append (cost=0.00..63,689.36 rows=2,122,976 width=84) (actual time=33,135.132..34,674.396 rows=123,644 loops=1)

98. 33,622.003 33,622.003 ↑ 17.2 123,644 1

CTE Scan on tempchildinvpresline (cost=0.00..42,459.44 rows=2,122,972 width=84) (actual time=33,135.131..33,622.003 rows=123,644 loops=1)

99. 0.002 1,040.654 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=0.00..0.20 rows=4 width=64) (actual time=1,040.654..1,040.654 rows=0 loops=1)

100. 1,040.652 1,040.652 ↓ 0.0 0 1

CTE Scan on tempinvprescontrol (cost=0.00..0.16 rows=4 width=64) (actual time=1,040.652..1,040.652 rows=0 loops=1)

  • Filter: (price <> detail)
  • Rows Removed by Filter: 2000
101. 0.001 0.003 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..6.14 rows=189 width=84) (actual time=0.003..0.003 rows=0 loops=1)

102. 0.002 0.002 ↓ 0.0 0 1

CTE Scan on tempparentinvpresline (cost=0.00..4.25 rows=189 width=84) (actual time=0.002..0.002 rows=0 loops=1)