explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PwHI

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 8,542.133 ↑ 1.0 1 1

Result (cost=10,000,117,807.13..10,000,117,807.14 rows=1 width=96) (actual time=8,542.132..8,542.133 rows=1 loops=1)

2.          

CTE selected_file

3. 0.007 455.382 ↓ 31.0 31 1

Unique (cost=38,021.91..38,021.92 rows=1 width=16) (actual time=455.374..455.382 rows=31 loops=1)

4. 0.000 455.375 ↓ 31.0 31 1

Sort (cost=38,021.91..38,021.92 rows=1 width=16) (actual time=455.373..455.375 rows=31 loops=1)

  • Sort Key: f.id
  • Sort Method: quicksort Memory: 26kB
5. 10.982 455.431 ↓ 31.0 31 1

Gather (cost=30,533.37..38,021.90 rows=1 width=16) (actual time=452.646..455.431 rows=31 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 14.122 444.449 ↓ 10.0 10 3

Hash Join (cost=29,533.37..37,021.80 rows=1 width=16) (actual time=444.072..444.449 rows=10 loops=3)

  • Hash Cond: (a."DECLARATION" = d.id)
7. 65.069 429.721 ↑ 1.3 169,514 3

Hash Join (cost=29,524.56..36,456.25 rows=212,094 width=32) (actual time=347.325..429.721 rows=169,514 loops=3)

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

Parallel Seq Scan on association a (cost=0.00..6,374.94 rows=212,094 width=32) (actual time=0.031..20.697 rows=169,514 loops=3)

9. 115.454 343.955 ↑ 1.0 452,341 3

Hash (cost=23,784.25..23,784.25 rows=459,225 width=16) (actual time=343.955..343.955 rows=452,341 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 25300kB
10. 228.501 228.501 ↑ 1.0 452,341 3

Seq Scan on file f (cost=0.00..23,784.25 rows=459,225 width=16) (actual time=0.034..228.501 rows=452,341 loops=3)

  • Filter: (NOT "toBeDeleted")
11. 0.017 0.606 ↓ 31.0 31 3

Hash (cost=8.79..8.79 rows=1 width=16) (actual time=0.606..0.606 rows=31 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
12. 0.589 0.589 ↓ 31.0 31 3

Index Scan using declaration_unique_k on declaration d (cost=0.42..8.79 rows=1 width=16) (actual time=0.331..0.589 rows=31 loops=3)

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

CTE selected_declaration

14. 0.009 513.443 ↓ 31.0 31 1

Unique (cost=7,940.65..7,940.66 rows=1 width=20) (actual time=513.433..513.443 rows=31 loops=1)

15. 0.030 513.434 ↓ 31.0 31 1

Sort (cost=7,940.65..7,940.65 rows=1 width=20) (actual time=513.432..513.434 rows=31 loops=1)

  • Sort Key: d_1.id
  • Sort Method: quicksort Memory: 27kB
16. 0.000 513.404 ↓ 31.0 31 1

Hash Join (cost=1,008.84..7,940.64 rows=1 width=20) (actual time=513.081..513.404 rows=31 loops=1)

  • Hash Cond: (a_1."FILE" = f_1.id)
17. 12.923 58.006 ↓ 31.0 31 1

Gather (cost=1,008.80..7,940.59 rows=1 width=36) (actual time=57.659..58.006 rows=31 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
18. 19.410 45.083 ↓ 10.0 10 3

Hash Join (cost=8.80..6,940.49 rows=1 width=36) (actual time=44.843..45.083 rows=10 loops=3)

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

Parallel Seq Scan on association a_1 (cost=0.00..6,374.94 rows=212,094 width=32) (actual time=0.029..25.158 rows=169,514 loops=3)

20. 0.016 0.515 ↓ 31.0 31 3

Hash (cost=8.79..8.79 rows=1 width=20) (actual time=0.515..0.515 rows=31 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
21. 0.499 0.499 ↓ 31.0 31 3

Index Scan using declaration_unique_k on declaration d_1 (cost=0.42..8.79 rows=1 width=20) (actual time=0.271..0.499 rows=31 loops=3)

  • Index Cond: (("ORGANIZATION" = '904eb59b-5610-4472-8254-72c487c1305b'::uuid) AND (date = '2018-10-01'::date))
  • Filter: ("NEXT_SERIAL" IS NULL)
22. 0.022 455.413 ↓ 31.0 31 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=455.413..455.413 rows=31 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
23. 455.391 455.391 ↓ 31.0 31 1

CTE Scan on selected_file f_1 (cost=0.00..0.02 rows=1 width=16) (actual time=455.376..455.391 rows=31 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.607 38.058 ↓ 78.0 624 1

GroupAggregate (cost=17,317.76..17,318.18 rows=8 width=112) (actual time=37.389..38.058 rows=624 loops=1)

  • Group Key: e."rowId
28. 0.538 37.451 ↓ 79.1 633 1

Sort (cost=17,317.76..17,317.78 rows=8 width=92) (actual time=37.364..37.451 rows=633 loops=1)

  • Sort Key: e."rowId
  • Sort Method: quicksort Memory: 382kB
29. 0.102 36.913 ↓ 79.1 633 1

Hash Join (cost=1,000.03..17,317.64 rows=8 width=92) (actual time=1.124..36.913 rows=633 loops=1)

  • Hash Cond: (e."FILE" = f_2.id)
30. 6.613 36.794 ↓ 2.6 633 1

Gather (cost=1,000.00..17,316.63 rows=239 width=108) (actual time=1.092..36.794 rows=633 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
31. 30.181 30.181 ↓ 2.1 158 4

Parallel Seq Scan on "error_904eb59b-5610-4472-8254-72c487c1305b_201810" e (cost=0.00..16,292.73 rows=77 width=108) (actual time=2.396..30.181 rows=158 loops=4)

  • Filter: (subgroup = 'S21.G00.3B'::text)
  • Rows Removed by Filter: 40063
32. 0.017 0.017 ↓ 31.0 31 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=0.017..0.017 rows=31 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • -> CTE Scan on selected_file f_2 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.006 rows=31 loops=1) CTE selected_data
33.          

CTE error_codes_for_filtering

34. 30.847 120.003 ↓ 31.0 160,885 1

Hash Join (cost=104.12..18,025.83 rows=5,190 width=37) (actual time=1.124..120.003 rows=160,885 loops=1)

  • Hash Cond: (e_1."CONTROL" = c.id)
35. 51.554 88.139 ↓ 31.0 160,885 1

Hash Join (cost=0.03..17,908.10 rows=5,190 width=43) (actual time=0.083..88.139 rows=160,885 loops=1)

  • Hash Cond: (e_1."FILE" = f_3.id)
36. 36.569 36.569 ↑ 1.0 160,885 1

Seq Scan on "error_904eb59b-5610-4472-8254-72c487c1305b_201810" e_1 (cost=0.00..17,252.85 rows=160,885 width=59) (actual time=0.054..36.569 rows=160,885 loops=1)

37. 0.010 0.016 ↓ 31.0 31 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=0.016..0.016 rows=31 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
38. 0.006 0.006 ↓ 31.0 31 1

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

39. 0.523 1.017 ↑ 1.0 2,802 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 193kB
40. 0.494 0.494 ↑ 1.0 2,802 1

Seq Scan on control c (cost=0.00..67.37 rows=2,937 width=26) (actual time=0.009..0.494 rows=2,802 loops=1)

41.          

CTE selected_data_by_code

42. 499.701 2,736.284 ↓ 633.1 10,549,004 1

Append (cost=117.10..4,400.35 rows=16,662 width=48) (actual time=212.267..2,736.284 rows=10,549,004 loops=1)

43. 0.016 182.466 ↓ 0.0 0 1

Hash Join (cost=117.10..705.62 rows=2,777 width=48) (actual time=182.466..182.466 rows=0 loops=1)

  • Hash Cond: (sd."S20.G00.05" = e_2.id)
44. 0.001 0.001 ↑ 21,362.0 1 1

CTE Scan on selected_data sd (cost=0.00..427.24 rows=21,362 width=32) (actual time=0.001..0.001 rows=1 loops=1)

45. 0.001 182.449 ↓ 0.0 0 1

Hash (cost=116.77..116.77 rows=26 width=48) (actual time=182.449..182.449 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
46. 182.448 182.448 ↓ 0.0 0 1

CTE Scan on error_codes_for_filtering e_2 (cost=0.00..116.77 rows=26 width=48) (actual time=182.448..182.448 rows=0 loops=1)

  • Filter: (subgroup = 'S20.G00.05'::text)
  • Rows Removed by Filter: 160885
47. 0.012 15.142 ↓ 0.0 0 1

Hash Join (cost=117.10..705.62 rows=2,777 width=48) (actual time=15.142..15.142 rows=0 loops=1)

  • Hash Cond: (sd_1."S10.G00.00" = e_3.id)
48. 0.001 0.001 ↑ 21,362.0 1 1

CTE Scan on selected_data sd_1 (cost=0.00..427.24 rows=21,362 width=32) (actual time=0.001..0.001 rows=1 loops=1)

49. 0.000 15.129 ↓ 0.0 0 1

Hash (cost=116.77..116.77 rows=26 width=48) (actual time=15.129..15.129 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
50. 15.129 15.129 ↓ 0.0 0 1

CTE Scan on error_codes_for_filtering e_3 (cost=0.00..116.77 rows=26 width=48) (actual time=15.129..15.129 rows=0 loops=1)

  • Filter: (subgroup = 'S10.G00.00'::text)
  • Rows Removed by Filter: 160885
51. 68.811 705.192 ↑ 4.4 633 1

Hash Join (cost=117.10..705.62 rows=2,777 width=48) (actual time=14.657..705.192 rows=633 loops=1)

  • Hash Cond: (sd_2.id = e_4.id)
52. 621.733 621.733 ↓ 29.0 619,510 1

CTE Scan on selected_data sd_2 (cost=0.00..427.24 rows=21,362 width=16) (actual time=0.001..621.733 rows=619,510 loops=1)

53. 0.106 14.648 ↓ 24.3 633 1

Hash (cost=116.77..116.77 rows=26 width=48) (actual time=14.648..14.648 rows=633 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 43kB
54. 14.542 14.542 ↓ 24.3 633 1

CTE Scan on error_codes_for_filtering e_4 (cost=0.00..116.77 rows=26 width=48) (actual time=0.045..14.542 rows=633 loops=1)

  • Filter: (subgroup = 'S21.G00.3B'::text)
  • Rows Removed by Filter: 160252
55. 0.011 11.700 ↓ 0.0 0 1

Hash Join (cost=117.10..705.62 rows=2,777 width=48) (actual time=11.700..11.700 rows=0 loops=1)

  • Hash Cond: (sd_3."S21.G00.06" = e_5.id)
56. 0.001 0.001 ↑ 21,362.0 1 1

CTE Scan on selected_data sd_3 (cost=0.00..427.24 rows=21,362 width=32) (actual time=0.001..0.001 rows=1 loops=1)

57. 0.001 11.688 ↓ 0.0 0 1

Hash (cost=116.77..116.77 rows=26 width=48) (actual time=11.688..11.688 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
58. 11.687 11.687 ↓ 0.0 0 1

CTE Scan on error_codes_for_filtering e_5 (cost=0.00..116.77 rows=26 width=48) (actual time=11.687..11.687 rows=0 loops=1)

  • Filter: (subgroup = 'S21.G00.06'::text)
  • Rows Removed by Filter: 160885
59. 130.410 142.077 ↓ 89.1 247,315 1

Hash Join (cost=117.10..705.62 rows=2,777 width=48) (actual time=14.677..142.077 rows=247,315 loops=1)

  • Hash Cond: (sd_4."S21.G00.11" = e_6.id)
60. 0.003 11.667 ↑ 3.2 8 1

CTE Scan on selected_data sd_4 (cost=0.00..427.24 rows=21362 width=32) (actual time=0.000..50.066 rows=619510 loops=1) -> Hash (cost=116.77..116.77 rows=26 width=48) (actual time=11.667..11.667 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
61. 11.664 11.664 ↑ 3.2 8 1

CTE Scan on error_codes_for_filtering e_6 (cost=0.00..116.77 rows=26 width=48) (actual time=0.008..11.664 rows=8 loops=1)

  • Filter: (subgroup = 'S21.G00.11'::text)
  • Rows Removed by Filter: 160877
62. 1,145.589 1,180.006 ↓ 3,709.4 10,301,056 1

Hash Join (cost=117.10..705.62 rows=2,777 width=48) (actual time=34.432..1,180.006 rows=10,301,056 loops=1)

  • Hash Cond: (sd_5."S21.G00.30" = e_7.id)
63. 14.423 34.417 ↓ 3,976.5 103,389 1

CTE Scan on selected_data sd_5 (cost=0.00..427.24 rows=21362 width=32) (actual time=0.000..69.213 rows=619510 loops=1) -> Hash (cost=116.77..116.77 rows=26 width=48) (actual time=34.417..34.417 rows=103,389 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 7991kB
64. 19.994 19.994 ↓ 3,976.5 103,389 1

CTE Scan on error_codes_for_filtering e_7 (cost=0.00..116.77 rows=26 width=48) (actual time=0.043..19.994 rows=103,389 loops=1)

  • Filter: (subgroup = 'S21.G00.30'::text)
  • Rows Removed by Filter: 57496
65.          

Initplan (forResult)

66. 33.025 8,542.129 ↑ 1.0 1 1

Aggregate (cost=1,008.24..1,008.25 rows=1 width=32) (actual time=8,542.128..8,542.129 rows=1 loops=1)

67. 194.810 8,509.104 ↓ 58.0 619,510 1

Hash Join (cost=379.39..981.54 rows=10,681 width=32) (actual time=8,251.601..8,509.104 rows=619,510 loops=1)

  • Hash Cond: (selected_data.id = selected_data_by_code.id)
68. 614.635 614.635 ↓ 29.0 619,510 1

CTE Scan on selected_data (cost=0.00..427.24 rows=21,362 width=48) (actual time=551.918..614.635 rows=619,510 loops=1)

69. 88.840 7,699.659 ↓ 3,097.6 619,510 1

Hash (cost=376.89..376.89 rows=200 width=16) (actual time=7,699.659..7,699.659 rows=619,510 loops=1)

  • Buckets: 1048576 (originally 1024) Batches: 1 (originally 1) Memory Usage: 37232kB
70. 1,807.751 7,610.819 ↓ 3,097.6 619,510 1

HashAggregate (cost=374.89..376.89 rows=200 width=16) (actual time=7,486.609..7,610.819 rows=619,510 loops=1)

  • Group Key: selected_data_by_code.id
71. 5,803.068 5,803.068 ↓ 633.1 10,549,004 1

CTE Scan on selected_data_by_code (cost=0.00..333.24 rows=16,662 width=16) (actual time=212.269..5,803.068 rows=10,549,004 loops=1)

72. 90.250 924.567 ↓ 29.0 619,510 1

Hash Left Join (cost=10,000,000,000.29..10,000,031,091.93 rows=21,362 width=200) (actual time=551.914..924.567 rows=619,510 loops=1)

  • Hash Cond: ("S21.G00.3B".id = "values".id)
73. 48.496 795.923 ↓ 29.0 619,510 1

Nested Loop (cost=10,000,000,000.03..10,000,031,011.55 rows=21,362 width=104) (actual time=513.499..795.923 rows=619,510 loops=1)

74. 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)

75. 146.281 747.422 ↓ 29.0 619,510 1

Hash Join (cost=0.03..30,797.92 rows=21,362 width=96) (actual time=513.492..747.422 rows=619,510 loops=1)

  • Hash Cond: ("S21.G00.3B"."DECLARATION" = d_2.id)
76. 87.674 87.674 ↑ 1.0 619,510 1

Seq Scan on "S21.G00.3B_904eb59b-5610-4472-8254-72c487c1305b_201810" "S21.G00.3B" (cost=0.00..28,261.10 rows=619,510 width=112) (actual time=0.013..87.674 rows=619,510 loops=1)

77. 0.015 513.467 ↓ 31.0 31 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=513.467..513.467 rows=31 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
78. 513.452 513.452 ↓ 31.0 31 1

CTE Scan on selected_declaration d_2 (cost=0.00..0.02 rows=1 width=16) (actual time=513.435..513.452 rows=31 loops=1)

79. 0.131 38.394 ↓ 78.0 624 1

Hash (cost=0.16..0.16 rows=8 width=112) (actual time=38.394..38.394 rows=624 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
80. 38.263 38.263 ↓ 78.0 624 1

CTE Scan on error_values "values" (cost=0.00..0.16 rows=8 width=112) (actual time=37.394..38.263 rows=624 loops=1)