explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y541

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 8,355.345 ↑ 11.0 2 1

Sort (cost=3,765,800,526.38..3,765,800,526.44 rows=22 width=104) (actual time=8,355.345..8,355.345 rows=2 loops=1)

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

CTE args

3. 0.014 0.014 ↑ 410.0 1 1

Seq Scan on sites (cost=0.00..14.10 rows=410 width=176) (actual time=0.014..0.014 rows=1 loops=1)

4.          

CTE args_choices

5. 0.004 0.041 ↑ 4,100,000.0 1 1

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

6. 0.002 0.037 ↑ 41,000.0 1 1

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

7. 0.015 0.015 ↑ 410.0 1 1

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

8. 0.020 0.020 ↑ 100.0 1 1

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

9.          

CTE option_choices

10. 0.023 0.071 ↑ 25.5 29 1

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

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

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

12. 0.007 0.016 ↑ 53.6 14 1

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

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

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

14.          

CTE exclude_choices

15. 0.007 16.456 ↑ 1.0 1 1

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

16. 16.297 16.449 ↑ 92.5 2 1

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

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

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

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

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

19. 0.006 0.053 ↑ 200.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.003 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.044 0.044 ↑ 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.042..0.044 rows=1 loops=1)

22. 0.003 0.005 ↑ 4,100,000.0 1 1

Hash (cost=82,000.00..82,000.00 rows=4,100,000 width=32) (actual time=0.005..0.005 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_agents

25. 0.007 0.264 ↑ 1.4 12 1

Unique (cost=26.70..26.78 rows=17 width=14) (actual time=0.252..0.264 rows=12 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=32) (actual time=0.001..0.001 rows=1 loops=1)

28. 0.030 0.256 ↓ 1.1 19 1

Sort (cost=18.50..18.54 rows=17 width=14) (actual time=0.252..0.256 rows=19 loops=1)

  • Sort Key: jobs.agent_id, jobs.started DESC
  • Sort Method: quicksort Memory: 25kB
29. 0.006 0.226 ↓ 1.1 19 1

Nested Loop (cost=1.52..18.15 rows=17 width=14) (actual time=0.099..0.226 rows=19 loops=1)

30. 0.034 0.034 ↓ 2.0 2 1

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

  • Filter: (name = $5)
  • Rows Removed by Filter: 96
31. 0.144 0.186 ↑ 1.9 10 2

Bitmap Heap Scan on jobs (cost=1.52..15.03 rows=19 width=46) (actual time=0.031..0.093 rows=10 loops=2)

  • Recheck Cond: (process_id = processes.id)
  • Filter: (agent_id <> '00:1b:78:5d:70:78'::macaddr)
  • Heap Blocks: exact=8
32. 0.042 0.042 ↑ 1.9 10 2

Bitmap Index Scan on jobs_process_id_started_idx (cost=0.00..1.52 rows=19 width=0) (actual time=0.021..0.021 rows=10 loops=2)

  • Index Cond: (process_id = processes.id)
33.          

CTE all_sn

34. 13.189 185.113 ↑ 2.0 7,005 1

HashAggregate (cost=68,953.28..69,090.60 rows=13,732 width=35) (actual time=175.361..185.113 rows=7,005 loops=1)

  • Group Key: logs_serials.serial_type, logs_serials.serial
35.          

Initplan (forHashAggregate)

36. 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.000..0.001 rows=1 loops=1)

37. 0.000 0.000 ↑ 410.0 1 1

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

38. 1.580 171.923 ↑ 1.8 7,520 1

Nested Loop (cost=1.14..68,868.22 rows=13,732 width=35) (actual time=0.547..171.923 rows=7,520 loops=1)

39. 0.739 34.983 ↑ 1.6 7,520 1

Nested Loop (cost=0.57..13,801.83 rows=12,095 width=16) (actual time=0.436..34.983 rows=7,520 loops=1)

40. 0.272 0.272 ↑ 1.4 12 1

CTE Scan on tmp_agents (cost=0.00..0.34 rows=17 width=6) (actual time=0.253..0.272 rows=12 loops=1)

41. 33.972 33.972 ↑ 1.1 627 12

Index Scan using logs_v2_agent_id_created_idx on logs_v2 (cost=0.57..804.74 rows=711 width=22) (actual time=0.039..2.831 rows=627 loops=12)

  • Index Cond: ((agent_id = tmp_agents.agent_id) AND (created >= $8) AND (created < $9))
42. 135.360 135.360 ↑ 9.0 1 7,520

Index Scan using logs_serials_log_id_idx on logs_serials (cost=0.57..4.46 rows=9 width=51) (actual time=0.018..0.018 rows=1 loops=7,520)

  • Index Cond: (log_id = logs_v2.id)
43.          

CTE tmp_sn

44. 3.837 1,930.444 ↑ 2.9 4,801 1

Nested Loop (cost=37.73..293,560.55 rows=13,732 width=64) (actual time=176.545..1,930.444 rows=4,801 loops=1)

