explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HQRx

Settings
# exclusive inclusive rows x rows loops node
1. 40.344 32,945.680 ↑ 26.4 123,644 1

Unique (cost=30,702,245.56..30,767,547.22 rows=3,265,083 width=84) (actual time=32,884.943..32,945.680 rows=123,644 loops=1)

2.          

CTE runids

3. 0.003 0.003 ↑ 1.0 1 1

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

4.          

CTE discounttext

5. 0.022 0.331 ↑ 2.5 2 1

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

6. 0.309 0.309 ↑ 1.1 18 1

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

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

CTE rateitemtext

8. 0.011 0.264 ↑ 1.5 2 1

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

9. 0.253 0.253 ↑ 1.2 10 1

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

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

CTE translation

11. 0.815 5.292 ↑ 1.9 693 1

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

12. 0.118 4.477 ↑ 1.9 694 1

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

13. 1.337 2.270 ↑ 2.5 274 1

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

14. 0.335 0.335 ↑ 2.5 2 1

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

15. 0.095 0.598 ↑ 1.0 137 2

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

16. 0.503 0.503 ↑ 1.0 137 1

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

17. 1.036 2.089 ↑ 1.5 420 1

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

18. 0.269 0.269 ↑ 1.5 2 1

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

19. 0.127 0.784 ↓ 1.0 210 2

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

20. 0.657 0.657 ↓ 1.0 210 1

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

21.          

CTE templatelineids

22. 182.887 208.858 ↓ 15.2 194,000 1

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

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

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

24. 0.007 1.548 ↑ 1.0 1 1

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

25. 0.006 0.006 ↑ 1.0 1 1

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

26. 1.535 1.535 ↑ 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.534..1.535 rows=1 loops=1)

  • Index Cond: (invdocrunid = ri.invdocrunid)
27. 20.482 20.482 ↓ 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.063..20.482 rows=2,000 loops=1)

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

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

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

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

  • Filter: active
30.          

CTE templateids

31. 139.210 30,258.850 ↑ 8.0 125,644 1

Nested Loop (cost=0.57..16,487,991.55 rows=1,005,060 width=164) (actual time=29.217..30,258.850 rows=125,644 loops=1)

32. 437.640 437.640 ↓ 15.2 194,000 1

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

33. 29,682.000 29,682.000 ↑ 79.0 1 194,000

Index Scan using invdocline_invdocid_index on invdocline l (cost=0.57..1,292.57 rows=79 width=64) (actual time=0.103..0.153 rows=1 loops=194,000)

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

CTE invoicedinids

35. 0.001 64.000 ↓ 0.0 0 1

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

36. 0.001 63.999 ↓ 0.0 0 1

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

37. 0.001 63.998 ↓ 0.0 0 1

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

38. 0.000 63.997 ↓ 0.0 0 1

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

39. 0.002 63.997 ↓ 0.0 0 1

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

40. 0.227 63.995 ↓ 0.0 0 1

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

41. 0.318 38.768 ↓ 1.3 1,000 1

