explain.depesz.com

PostgreSQL's explain analyze made readable

Result: f7wD

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 6,950.342 ↓ 6.0 12 1

Sort (cost=335,038.23..335,038.24 rows=2 width=104) (actual time=6,950.341..6,950.342 rows=12 loops=1)

  • Sort Key: ((sum(tmp_result.failed) / (count(*))::numeric)) DESC
  • Sort Method: quicksort Memory: 25kB
2.          

CTE args

3. 0.007 0.007 ↑ 410.0 1 1

Seq Scan on sites (cost=0.00..14.10 rows=410 width=208) (actual time=0.006..0.007 rows=1 loops=1)

4.          

CTE args_choices

5. 0.005 0.039 ↑ 4,100,000.0 1 1

ProjectSet (cost=0.01..21,635.71 rows=4,100,000 width=64) (actual time=0.036..0.039 rows=1 loops=1)

6. 0.004 0.034 ↑ 41,000.0 1 1

Nested Loop (cost=0.01..828.21 rows=41,000 width=64) (actual time=0.031..0.034 rows=1 loops=1)

7. 0.009 0.009 ↑ 410.0 1 1

CTE Scan on args (cost=0.00..8.20 rows=410 width=32) (actual time=0.008..0.009 rows=1 loops=1)

8. 0.021 0.021 ↑ 100.0 1 1

Function Scan on jsonb_each configs (cost=0.01..1.01 rows=100 width=64) (actual time=0.020..0.021 rows=1 loops=1)

9.          

CTE option_choices

10. 0.021 0.061 ↑ 25.5 29 1

Hash Join (cost=26.88..46.23 rows=740 width=80) (actual time=0.042..0.061 rows=29 loops=1)

  • Hash Cond: (choices.option_id = options.id)
11. 0.019 0.019 ↑ 25.5 29 1

Seq Scan on choices (cost=0.00..17.40 rows=740 width=64) (actual time=0.012..0.019 rows=29 loops=1)

12. 0.009 0.021 ↑ 53.6 14 1