45.          

Initplan (forNested Loop)

46. 0.001 0.001 ↑ 410.0 1 1

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

47. 0.001 0.001 ↑ 410.0 1 1

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

48. 189.365 189.365 ↑ 2.0 7,005 1

CTE Scan on all_sn (cost=0.00..274.64 rows=13,732 width=64) (actual time=175.362..189.365 rows=7,005 loops=1)

49. 7.005 1,737.240 ↑ 1.0 1 7,005

Subquery Scan on first_log (cost=21.33..21.35 rows=1 width=0) (actual time=0.247..0.248 rows=1 loops=7,005)

  • Filter: ((first_log.first_seen >= $13) AND (first_log.first_seen < $14))
  • Rows Removed by Filter: 0
50. 7.005 1,730.235 ↑ 1.0 1 7,005

Limit (cost=21.33..21.33 rows=1 width=8) (actual time=0.246..0.247 rows=1 loops=7,005)

51. 14.010 1,723.230 ↑ 1.0 1 7,005

Sort (cost=21.33..21.33 rows=1 width=8) (actual time=0.246..0.246 rows=1 loops=7,005)

  • Sort Key: logs_v2_1.created
  • Sort Method: quicksort Memory: 25kB
52. 13.404 1,709.220 ↓ 4.0 4 7,005

Hash Join (cost=1.69..21.32 rows=1 width=8) (actual time=0.124..0.244 rows=4 loops=7,005)

  • Hash Cond: (logs_v2_1.agent_id = tmp_agents_1.agent_id)
53. 30.054 1,695.210 ↓ 2.0 10 7,005

Nested Loop (cost=1.14..20.74 rows=5 width=14) (actual time=0.043..0.242 rows=10 loops=7,005)

54. 448.320 448.320 ↓ 2.0 10 7,005

Index Scan using logs_serials_serial_type_serial_text_ops_idx on logs_serials logs_serials_1 (cost=0.57..6.81 rows=5 width=16) (actual time=0.023..0.064 rows=10 loops=7,005)

  • Index Cond: ((serial_type = all_sn.serial_type) AND (serial = all_sn.serial))
55. 1,216.836 1,216.836 ↑ 1.0 1 67,602

Index Scan using logs_v2_pkey on logs_v2 logs_v2_1 (cost=0.57..2.79 rows=1 width=30) (actual time=0.018..0.018 rows=1 loops=67,602)

  • Index Cond: (id = logs_serials_1.log_id)
56. 0.342 0.606 ↑ 1.4 12 1

Hash (cost=0.34..0.34 rows=17 width=6) (actual time=0.007..0.606 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
57. 0.264 0.264 ↑ 1.4 12 1

CTE Scan on tmp_agents tmp_agents_1 (cost=0.00..0.34 rows=17 width=6) (actual time=0.001..0.264 rows=12 loops=1)

58.          

CTE tmp_his

59. 9.080 8,282.434 ↓ 3.5 4,801 1

GroupAggregate (cost=3,765,154,817.31..3,765,157,989.40 rows=1,373 width=392) (actual time=8,272.948..8,282.434 rows=4,801 loops=1)

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

Initplan (forGroupAggregate)

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

62. 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.001..0.001 rows=1 loops=1)

63. 0.002 0.002 ↑ 410.0 1 1

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

64. 0.002 0.002 ↑ 410.0 1 1

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

65. 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.001..0.001 rows=1 loops=1)

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

67. 0.002 0.002 ↑ 410.0 1 1

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

68. 0.001 0.001 ↑ 410.0 1 1

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

69. 0.004 0.004 ↑ 410.0 1 1

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

70. 33.638 8,273.339 ↑ 5.7 9,601 1

Sort (cost=3,765,154,743.51..3,765,154,880.83 rows=54,928 width=430) (actual time=8,272.726..8,273.339 rows=9,601 loops=1)

  • Sort Key: tmp_sn.serial_type, tmp_sn.serial
  • Sort Method: quicksort Memory: 2935kB
71. 6.941 8,239.701 ↑ 5.7 9,601 1

Nested Loop (cost=274,187.66..3,765,150,419.23 rows=54,928 width=430) (actual time=178.185..8,239.701 rows=9,601 loops=1)

72. 1,933.848 1,933.848 ↑ 2.9 4,801 1

CTE Scan on tmp_sn (cost=0.00..274.64 rows=13,732 width=64) (actual time=176.546..1,933.848 rows=4,801 loops=1)

73. 4.796 6,298.912 ↑ 2.0 2 4,801

Unique (cost=274,187.66..274,187.96 rows=4 width=406) (actual time=1.311..1.312 rows=2 loops=4,801)

74.          

Initplan (forUnique)

75. 0.001 0.001 ↑ 410.0 1 1

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

76. 0.001 0.001 ↑ 410.0 1 1

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

77. 0.001 0.001 ↑ 410.0 1 1

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

78. 0.001 0.001 ↑ 410.0 1 1

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

79. 0.001 0.001 ↑ 410.0 1 1

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

