explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BS9H

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

Result (cost=10,000,090,544.45..10,000,090,544.46 rows=1 width=96) (actual time=8,553.406..8,553.406 rows=1 loops=1)

2.          

CTE selected_declaration

3. 11.218 78.673 ↓ 29.0 29 1

Gather (cost=1,023.80..7,950.71 rows=1 width=20) (actual time=78.263..78.673 rows=29 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 25.711 67.455 ↓ 10.0 10 3

Hash Join (cost=23.80..6,950.61 rows=1 width=20) (actual time=67.174..67.455 rows=10 loops=3)

  • Hash Cond: (a."DECLARATION" = d.id)
5. 41.371 41.371 ↑ 1.3 169,428 3

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

6. 0.017 0.373 ↓ 29.0 29 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
7. 0.356 0.356 ↓ 29.0 29 3

Index Scan using declaration_unique_k on declaration d (cost=0.42..23.79 rows=1 width=20) (actual time=0.180..0.356 rows=29 loops=3)

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

CTE the_dates

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

10.          

CTE error_values

11. 0.603 125.187 ↓ 25.4 687 1

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

  • Group Key: e."rowId
12. 0.432 124.584 ↓ 25.6 691 1

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

  • Sort Key: e."rowId
  • Sort Method: quicksort Memory: 298kB
13. 0.189 124.152 ↓ 25.6 691 1

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

  • Hash Cond: (r."CONTROL" = c.id)
14. 15.105 122.842 ↓ 25.6 691 1

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

  • Hash Cond: (e."REVISION" = r.id)
15. 49.997 86.229 ↓ 29.7 160,923 1

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

  • Hash Cond: (e."DECLARATION" = d_1.id)
16. 36.219 36.219 ↓ 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.007..36.219 rows=160,923 loops=1)

17. 0.006 0.013 ↓ 29.0 29 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
18. 0.007 0.007 ↓ 29.0 29 1

CTE Scan on selected_declaration d_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.007 rows=29 loops=1)

19. 1.578 21.508 ↓ 53.5 6,690 1

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

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

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 164kB
22. 0.579 0.579 ↑ 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.579 rows=2,801 loops=1)

23.          

CTE selected_data

24. 88.101 554.192 ↓ 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=204.212..554.192 rows=608,065 loops=1)

  • Hash Cond: ("S21.G00.3B".id = "values".id)
25. 50.057 340.540 ↓ 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=78.650..340.540 rows=608,065 loops=1)

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

27. 130.650 290.478 ↓ 27.0 608,065 1

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

  • Hash Cond: ("S21.G00.3B"."DECLARATION" = d_2.id)
28. 81.209 81.209 ↑ 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..81.209 rows=608,065 loops=1)

29. 0.010 78.619 ↓ 29.0 29 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
30. 78.609 78.609 ↓ 29.0 29 1

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

31. 0.124 125.551 ↓ 25.4 687 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
32. 125.427 125.427 ↓ 25.4 687 1

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

33.          

CTE error_codes_for_filtering

34. 31.867 570.708 ↓ 29.6 160,758 1

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

35. 354.639 538.841 ↓ 29.7 160,923 1

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

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

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

  • Hash Cond: (r_1."CONTROL" = c_1.id)
37. 36.891 134.711 ↓ 29.7 160,923 1

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

  • Hash Cond: (e_1."REVISION" = r_1.id)
38. 45.912 77.882 ↓ 29.7 160,923 1

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

  • Hash Cond: (e_1."DECLARATION" = d_3.id)
39. 31.957 31.957 ↓ 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..31.957 rows=160,923 loops=1)

40. 0.006 0.013 ↓ 29.0 29 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
41. 0.007 0.007 ↓ 29.0 29 1

CTE Scan on selected_declaration d_3 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.007 rows=29 loops=1)

42. 5.099 19.938 ↑ 1.0 23,846 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 2213kB
43. 14.839 14.839 ↑ 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..14.839 rows=23,846 loops=1)

44. 0.446 0.909 ↑ 1.0 2,801 1

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

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

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

46.          

CTE selected_data_by_code

47. 478.586 3,101.808 ↓ 568.2 10,364,209 1

Append (cost=122.38..4,646.12 rows=18,240 width=48) (actual time=606.185..3,101.808 rows=10,364,209 loops=1)

48. 94.046 1,280.372 ↓ 80.9 245,985 1

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

  • Hash Cond: (sd."S21.G00.11" = e_2.id)
49. 580.152 580.152 ↓ 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..580.152 rows=608,065 loops=1)

50. 0.012 606.174 ↑ 1.7 16 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 606.162 606.162 ↑ 1.7 16 1

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

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

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

  • Hash Cond: (sd_1."S10.G00.00" = e_3.id)
53. 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)

54. 0.000 12.074 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
55. 12.074 12.074 ↓ 0.0 0 1

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

  • Filter: (subgroup = 'S10.G00.00'::text)
  • Rows Removed by Filter: 160758
56. 0.011 11.770 ↓ 0.0 0 1

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

  • Hash Cond: (sd_2."S20.G00.05" = e_4.id)
57. 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)

58. 0.000 11.759 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
59. 11.759 11.759 ↓ 0.0 0 1

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

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

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

  • Hash Cond: (sd_3."S21.G00.30" = e_5.id)
61. 76.797 76.797 ↓ 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..76.797 rows=608,065 loops=1)

62. 15.233 35.697 ↓ 3,698.7 99,864 1

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

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 7753kB
63. 20.464 20.464 ↓ 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.098..20.464 rows=99,864 loops=1)

  • Filter: (subgroup = 'S21.G00.30'::text)
  • Rows Removed by Filter: 60894
64. 0.016 11.313 ↓ 0.0 0 1

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

  • Hash Cond: (sd_4."S21.G00.06" = e_6.id)
65. 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)

66. 0.001 11.296 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
67. 11.295 11.295 ↓ 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.295..11.295 rows=0 loops=1)

  • Filter: (subgroup = 'S21.G00.06'::text)
  • Rows Removed by Filter: 160758
68. 52.758 103.345 ↑ 4.4 691 1

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

  • Hash Cond: (sd_5.id = e_7.id)
69. 39.400 39.400 ↓ 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.000..39.400 rows=608,065 loops=1)

70. 0.090 11.187 ↓ 25.6 691 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
71. 11.097 11.097 ↓ 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.077..11.097 rows=691 loops=1)

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

Initplan (forResult)

73. 33.627 8,553.401 ↑ 1.0 1 1

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

74. 193.656 8,519.774 ↓ 54.0 608,065 1

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

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

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

76. 86.838 8,059.525 ↓ 3,040.3 608,065 1

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

  • Buckets: 1048576 (originally 1024) Batches: 1 (originally 1) Memory Usage: 36696kB
77. 1,803.239 7,972.687 ↓ 3,040.3 608,065 1

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

  • Group Key: selected_data_by_code.id
78. 6,169.448 6,169.448 ↓ 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=606.187..6,169.448 rows=10,364,209 loops=1)