Hash (cost=17.50..17.50 rows=750 width=48) (actual time=0.021..0.021 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.012 0.012 ↑ 53.6 14 1

Seq Scan on options (cost=0.00..17.50 rows=750 width=48) (actual time=0.007..0.012 rows=14 loops=1)

14.          

CTE exclude_choices

15. 0.008 21.137 ↑ 1.0 1 1

Aggregate (cost=225,533.71..225,533.72 rows=1 width=64) (actual time=21.137..21.137 rows=1 loops=1)

16. 20.988 21.129 ↑ 92.5 2 1

Hash Anti Join (cost=225,504.50..225,532.78 rows=185 width=48) (actual time=21.110..21.129 rows=2 loops=1)

  • Hash Cond: (option_choices.choice_value = args_choices_1.choice_value)
17. 0.013 0.137 ↑ 123.3 3 1

Hash Join (cost=92,254.50..92,275.36 rows=370 width=48) (actual time=0.119..0.137 rows=3 loops=1)

  • Hash Cond: (option_choices.option_name = args_choices.option_name)
18. 0.076 0.076 ↑ 25.5 29 1

CTE Scan on option_choices (cost=0.00..14.80 rows=740 width=80) (actual time=0.044..0.076 rows=29 loops=1)

19. 0.001 0.048 ↑ 200.0 1 1

Hash (cost=92,252.00..92,252.00 rows=200 width=32) (actual time=0.048..0.048 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.006 0.047 ↑ 200.0 1 1

HashAggregate (cost=92,250.00..92,252.00 rows=200 width=32) (actual time=0.046..0.047 rows=1 loops=1)

  • Group Key: args_choices.option_name
21. 0.041 0.041 ↑ 4,100,000.0 1 1

CTE Scan on args_choices (cost=0.00..82,000.00 rows=4,100,000 width=32) (actual time=0.037..0.041 rows=1 loops=1)

22. 0.002 0.004 ↑ 4,100,000.0 1 1

Hash (cost=82,000.00..82,000.00 rows=4,100,000 width=32) (actual time=0.004..0.004 rows=1 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 32769kB
23. 0.002 0.002 ↑ 4,100,000.0 1 1

CTE Scan on args_choices args_choices_1 (cost=0.00..82,000.00 rows=4,100,000 width=32) (actual time=0.002..0.002 rows=1 loops=1)

24.          

CTE tmp_sn

25. 2.240 288.055 ↓ 154.9 4,801 1

Unique (cost=39,232.22..39,232.45 rows=31 width=24) (actual time=284.538..288.055 rows=4,801 loops=1)

26.          

Initplan (forUnique)

27. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_1 (cost=0.00..8.20 rows=410 width=8) (actual time=0.001..0.001 rows=1 loops=1)

28. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_2 (cost=0.00..8.20 rows=410 width=8) (actual time=0.001..0.001 rows=1 loops=1)

29. 0.002 0.002 ↑ 410.0 1 1

CTE Scan on args args_3 (cost=0.00..8.20 rows=410 width=32) (actual time=0.002..0.002 rows=1 loops=1)

30. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_4 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 rows=1 loops=1)

31. 17.710 285.810 ↓ 223.8 6,937 1

Sort (cost=39,199.42..39,199.50 rows=31 width=24) (actual time=284.537..285.810 rows=6,937 loops=1)

  • Sort Key: m_logs_v2.root_serial_type, m_logs_v2.root_serial
  • Sort Method: quicksort Memory: 735kB
32. 18.246 268.100 ↓ 223.8 6,937 1

Nested Loop (cost=0.72..39,198.65 rows=31 width=24) (actual time=141.656..268.100 rows=6,937 loops=1)

  • Join Filter: (m_logs_v2.process_id = processes.id)
  • Rows Removed by Join Filter: 222715
33. 0.024 0.024 ↓ 2.0 2 1

Seq Scan on processes (cost=0.00..2.92 rows=1 width=16) (actual time=0.011..0.024 rows=2 loops=1)

  • Filter: (name = $7)
  • Rows Removed by Filter: 96
34. 19.370 249.830 ↓ 49.7 114,826 2

Nested Loop (cost=0.72..39,166.84 rows=2,311 width=40) (actual time=0.026..124.915 rows=114,826 loops=2)

35. 0.010 0.026 ↑ 3.0 1 2

Nested Loop (cost=0.15..90.57 rows=3 width=16) (actual time=0.009..0.013 rows=1 loops=2)

  • Join Filter: (components.project_id = projects.id)
  • Rows Removed by Join Filter: 2
36. 0.010 0.010 ↑ 250.0 3 2

Index Scan using components_pkey on components (cost=0.15..24.60 rows=750 width=32) (actual time=0.004..0.005 rows=3 loops=2)

37. 0.000 0.006 ↑ 4.0 1 6

Materialize (cost=0.00..21.02 rows=4 width=16) (actual time=0.001..0.001 rows=1 loops=6)

38. 0.006 0.006 ↑ 4.0 1 1

Seq Scan on projects (cost=0.00..21.00 rows=4 width=16) (actual time=0.005..0.006 rows=1 loops=1)

  • Filter: (name = $8)
  • Rows Removed by Filter: 2
39. 230.434 230.434 ↑ 2.2 114,826 2

Index Scan using m_logs_v2_component_id_first_time_idx on m_logs_v2 (cost=0.57..10,483.63 rows=254,179 width=56) (actual time=0.013..115.217 rows=114,826 loops=2)

  • Index Cond: ((component_id = components.id) AND (first_time >= $5) AND (first_time < $6))
40.          

CTE tmp_his

41. 9.366 6,877.638 ↓ 154.9 4,801 1

GroupAggregate (cost=47,835.06..47,839.00 rows=31 width=200) (actual time=6,867.498..6,877.638 rows=4,801 loops=1)

  • Group Key: tmp_sn.serial_type, tmp_sn.serial
42.          

Initplan (forGroupAggregate)

43. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_5 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 rows=1 loops=1)

44. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_6 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 rows=1 loops=1)

45. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_7 (cost=0.00..8.20 rows=410 width=32) (actual time=0.000..0.001 rows=1 loops=1)

46. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_8 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 rows=1 loops=1)

47. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_9 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 rows=1 loops=1)

48. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_10 (cost=0.00..8.20 rows=410 width=32) (actual time=0.000..0.001 rows=1 loops=1)

49. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_11 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 rows=1 loops=1)

50. 8.129 6,868.265 ↓ 272.3 18,245 1

Sort (cost=47,777.66..47,777.82 rows=67 width=226) (actual time=6,867.483..6,868.265 rows=18,245 loops=1)

  • Sort Key: tmp_sn.serial_type, tmp_sn.serial
  • Sort Method: quicksort Memory: 3334kB
51. 79.829 6,860.136 ↓ 272.3 18,245 1

Nested Loop Left Join (cost=0.58..47,775.62 rows=67 width=226) (actual time=285.318..6,860.136 rows=18,245 loops=1)

  • Join Filter: (processes_1.name = $17)
  • Rows Removed by Join Filter: 7112
52. 72.116 3,400.755 ↓ 143.3 9,601 1

Nested Loop Left Join (cost=0.57..47,691.03 rows=67 width=975) (actual time=284.935..3,400.755 rows=9,601 loops=1)

  • Join Filter: (processes_1.name = $15)
53. 3.714 505.945 ↓ 143.3 9,601 1

Nested Loop (cost=0.57..47,606.44 rows=67 width=943) (actual time=284.616..505.945 rows=9,601 loops=1)

54. 61.918 435.024 ↓ 143.3 9,601 1

Nested Loop (cost=0.00..47,538.99 rows=67 width=154) (actual time=284.603..435.024 rows=9,601 loops=1)

  • Join Filter: (m_logs_v2_1.process_id = processes_1.id)
  • Rows Removed by Join Filter: 440226
55. 16.173 373.106 ↓ 91.4 113,657 1

