explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MtqB

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 8,647.843 ↑ 1.0 1 1

Result (cost=10,000,128,542.32..10,000,128,542.33 rows=1 width=96) (actual time=8,647.843..8,647.843 rows=1 loops=1)

2.          

CTE selected_file

3. 0.007 420.429 ↓ 29.0 29 1

Unique (cost=37,997.80..37,997.81 rows=1 width=16) (actual time=420.421..420.429 rows=29 loops=1)

4. 0.000 420.422 ↓ 29.0 29 1

Sort (cost=37,997.80..37,997.80 rows=1 width=16) (actual time=420.421..420.422 rows=29 loops=1)

  • Sort Key: f.id
  • Sort Method: quicksort Memory: 26kB
5. 14.009 420.484 ↓ 29.0 29 1

Gather (cost=30,514.53..37,997.79 rows=1 width=16) (actual time=417.754..420.484 rows=29 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 14.487 406.475 ↓ 10.0 10 3

Hash Join (cost=29,514.53..36,997.69 rows=1 width=16) (actual time=406.092..406.475 rows=10 loops=3)

  • Hash Cond: (a."DECLARATION" = d.id)
7. 63.730 391.598 ↑ 1.3 169,428 3

Hash Join (cost=29,490.73..36,417.53 rows=211,945 width=32) (actual time=313.104..391.598 rows=169,428 loops=3)

  • Hash Cond: (a."FILE" = f.id)
8. 17.921 17.921 ↑ 1.3 169,428 3

Parallel Seq Scan on association a (cost=0.00..6,370.45 rows=211,945 width=32) (actual time=0.030..17.921 rows=169,428 loops=3)

9. 112.554 309.947 ↑ 1.0 452,316 3

Hash (cost=23,756.99..23,756.99 rows=458,699 width=16) (actual time=309.947..309.947 rows=452,316 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 25299kB
10. 197.393 197.393 ↑ 1.0 452,316 3

Seq Scan on file f (cost=0.00..23,756.99 rows=458,699 width=16) (actual time=0.028..197.393 rows=452,316 loops=3)

  • Filter: (NOT "toBeDeleted")
11. 0.016 0.390 ↓ 29.0 29 3

Hash (cost=23.79..23.79 rows=1 width=16) (actual time=0.390..0.390 rows=29 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
12. 0.374 0.374 ↓ 29.0 29 3

Index Scan using declaration_unique_k on declaration d (cost=0.42..23.79 rows=1 width=16) (actual time=0.197..0.374 rows=29 loops=3)

  • Index Cond: (("ORGANIZATION" = '904eb59b-5610-4472-8254-72c487c1305b'::uuid) AND (date = '2018-09-01'::date))
  • Filter: ("NEXT_SERIAL" IS NULL)
13.          

CTE selected_declaration

14. 0.006 477.286 ↓ 29.0 29 1

Unique (cost=7,950.76..7,950.77 rows=1 width=20) (actual time=477.279..477.286 rows=29 loops=1)

15. 0.025 477.280 ↓ 29.0 29 1

Sort (cost=7,950.76..7,950.77 rows=1 width=20) (actual time=477.278..477.280 rows=29 loops=1)

  • Sort Key: d_1.id
  • Sort Method: quicksort Memory: 27kB
16. 0.000 477.255 ↓ 29.0 29 1

Hash Join (cost=1,023.84..7,950.75 rows=1 width=20) (actual time=476.949..477.255 rows=29 loops=1)

  • Hash Cond: (a_1."FILE" = f_1.id)
17. 10.875 56.818 ↓ 29.0 29 1

Gather (cost=1,023.80..7,950.71 rows=1 width=36) (actual time=56.494..56.818 rows=29 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
18. 19.953 45.943 ↓ 10.0 10 3

Hash Join (cost=23.80..6,950.61 rows=1 width=36) (actual time=45.686..45.943 rows=10 loops=3)

  • Hash Cond: (a_1."DECLARATION" = d_1.id)
19. 25.622 25.622 ↑ 1.3 169,428 3

Parallel Seq Scan on association a_1 (cost=0.00..6,370.45 rows=211,945 width=32) (actual time=0.026..25.622 rows=169,428 loops=3)

20. 0.015 0.368 ↓ 29.0 29 3

Hash (cost=23.79..23.79 rows=1 width=20) (actual time=0.368..0.368 rows=29 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
21. 0.353 0.353 ↓ 29.0 29 3

Index Scan using declaration_unique_k on declaration d_1 (cost=0.42..23.79 rows=1 width=20) (actual time=0.182..0.353 rows=29 loops=3)

  • Index Cond: (("ORGANIZATION" = '904eb59b-5610-4472-8254-72c487c1305b'::uuid) AND (date = '2018-09-01'::date))
  • Filter: ("NEXT_SERIAL" IS NULL)
22. 0.009 420.445 ↓ 29.0 29 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=420.445..420.445 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
23. 420.436 420.436 ↓ 29.0 29 1

CTE Scan on selected_file f_1 (cost=0.00..0.02 rows=1 width=16) (actual time=420.423..420.436 rows=29 loops=1)

24.          

CTE the_dates

25. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

26.          

CTE error_values

27. 0.612 106.661 ↓ 25.4 687 1

GroupAggregate (cost=22,860.14..22,861.56 rows=27 width=112) (actual time=105.982..106.661 rows=687 loops=1)

  • Group Key: e."rowId
28. 0.388 106.049 ↓ 25.6 691 1

Sort (cost=22,860.14..22,860.21 rows=27 width=93) (actual time=105.969..106.049 rows=691 loops=1)

  • Sort Key: e."rowId
  • Sort Method: quicksort Memory: 298kB
29. 0.160 105.661 ↓ 25.6 691 1

Hash Join (cost=3,007.04..22,859.50 rows=27 width=93) (actual time=15.625..105.661 rows=691 loops=1)

  • Hash Cond: (r."CONTROL" = c.id)
30. 14.378 104.696 ↓ 25.6 691 1

Hash Join (cost=2,902.95..22,755.35 rows=27 width=109) (actual time=14.810..104.696 rows=691 loops=1)

  • Hash Cond: (e."REVISION" = r.id)
31. 44.709 75.623 ↓ 29.7 160,923 1

Hash Join (cost=0.03..19,838.19 rows=5,424 width=109) (actual time=0.022..75.623 rows=160,923 loops=1)

  • Hash Cond: (e."FILE" = f_2.id)
32. 30.905 30.905 ↓ 1.0 160,923 1

Seq Scan on "error_904eb59b-5610-4472-8254-72c487c1305b_201809" e (cost=0.00..19,194.03 rows=157,303 width=125) (actual time=0.006..30.905 rows=160,923 loops=1)

33. 0.004 0.009 ↓ 29.0 29 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=0.009..0.009 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
34. 0.005 0.005 ↓ 29.0 29 1

CTE Scan on selected_file f_2 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.005 rows=29 loops=1)

35. 1.198 14.695 ↓ 53.5 6,690 1

Hash (cost=2,901.36..2,901.36 rows=125 width=32) (actual time=14.695..14.695 rows=6,690 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 483kB
36. 13.497 13.497 ↓ 53.5 6,690 1

Seq Scan on revision r (cost=0.00..2,901.36 rows=125 width=32) (actual time=0.014..13.497 rows=6,690 loops=1)

  • Filter: ("left"((cause)::text, 10) = 'S21.G00.3B'::text)
  • Rows Removed by Filter: 17156
37. 0.398 0.805 ↑ 1.0 2,801 1

Hash (cost=67.37..67.37 rows=2,937 width=16) (actual time=0.805..0.805 rows=2,801 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 164kB
38. 0.407 0.407 ↑ 1.0 2,801 1

Seq Scan on control c (cost=0.00..67.37 rows=2,937 width=16) (actual time=0.012..0.407 rows=2,801 loops=1)

39.          

CTE selected_data

40. 88.329 933.215 ↓ 27.0 608,065 1

Hash Left Join (cost=10,000,000,000.91..10,000,030,557.71 rows=22,521 width=200) (actual time=584.369..933.215 rows=608,065 loops=1)

  • Hash Cond: ("S21.G00.3B".id = "values".id)
41. 48.769 737.867 ↓ 27.0 608,065 1

Nested Loop (cost=10,000,000,000.03..10,000,030,472.37 rows=22,521 width=104) (actual time=477.338..737.867 rows=608,065 loops=1)

42. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on the_dates (cost=0.00..0.02 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1)

43. 131.255 689.093 ↓ 27.0 608,065 1

Hash Join (cost=0.03..30,247.14 rows=22,521 width=96) (actual time=477.331..689.093 rows=608,065 loops=1)

  • Hash Cond: ("S21.G00.3B"."DECLARATION" = d_2.id)
44. 80.533 80.533 ↑ 1.0 608,065 1

Seq Scan on "S21.G00.3B_904eb59b-5610-4472-8254-72c487c1305b_201809" "S21.G00.3B" (cost=0.00..27,741.65 rows=608,065 width=112) (actual time=0.014..80.533 rows=608,065 loops=1)

45. 0.009 477.305 ↓ 29.0 29 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=477.305..477.305 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
46. 477.296 477.296 ↓ 29.0 29 1

CTE Scan on selected_declaration d_2 (cost=0.00..0.02 rows=1 width=16) (actual time=477.282..477.296 rows=29 loops=1)

47. 0.124 107.019 ↓ 25.4 687 1

Hash (cost=0.54..0.54 rows=27 width=112) (actual time=107.019..107.019 rows=687 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
48. 106.895 106.895 ↓ 25.4 687 1

CTE Scan on error_values "values" (cost=0.00..0.54 rows=27 width=112) (actual time=105.985..106.895 rows=687 loops=1)

49.          

CTE error_codes_for_filtering

50. 27.922 500.043 ↓ 29.6 160,758 1

Unique (cost=23,409.80..23,450.48 rows=5,424 width=62) (actual time=455.842..500.043 rows=160,758 loops=1)

51. 291.064 472.121 ↓ 29.7 160,923 1

Sort (cost=23,409.80..23,423.36 rows=5,424 width=62) (actual time=455.840..472.121 rows=160,923 loops=1)

  • Sort Key: c_1.code, e_1."rowId", r_1.version DESC
  • Sort Method: quicksort Memory: 25077kB
52. 48.616 181.057 ↓ 29.7 160,923 1

Hash Join (cost=3,193.15..23,073.37 rows=5,424 width=62) (actual time=19.835..181.057 rows=160,923 loops=1)

  • Hash Cond: (r_1."CONTROL" = c_1.id)
53. 36.458 131.574 ↓ 29.7 160,923 1

Hash Join (cost=3,089.07..22,941.47 rows=5,424 width=51) (actual time=18.956..131.574 rows=160,923 loops=1)

  • Hash Cond: (e_1."REVISION" = r_1.id)
54. 45.266 76.230 ↓ 29.7 160,923 1

Hash Join (cost=0.03..19,838.19 rows=5,424 width=32) (actual time=0.020..76.230 rows=160,923 loops=1)

  • Hash Cond: (e_1."FILE" = f_3.id)
55. 30.956 30.956 ↓ 1.0 160,923 1

Seq Scan on "error_904eb59b-5610-4472-8254-72c487c1305b_201809" e_1 (cost=0.00..19,194.03 rows=157,303 width=48) (actual time=0.005..30.956 rows=160,923 loops=1)

56. 0.004 0.008 ↓ 29.0 29 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=0.008..0.008 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
57. 0.004 0.004 ↓ 29.0 29 1

CTE Scan on selected_file f_3 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.004 rows=29 loops=1)

58. 5.040 18.886 ↑ 1.0 23,846 1

Hash (cost=2,776.24..2,776.24 rows=25,024 width=51) (actual time=18.886..18.886 rows=23,846 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2213kB
59. 13.846 13.846 ↑ 1.0 23,846 1

Seq Scan on revision r_1 (cost=0.00..2,776.24 rows=25,024 width=51) (actual time=0.005..13.846 rows=23,846 loops=1)

60. 0.431 0.867 ↑ 1.0 2,801 1

Hash (cost=67.37..67.37 rows=2,937 width=26) (actual time=0.867..0.867 rows=2,801 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 193kB
61. 0.436 0.436 ↑ 1.0 2,801 1

Seq Scan on control c_1 (cost=0.00..67.37 rows=2,937 width=26) (actual time=0.010..0.436 rows=2,801 loops=1)

62.          

CTE selected_data_by_code

63. 491.398 2,990.637 ↓ 568.2 10,364,209 1

Append (cost=122.38..4,646.12 rows=18,240 width=48) (actual time=529.611..2,990.637 rows=10,364,209 loops=1)

64. 95.140 1,206.554 ↓ 80.9 245,985 1

Hash Join (cost=122.38..743.95 rows=3,040 width=48) (actual time=529.610..1,206.554 rows=245,985 loops=1)

  • Hash Cond: (sd."S21.G00.11" = e_2.id)
65. 581.812 581.812 ↓ 27.0 608,065 1

CTE Scan on selected_data sd (cost=0.00..450.42 rows=22,521 width=32) (actual time=0.001..581.812 rows=608,065 loops=1)

66. 0.009 529.602 ↑ 1.7 16 1

Hash (cost=122.04..122.04 rows=27 width=48) (actual time=529.602..529.602 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
67. 529.593 529.593 ↑ 1.7 16 1

CTE Scan on error_codes_for_filtering e_2 (cost=0.00..122.04 rows=27 width=48) (actual time=456.447..529.593 rows=16 loops=1)

  • Filter: (subgroup = 'S21.G00.11'::text)
  • Rows Removed by Filter: 160742
68. 0.013 11.513 ↓ 0.0 0 1

Hash Join (cost=122.38..743.95 rows=3,040 width=48) (actual time=11.513..11.513 rows=0 loops=1)

  • Hash Cond: (sd_1."S10.G00.00" = e_3.id)
69. 0.001 0.001 ↑ 22,521.0 1 1

CTE Scan on selected_data sd_1 (cost=0.00..450.42 rows=22,521 width=32) (actual time=0.001..0.001 rows=1 loops=1)

70. 0.000 11.499 ↓ 0.0 0 1

Hash (cost=122.04..122.04 rows=27 width=48) (actual time=11.499..11.499 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
71. 11.499 11.499 ↓ 0.0 0 1

CTE Scan on error_codes_for_filtering e_3 (cost=0.00..122.04 rows=27 width=48) (actual time=11.499..11.499 rows=0 loops=1)

  • Filter: (subgroup = 'S10.G00.00'::text)
  • Rows Removed by Filter: 160758
72. 0.007 12.389 ↓ 0.0 0 1

Hash Join (cost=122.38..743.95 rows=3,040 width=48) (actual time=12.389..12.389 rows=0 loops=1)

  • Hash Cond: (sd_2."S20.G00.05" = e_4.id)
73. 0.000 0.000 ↑ 22,521.0 1 1

CTE Scan on selected_data sd_2 (cost=0.00..450.42 rows=22,521 width=32) (actual time=0.000..0.000 rows=1 loops=1)

74. 0.000 12.382 ↓ 0.0 0 1

Hash (cost=122.04..122.04 rows=27 width=48) (actual time=12.382..12.382 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
75. 12.382 12.382 ↓ 0.0 0 1

CTE Scan on error_codes_for_filtering e_4 (cost=0.00..122.04 rows=27 width=48) (actual time=12.381..12.382 rows=0 loops=1)

  • Filter: (subgroup = 'S20.G00.05'::text)
  • Rows Removed by Filter: 160758
76. 1,054.292 1,154.909 ↓ 3,328.1 10,117,533 1

Hash Join (cost=122.38..743.95 rows=3,040 width=48) (actual time=33.202..1,154.909 rows=10,117,533 loops=1)

  • Hash Cond: (sd_3."S21.G00.30" = e_5.id)
77. 67.423 67.423 ↓ 27.0 608,065 1

CTE Scan on selected_data sd_3 (cost=0.00..450.42 rows=22,521 width=32) (actual time=0.000..67.423 rows=608,065 loops=1)

78. 14.183 33.194 ↓ 3,698.7 99,864 1

Hash (cost=122.04..122.04 rows=27 width=48) (actual time=33.194..33.194 rows=99,864 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 7753kB
79. 19.011 19.011 ↓ 3,698.7 99,864 1

CTE Scan on error_codes_for_filtering e_5 (cost=0.00..122.04 rows=27 width=48) (actual time=0.092..19.011 rows=99,864 loops=1)

  • Filter: (subgroup = 'S21.G00.30'::text)
  • Rows Removed by Filter: 60894
80. 0.014 11.238 ↓ 0.0 0 1

Hash Join (cost=122.38..743.95 rows=3,040 width=48) (actual time=11.238..11.238 rows=0 loops=1)

  • Hash Cond: (sd_4."S21.G00.06" = e_6.id)
81. 0.001 0.001 ↑ 22,521.0 1 1

CTE Scan on selected_data sd_4 (cost=0.00..450.42 rows=22,521 width=32) (actual time=0.001..0.001 rows=1 loops=1)

82. 0.000 11.223 ↓ 0.0 0 1

Hash (cost=122.04..122.04 rows=27 width=48) (actual time=11.223..11.223 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
83. 11.223 11.223 ↓ 0.0 0 1

CTE Scan on error_codes_for_filtering e_6 (cost=0.00..122.04 rows=27 width=48) (actual time=11.223..11.223 rows=0 loops=1)

  • Filter: (subgroup = 'S21.G00.06'::text)
  • Rows Removed by Filter: 160758
84. 52.905 102.636 ↑ 4.4 691 1

Hash Join (cost=122.38..743.95 rows=3,040 width=48) (actual time=12.714..102.636 rows=691 loops=1)

  • Hash Cond: (sd_5.id = e_7.id)
85. 38.692 38.692 ↓ 27.0 608,065 1

CTE Scan on selected_data sd_5 (cost=0.00..450.42 rows=22,521 width=16) (actual time=0.001..38.692 rows=608,065 loops=1)

86. 0.090 11.039 ↓ 25.6 691 1

Hash (cost=122.04..122.04 rows=27 width=48) (actual time=11.039..11.039 rows=691 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
87. 10.949 10.949 ↓ 25.6 691 1

CTE Scan on error_codes_for_filtering e_7 (cost=0.00..122.04 rows=27 width=48) (actual time=0.084..10.949 rows=691 loops=1)

  • Filter: (subgroup = 'S21.G00.3B'::text)
  • Rows Removed by Filter: 160067
88.          

Initplan (forResult)

89. 32.981 8,647.838 ↑ 1.0 1 1

Aggregate (cost=1,077.86..1,077.87 rows=1 width=32) (actual time=8,647.837..8,647.838 rows=1 loops=1)

90. 193.155 8,614.857 ↓ 54.0 608,065 1

Hash Join (cost=414.90..1,049.71 rows=11,260 width=32) (actual time=8,358.280..8,614.857 rows=608,065 loops=1)

  • Hash Cond: (selected_data.id = selected_data_by_code.id)
91. 647.811 647.811 ↓ 27.0 608,065 1

CTE Scan on selected_data (cost=0.00..450.42 rows=22,521 width=48) (actual time=584.373..647.811 rows=608,065 loops=1)

92. 87.294 7,773.891 ↓ 3,040.3 608,065 1

Hash (cost=412.40..412.40 rows=200 width=16) (actual time=7,773.891..7,773.891 rows=608,065 loops=1)

  • Buckets: 1048576 (originally 1024) Batches: 1 (originally 1) Memory Usage: 36696kB
93. 1,736.070 7,686.597 ↓ 3,040.3 608,065 1

HashAggregate (cost=410.40..412.40 rows=200 width=16) (actual time=7,563.963..7,686.597 rows=608,065 loops=1)

  • Group Key: selected_data_by_code.id
94. 5,950.527 5,950.527 ↓ 568.2 10,364,209 1

CTE Scan on selected_data_by_code (cost=0.00..364.80 rows=18,240 width=16) (actual time=529.613..5,950.527 rows=10,364,209 loops=1)