explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WAqU

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 11,689.839 ↑ 1.8 12 1

Sort (cost=3,780,761,245.27..3,780,761,245.33 rows=22 width=104) (actual time=11,689.838..11,689.839 rows=12 loops=1)

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

CTE args

3. 0.013 0.013 ↑ 410.0 1 1

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

4.          

CTE args_choices

5. 0.008 0.052 ↑ 4,100,000.0 1 1

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

6. 0.002 0.044 ↑ 41,000.0 1 1

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

7. 0.020 0.020 ↑ 410.0 1 1

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

8. 0.022 0.022 ↑ 100.0 1 1

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

9.          

CTE option_choices

10. 0.036 0.071 ↑ 25.5 29 1

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

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

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

12. 0.011 0.020 ↑ 53.6 14 1

Hash (cost=17.50..17.50 rows=750 width=48) (actual time=0.020..0.020 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.005..0.009 rows=14 loops=1)

14.          

CTE exclude_choices

15. 0.007 18.692 ↑ 1.0 1 1

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

16. 18.507 18.685 ↑ 92.5 2 1

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

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

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

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

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

19. 0.003 0.061 ↑ 200.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.005 0.058 ↑ 200.0 1 1

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

  • Group Key: args_choices.option_name
21. 0.053 0.053 ↑ 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.051..0.053 rows=1 loops=1)

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

Hash (cost=82,000.00..82,000.00 rows=4,100,000 width=32) (actual time=0.006..0.006 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.001..0.002 rows=1 loops=1)

24.          

CTE tmp_agents

25. 0.005 0.099 ↑ 1.4 12 1

Unique (cost=26.70..26.78 rows=17 width=14) (actual time=0.090..0.099 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.022 0.093 ↓ 1.1 19 1

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

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

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

30. 0.019 0.019 ↓ 2.0 2 1

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

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

Bitmap Heap Scan on jobs (cost=1.52..15.03 rows=19 width=46) (actual time=0.016..0.023 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.024 0.024 ↑ 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.012..0.012 rows=10 loops=2)

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

CTE all_sn

34. 7.876 70.681 ↑ 2.0 7,005 1

HashAggregate (cost=68,979.74..69,117.07 rows=13,733 width=35) (actual time=65.886..70.681 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.001..0.001 rows=1 loops=1)

37. 0.001 0.001 ↑ 410.0 1 1

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

38. 0.720 62.803 ↑ 1.8 7,520 1

Nested Loop (cost=1.14..68,894.68 rows=13,733 width=35) (actual time=0.150..62.803 rows=7,520 loops=1)

39. 0.697 9.443 ↑ 1.6 7,520 1

Nested Loop (cost=0.57..13,821.07 rows=12,096 width=16) (actual time=0.131..9.443 rows=7,520 loops=1)

40. 0.106 0.106 ↑ 1.4 12 1

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

41. 8.640 8.640 ↑ 1.1 627 12

Index Scan using logs_v2_agent_id_created_idx on logs_v2 (cost=0.57..805.86 rows=712 width=22) (actual time=0.012..0.720 rows=627 loops=12)

  • Index Cond: ((agent_id = tmp_agents.agent_id) AND (created >= $8) AND (created < $9))
42. 52.640 52.640 ↑ 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.007..0.007 rows=1 loops=7,520)

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

CTE tmp_sn

44. 7.055 936.009 ↑ 2.9 4,801 1

Nested Loop (cost=37.73..293,581.93 rows=13,733 width=64) (actual time=66.349..936.009 rows=4,801 loops=1)

45.          

Initplan (forNested Loop)

46. 0.002 0.002 ↑ 410.0 1 1