80. 19.204 6,294.111 ↑ 30.0 2 4,801

Sort (cost=274,146.66..274,146.81 rows=60 width=406) (actual time=1.311..1.311 rows=2 loops=4,801)

  • Sort Key: processes_1.name, m_logs_v2.created DESC
  • Sort Method: quicksort Memory: 25kB
81. 3,098.006 6,274.907 ↑ 30.0 2 4,801

Nested Loop Left Join (cost=265.23..274,144.89 rows=60 width=406) (actual time=0.476..1.307 rows=2 loops=4,801)

  • Join Filter: (processes_1.name = $30)
82. 18.438 446.493 ↑ 30.0 2 4,801

Nested Loop (cost=265.22..274,067.48 rows=60 width=887) (actual time=0.057..0.093 rows=2 loops=4,801)

83. 67.214 345.672 ↑ 30.0 2 4,801

Nested Loop (cost=264.65..274,006.31 rows=60 width=98) (actual time=0.049..0.072 rows=2 loops=4,801)

  • Join Filter: (m_logs_v2.process_id = processes_1.id)
  • Rows Removed by Join Filter: 98
84. 24.005 278.458 ↑ 44.6 25 4,801

Nested Loop (cost=264.65..273,936.92 rows=1,115 width=106) (actual time=0.038..0.058 rows=25 loops=4,801)

85. 52.811 120.025 ↑ 163.0 1 4,801

Bitmap Heap Scan on m_assemblies (cost=264.65..32,317.48 rows=163 width=162) (actual time=0.025..0.025 rows=1 loops=4,801)

  • Recheck Cond: (serials @> ARRAY[tmp_sn.serial])
  • Filter: (serials[array_position(serial_types, tmp_sn.serial_type)] = tmp_sn.serial)
  • Heap Blocks: exact=4801
86. 67.214 67.214 ↑ 32,642.0 1 4,801

Bitmap Index Scan on m_assemblies_serials_idx (cost=0.00..264.61 rows=32,642 width=0) (actual time=0.014..0.014 rows=1 loops=4,801)

  • Index Cond: (serials @> ARRAY[tmp_sn.serial])
87. 134.428 134.428 ↑ 13.7 25 4,801

Index Scan using m_logs_v2_root_serial_idx on m_logs_v2 (cost=0.00..1,478.91 rows=342 width=146) (actual time=0.011..0.028 rows=25 loops=4,801)

  • Index Cond: (root_serial = (m_assemblies.serials)[1])
  • Rows Removed by Index Recheck: 0
  • Filter: ((m_assemblies.serial_types)[1] = root_serial_type)
88. 0.000 0.000 ↑ 1.0 4 121,832

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

89. 0.036 0.036 ↑ 1.0 4 1

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

  • Filter: (name = ANY (ARRAY[$32, $33, $34]))
  • Rows Removed by Filter: 94
90. 82.383 82.383 ↑ 1.0 1 11,769

Index Scan using logs_v2_pkey on logs_v2 logs_v2_2 (cost=0.57..1.02 rows=1 width=821) (actual time=0.007..0.007 rows=1 loops=11,769)

  • Index Cond: (id = m_logs_v2.id)
91. 2,730.408 2,730.408 ↑ 1.0 1 11,769

Function Scan on jsonb_to_recordset raw_data (cost=0.01..1.25 rows=1 width=96) (actual time=0.212..0.232 rows=1 loops=11,769)

  • Filter: (sub_test = $31)
  • Rows Removed by Filter: 382
92.          

CTE tmp_result

93. 1.836 8,350.238 ↓ 7.0 4,801 1

Nested Loop (cost=23.68..32,598.47 rows=686 width=424) (actual time=8,289.490..8,350.238 rows=4,801 loops=1)

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

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

95. 3.040 8,331.942 ↓ 3.5 4,801 1

Nested Loop Left Join (cost=23.68..32,581.29 rows=1,373 width=424) (actual time=8,273.026..8,331.942 rows=4,801 loops=1)

96. 8,285.693 8,285.693 ↓ 3.5 4,801 1

CTE Scan on tmp_his (cost=0.00..27.46 rows=1,373 width=392) (actual time=8,272.953..8,285.693 rows=4,801 loops=1)

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

98. 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)
99. 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)

100. 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
101. 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)

102. 1.276 8,355.321 ↑ 11.0 2 1

HashAggregate (cost=25.73..30.34 rows=22 width=104) (actual time=8,355.320..8,355.321 rows=2 loops=1)

  • Group Key: tmp_result.line
  • Filter: ((sum(tmp_result.failed) > '0'::numeric) AND (count(*) >= 1))
  • Rows Removed by Filter: 1
103. 8,354.045 8,354.045 ↓ 7.0 4,801 1

CTE Scan on tmp_result (cost=0.00..13.72 rows=686 width=40) (actual time=8,289.493..8,354.045 rows=4,801 loops=1)

Planning time : 11.648 ms
Execution time : 8,360.878 ms