Nested Loop (cost=0.00..47,461.99 rows=1,243 width=162) (actual time=284.554..373.106 rows=113,657 loops=1)

56. 289.719 289.719 ↓ 154.9 4,801 1

CTE Scan on tmp_sn (cost=0.00..0.62 rows=31 width=64) (actual time=284.540..289.719 rows=4,801 loops=1)

57. 67.214 67.214 ↑ 13.0 24 4,801

Index Scan using m_logs_v2_root_serial_idx on m_logs_v2 m_logs_v2_1 (cost=0.00..1,527.90 rows=311 width=122) (actual time=0.004..0.014 rows=24 loops=4,801)

  • Index Cond: (root_serial = tmp_sn.serial)
  • Rows Removed by Index Recheck: 0
  • Filter: (last AND (tmp_sn.serial_type = root_serial_type))
  • Rows Removed by Filter: 2
58. 0.000 0.000 ↑ 1.0 4 113,657

Materialize (cost=0.00..3.04 rows=4 width=24) (actual time=0.000..0.000 rows=4 loops=113,657)

59. 0.039 0.039 ↑ 1.0 4 1

Seq Scan on processes processes_1 (cost=0.00..3.02 rows=4 width=24) (actual time=0.016..0.039 rows=4 loops=1)

  • Filter: (name = ANY (ARRAY[$11, $12, $13]))
  • Rows Removed by Filter: 94
60. 67.207 67.207 ↑ 1.0 1 9,601

Index Scan using logs_v2_pkey on logs_v2 (cost=0.57..1.01 rows=1 width=821) (actual time=0.007..0.007 rows=1 loops=9,601)

  • Index Cond: (id = m_logs_v2_1.id)
61. 2,822.694 2,822.694 ↑ 1.0 1 9,601

Function Scan on jsonb_to_recordset station (cost=0.01..1.25 rows=1 width=32) (actual time=0.277..0.294 rows=1 loops=9,601)

  • Filter: (sub_test = $14)
  • Rows Removed by Filter: 624
62. 3,379.552 3,379.552 ↓ 2.0 2 9,601

Function Scan on jsonb_to_recordset cnc_data (cost=0.01..1.25 rows=1 width=64) (actual time=0.343..0.352 rows=2 loops=9,601)

  • Filter: (test = $16)
  • Rows Removed by Filter: 623
63.          

CTE tmp_result

64. 1.672 6,947.258 ↓ 300.1 4,801 1

Nested Loop (cost=23.68..736.04 rows=16 width=232) (actual time=6,888.681..6,947.258 rows=4,801 loops=1)

  • Join Filter: COALESCE((NOT ((array_agg(DISTINCT choices_1.value)) && exclude_choices.exclude_choice_values)), true)
65. 21.138 21.138 ↑ 1.0 1 1

CTE Scan on exclude_choices (cost=0.00..0.02 rows=1 width=32) (actual time=21.138..21.138 rows=1 loops=1)

66. 2.105 6,924.448 ↓ 154.9 4,801 1

Nested Loop Left Join (cost=23.68..735.63 rows=31 width=232) (actual time=6,867.539..6,924.448 rows=4,801 loops=1)

67. 6,879.134 6,879.134 ↓ 154.9 4,801 1

CTE Scan on tmp_his (cost=0.00..0.62 rows=31 width=200) (actual time=6,867.500..6,879.134 rows=4,801 loops=1)

68. 9.602 43.209 ↑ 1.0 1 4,801

Aggregate (cost=23.68..23.69 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=4,801)

69. 14.403 33.607 ↑ 33.3 3 4,801

Hash Join (cost=2.25..23.43 rows=100 width=32) (actual time=0.004..0.007 rows=3 loops=4,801)

  • Hash Cond: (choices_1.id = unnested.unnested)
70. 14.403 14.403 ↑ 25.5 29 4,801

Seq Scan on choices choices_1 (cost=0.00..17.40 rows=740 width=48) (actual time=0.001..0.003 rows=29 loops=4,801)

71. 0.000 4.801 ↑ 33.3 3 4,801

Hash (cost=1.00..1.00 rows=100 width=16) (actual time=0.001..0.001 rows=3 loops=4,801)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
72. 4.801 4.801 ↑ 33.3 3 4,801

Function Scan on unnest unnested (cost=0.00..1.00 rows=100 width=16) (actual time=0.001..0.001 rows=3 loops=4,801)

73. 1.190 6,950.332 ↓ 6.0 12 1

HashAggregate (cost=0.60..0.97 rows=2 width=104) (actual time=6,950.315..6,950.332 rows=12 loops=1)

  • Group Key: tmp_result.line
  • Filter: ((sum(tmp_result.failed) > '0'::numeric) AND (count(*) >= 1))
  • Rows Removed by Filter: 81
74. 6,949.142 6,949.142 ↓ 300.1 4,801 1

CTE Scan on tmp_result (cost=0.00..0.32 rows=16 width=40) (actual time=6,888.683..6,949.142 rows=4,801 loops=1)

Planning time : 3.104 ms
Execution time : 6,953.593 ms