CTE Scan on args args_4 (cost=0.00..8.20 rows=410 width=8) (actual time=0.002..0.002 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. 74.341 74.341 ↑ 2.0 7,005 1

CTE Scan on all_sn (cost=0.00..274.66 rows=13,733 width=64) (actual time=65.888..74.341 rows=7,005 loops=1)

49. 0.000 854.610 ↑ 1.0 1 7,005

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

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

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

51. 7.005 840.600 ↑ 1.0 1 7,005

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

  • Sort Key: logs_v2_1.created
  • Sort Method: quicksort Memory: 25kB
52. 13.375 833.595 ↓ 4.0 4 7,005

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

  • Hash Cond: (logs_v2_1.agent_id = tmp_agents_1.agent_id)
53. 61.614 819.585 ↓ 2.0 10 7,005

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

54. 217.155 217.155 ↓ 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.012..0.031 rows=10 loops=7,005)

  • Index Cond: ((serial_type = all_sn.serial_type) AND (serial = all_sn.serial))
55. 540.816 540.816 ↑ 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.008..0.008 rows=1 loops=67,602)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
57. 0.225 0.225 ↑ 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.225 rows=12 loops=1)

58.          

CTE tmp_his

59. 9.099 11,616.117 ↓ 3.5 4,801 1

GroupAggregate (cost=3,780,115,488.11..3,780,118,660.43 rows=1,373 width=392) (actual time=11,606.460..11,616.117 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.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)

65. 0.002 0.002 ↑ 410.0 1 1

CTE Scan on args args_10 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.002 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.001 0.001 ↑ 410.0 1 1

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

68. 0.002 0.002 ↑ 410.0 1 1

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

69. 0.006 0.006 ↑ 410.0 1 1

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

70. 31.294 11,607.001 ↑ 5.7 9,601 1

Sort (cost=3,780,115,414.31..3,780,115,551.64 rows=54,932 width=430) (actual time=11,606.382..11,607.001 rows=9,601 loops=1)

  • Sort Key: tmp_sn.serial_type, tmp_sn.serial
  • Sort Method: quicksort Memory: 2935kB
71. 7.212 11,575.707 ↑ 5.7 9,601 1

Nested Loop (cost=275,257.09..3,780,111,089.69 rows=54,932 width=430) (actual time=68.519..11,575.707 rows=9,601 loops=1)

72. 939.081 939.081 ↑ 2.9 4,801 1

CTE Scan on tmp_sn (cost=0.00..274.66 rows=13,733 width=64) (actual time=66.350..939.081 rows=4,801 loops=1)

73. 4.791 10,629.414 ↑ 2.0 2 4,801

Unique (cost=275,257.09..275,257.39 rows=4 width=406) (actual time=2.213..2.214 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.006 0.006 ↑ 410.0 1 1

CTE Scan on args args_17 (cost=0.00..8.20 rows=410 width=32) (actual time=0.006..0.006 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.001..0.001 rows=1 loops=1)

80. 19.204 10,624.613 ↑ 20.0 3 4,801

Sort (cost=275,216.09..275,216.24 rows=60 width=406) (actual time=2.213..2.213 rows=3 loops=4,801)

  • Sort Key: processes_1.name, m_logs_v2.created DESC
  • Sort Method: quicksort Memory: 25kB
81. 2,806.440 10,605.409 ↑ 20.0 3 4,801

Nested Loop Left Join (cost=282.95..275,214.32 rows=60 width=406) (actual time=0.778..2.209 rows=3 loops=4,801)

82. 130.304 5,420.329 ↑ 20.0 3 4,801

Nested Loop Left Join (cost=265.27..274,149.77 rows=60 width=983) (actual time=0.395..1.129 rows=3 loops=4,801)

  • Join Filter: (processes_1.name = $30)
83. 11.905 384.080 ↑ 20.0 3 4,801

Nested Loop (cost=265.26..274,074.01 rows=60 width=887) (actual time=0.036..0.080 rows=3 loops=4,801)

84. 76.816 264.055 ↑ 20.0 3 4,801

Nested Loop (cost=264.70..274,012.83 rows=60 width=98) (actual time=0.029..0.055 rows=3 loops=4,801)

  • Join Filter: (m_logs_v2.process_id = processes_1.id)
  • Rows Removed by Join Filter: 97
85. 24.005 187.239 ↑ 44.6 25 4,801

Nested Loop (cost=264.70..273,943.45 rows=1,115 width=106) (actual time=0.023..0.039 rows=25 loops=4,801)

86. 24.005 76.816 ↑ 163.0 1 4,801

Bitmap Heap Scan on m_assemblies (cost=264.70..32,322.91 rows=163 width=162) (actual time=0.016..0.016 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
87. 52.811 52.811 ↑ 32,647.0 1 4,801

Bitmap Index Scan on m_assemblies_serials_idx (cost=0.00..264.66 rows=32,647 width=0) (actual time=0.011..0.011 rows=1 loops=4,801)

  • Index Cond: (serials @> ARRAY[tmp_sn.serial])
88. 86.418 86.418 ↑ 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.004..0.018 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)
89. 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)