Nested Loop (cost=18.48..2,858.82 rows=781 width=8) (actual time=2.495..38.768 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. 36.411 38.447 ↓ 1.3 1,000 1

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

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

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

  • Index Cond: (invdocrunid = ri_1.invdocrunid)
45. 25.000 25.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.025..0.025 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. 375.005 31,619.233 ↑ 26.4 123,644 1

WindowAgg (cost=2,500,143.01..10,164,232.84 rows=3,264,788 width=287) (actual time=31,209.260..31,619.233 rows=123,644 loops=1)

53. 344.613 31,244.228 ↑ 26.4 123,644 1

Sort (cost=2,500,143.01..2,508,304.98 rows=3,264,788 width=287) (actual time=31,209.196..31,244.228 rows=123,644 loops=1)

  • Sort Key: tis.invpresid, tis.rank, tis.invdoclineid
  • Sort Method: external merge Disk: 10232kB
54. 43.464 30,899.615 ↑ 26.4 123,644 1

Hash Left Join (cost=122.77..406,114.49 rows=3,264,788 width=287) (actual time=57.115..30,899.615 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. 187.246 30,849.846 ↑ 8.1 123,644 1

Hash Left Join (cost=80.33..60,269.51 rows=999,935 width=255) (actual time=50.789..30,849.846 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. 30,641.090 30,641.090 ↑ 8.1 123,644 1

CTE Scan on templateids tis (cost=0.00..25,126.50 rows=999,935 width=176) (actual time=29.232..30,641.090 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.852 21.510 ↑ 1.2 998 1

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

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

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

59. 0.382 6.305 ↑ 1.9 693 1

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

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

CTE Scan on translation tr (cost=0.00..26.12 rows=1,306 width=96) (actual time=5.086..5.923 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.003 64.040 ↓ 0.0 0 1

WindowAgg (cost=82,490.82..82,498.79 rows=290 width=116) (actual time=64.040..64.040 rows=0 loops=1)

67. 0.006 64.037 ↓ 0.0 0 1

Sort (cost=82,490.82..82,491.54 rows=290 width=116) (actual time=64.037..64.037 rows=0 loops=1)

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

Hash Join (cost=40.16..82,478.96 rows=290 width=116) (actual time=64.031..64.031 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.010 64.030 ↓ 0.0 0 1

Hash Join (cost=0.52..80,151.07 rows=261,183 width=132) (actual time=64.030..64.030 rows=0 loops=1)

  • Hash Cond: (l_1.invpresid = p_1.originalinvpresid)
70. 0.017 0.017 ↑ 3,264,788.0 1 1

CTE Scan on tempchildinvpresline l_1 (cost=0.00..65,295.76 rows=3,264,788 width=92) (actual time=0.017..0.017 rows=1 loops=1)

71. 0.002 64.003 ↓ 0.0 0 1

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

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

CTE Scan on invoicedinids p_1 (cost=0.00..0.32 rows=16 width=44) (actual time=64.001..64.001 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. 41.421 295.836 ↑ 26.4 123,644 1

Unique (cost=986,890.83..1,052,192.39 rows=3,265,078 width=104) (actual time=234.013..295.836 rows=123,644 loops=1)

77. 137.146 254.415 ↑ 26.4 123,644 1

Sort (cost=986,890.83..995,053.52 rows=3,265,078 width=104) (actual time=234.012..254.415 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.437 117.269 ↑ 26.4 123,644 1

Append (cost=0.00..97,952.34 rows=3,265,078 width=104) (actual time=64.048..117.269 rows=123,644 loops=1)

79. 64.041 64.041 ↓ 0.0 0 1

CTE Scan on tempparentinvpresline tempparentinvpresline_1 (cost=0.00..5.80 rows=290 width=104) (actual time=64.041..64.041 rows=0 loops=1)

80. 23.723 42.791 ↑ 26.4 123,644 1

Subquery Scan on *SELECT* 2_2 (cost=0.00..97,943.64 rows=3,264,788 width=104) (actual time=0.005..42.791 rows=123,644 loops=1)

81. 19.068 19.068 ↑ 26.4 123,644 1

CTE Scan on tempchildinvpresline tempchildinvpresline_1 (cost=0.00..65,295.76 rows=3,264,788 width=104) (actual time=0.002..19.068 rows=123,644 loops=1)

82.          

CTE tempinvpreslinecontrol

83. 165.022 165.022 ↓ 1.2 12,000 1

CTE Scan on templateids tis_1 (cost=0.00..22,666.37 rows=10,100 width=96) (actual time=0.095..165.022 rows=12,000 loops=1)

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

CTE tempinvprescontrol

85. 16.966 611.142 ↓ 400.0 2,000 1

HashAggregate (cost=122,760.25..122,760.30 rows=5 width=52) (actual time=610.498..611.142 rows=2,000 loops=1)

86. 48.293 594.176 ↓ 5.7 23,155 1

Hash Join (cost=228.00..122,709.24 rows=4,081 width=52) (actual time=433.655..594.176 rows=23,155 loops=1)

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

CTE Scan on tempinvpresline pl (cost=0.00..65,301.56 rows=3,265,078 width=56) (actual time=234.017..346.279 rows=123,644 loops=1)

88. 3.718 199.604 ↓ 200.0 10,000 1

Hash (cost=227.25..227.25 rows=50 width=64) (actual time=199.604..199.604 rows=10,000 loops=1)

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

CTE Scan on tempinvpreslinecontrol plc (cost=0.00..227.25 rows=50 width=64) (actual time=0.104..195.886 rows=10,000 loops=1)

  • Filter: (type = 'cssClass'::text)
  • Rows Removed by Filter: 2000
90. 151.757 32,905.336 ↑ 26.4 123,644 1

Sort (cost=2,759,631.66..2,767,794.36 rows=3,265,083 width=84) (actual time=32,884.942..32,905.336 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. 10.218 32,753.579 ↑ 26.4 123,644 1

Append (cost=1,831,535.89..1,937,651.09 rows=3,265,083 width=84) (actual time=32,675.478..32,753.579 rows=123,644 loops=1)

92. 48.728 32,743.357 ↑ 26.4 123,644 1

WindowAgg (cost=1,831,535.89..1,904,993.74 rows=3,264,793 width=84) (actual time=32,675.477..32,743.357 rows=123,644 loops=1)

93. 160.811 32,694.629 ↑ 26.4 123,644 1

Sort (cost=1,831,535.89..1,839,697.88 rows=3,264,793 width=84) (actual time=32,675.466..32,694.629 rows=123,644 loops=1)

  • Sort Key: x.invpresid, x.rank, x.invdoclineid
  • Sort Method: external sort Disk: 6104kB
94. 19.609 32,533.818 ↑ 26.4 123,644 1

Subquery Scan on x (cost=919,848.99..1,009,630.80 rows=3,264,793 width=84) (actual time=32,463.245..32,533.818 rows=123,644 loops=1)

95. 32.573 32,514.209 ↑ 26.4 123,644 1

Unique (cost=919,848.99..976,982.87 rows=3,264,793 width=84) (actual time=32,463.244..32,514.209 rows=123,644 loops=1)

96. 150.486 32,481.636 ↑ 26.4 123,644 1

Sort (cost=919,848.99..928,010.97 rows=3,264,793 width=84) (actual time=32,463.242..32,481.636 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.876 32,331.150 ↑ 26.4 123,644 1

Append (cost=0.00..97,943.89 rows=3,264,793 width=84) (actual time=31,209.266..32,331.150 rows=123,644 loops=1)

98. 31,707.222 31,707.222 ↑ 26.4 123,644 1

CTE Scan on tempchildinvpresline (cost=0.00..65,295.76 rows=3,264,788 width=84) (actual time=31,209.265..31,707.222 rows=123,644 loops=1)

99. 0.002 612.052 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=0.00..0.25 rows=5 width=64) (actual time=612.052..612.052 rows=0 loops=1)

100. 612.050 612.050 ↓ 0.0 0 1

CTE Scan on tempinvprescontrol (cost=0.00..0.20 rows=5 width=64) (actual time=612.050..612.050 rows=0 loops=1)

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

Subquery Scan on *SELECT* 2 (cost=0.00..9.42 rows=290 width=84) (actual time=0.004..0.004 rows=0 loops=1)

102. 0.002 0.002 ↓ 0.0 0 1

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