90. 0.038 0.038 ↑ 1.0 4 1

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

  • Filter: (name = ANY (ARRAY[$32, $33, $34]))
  • Rows Removed by Filter: 94
91. 108.120 108.120 ↑ 1.0 1 13,515

Index Scan using logs_v2_pkey on logs_v2 logs_v2_2 (cost=0.57..1.02 rows=1 width=821) (actual time=0.008..0.008 rows=1 loops=13,515)

  • Index Cond: (id = m_logs_v2.id)
92. 4,905.945 4,905.945 ↑ 1.0 1 13,515

Function Scan on jsonb_to_recordset raw_data (cost=0.01..1.25 rows=1 width=96) (actual time=0.346..0.363 rows=1 loops=13,515)

  • Filter: (sub_test = $31)
  • Rows Removed by Filter: 624
93. 27.029 2,378.640 ↑ 1.0 1 13,515

Aggregate (cost=17.68..17.69 rows=1 width=192) (actual time=0.176..0.176 rows=1 loops=13,515)

94.          

Initplan (forAggregate)

95. 0.000 0.000 ↑ 410.0 1 1

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

96. 0.001 0.001 ↑ 410.0 1 1

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

97. 62.466 2,351.610 ↑ 1.0 1 13,515

Result (cost=0.01..1.26 rows=1 width=64) (actual time=0.166..0.174 rows=1 loops=13,515)

  • One-Time Filter: (processes_1.name = $35)
98. 2,289.144 2,289.144 ↓ 3.0 3 6,578

Function Scan on jsonb_to_recordset raw_data_1 (cost=0.01..1.26 rows=1 width=64) (actual time=0.332..0.348 rows=3 loops=6,578)

  • Filter: (test = $36)
  • Rows Removed by Filter: 601
99.          

CTE tmp_result

100. 1.770 11,685.392 ↓ 7.0 4,801 1

Nested Loop (cost=23.68..32,598.47 rows=686 width=424) (actual time=11,625.233..11,685.392 rows=4,801 loops=1)

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

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

102. 2.965 11,664.920 ↓ 3.5 4,801 1

Nested Loop Left Join (cost=23.68..32,581.29 rows=1,373 width=424) (actual time=11,606.531..11,664.920 rows=4,801 loops=1)

103. 11,618.746 11,618.746 ↓ 3.5 4,801 1

CTE Scan on tmp_his (cost=0.00..27.46 rows=1,373 width=392) (actual time=11,606.465..11,618.746 rows=4,801 loops=1)

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

105. 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)
106. 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)

107. 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
108. 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)

109. 1.316 11,689.813 ↑ 1.8 12 1

HashAggregate (cost=25.73..30.34 rows=22 width=104) (actual time=11,689.795..11,689.813 rows=12 loops=1)

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

CTE Scan on tmp_result (cost=0.00..13.72 rows=686 width=40) (actual time=11,625.237..11,688.497 rows=4,801 loops=1)

Planning time : 8.425 ms
Execution time : 11,694.908 ms