explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DQ59

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 2,106.396 ↑ 1.0 2 1

Sort (cost=334,980.76..334,980.76 rows=2 width=104) (actual time=2,106.396..2,106.396 rows=2 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.006 0.041 ↑ 4,100,000.0 1 1

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

6. 0.004 0.035 ↑ 41,000.0 1 1

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

7. 0.010 0.010 ↑ 410.0 1 1

CTE Scan on args (cost=0.00..8.20 rows=410 width=32) (actual time=0.009..0.010 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.021..0.021 rows=1 loops=1)

9.          

CTE option_choices

10. 0.021 0.063 ↑ 25.5 29 1

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

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

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

12. 0.010 0.022 ↑ 53.6 14 1

Hash (cost=17.50..17.50 rows=750 width=48) (actual time=0.021..0.022 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.393 ↑ 1.0 1 1

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

16. 21.239 21.385 ↑ 92.5 2 1

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

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

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

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

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

19. 0.002 0.051 ↑ 200.0 1 1

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

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

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

  • Group Key: args_choices.option_name
21. 0.043 0.043 ↑ 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.039..0.043 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. 1.515 288.887 ↓ 154.9 4,801 1

Unique (cost=39,225.86..39,226.09 rows=31 width=24) (actual time=286.439..288.887 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.000..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.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_3 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 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.152 287.368 ↓ 223.8 6,937 1

Sort (cost=39,193.06..39,193.14 rows=31 width=24) (actual time=286.438..287.368 rows=6,937 loops=1)

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

Nested Loop (cost=0.72..39,192.29 rows=31 width=24) (actual time=141.170..270.216 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.708 252.354 ↓ 49.7 114,826 2

Nested Loop (cost=0.72..39,160.49 rows=2,310 width=40) (actual time=0.027..126.177 rows=114,826 loops=2)

35. 0.008 0.024 ↑ 3.0 1 2

Nested Loop (cost=0.15..90.57 rows=3 width=16) (actual time=0.009..0.012 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.001 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.005 0.005 ↑ 4.0 1 1

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

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

Index Scan using m_logs_v2_component_id_first_time_idx on m_logs_v2 (cost=0.57..10,481.95 rows=254,136 width=56) (actual time=0.015..116.311 rows=114,826 loops=2)

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

CTE tmp_his

41. 32.227 2,029.901 ↓ 154.9 4,801 1

GroupAggregate (cost=47,783.25..47,787.89 rows=31 width=296) (actual time=1,996.972..2,029.901 rows=4,801 loops=1)

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

Initplan (forGroupAggregate)

43. 0.000 0.000 ↑ 410.0 1 1

CTE Scan on args args_5 (cost=0.00..8.20 rows=410 width=32) (actual time=0.000..0.000 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.001..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.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.002..0.002 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. 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)

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

52. 0.001 0.001 ↑ 410.0 1 1

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

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

54. 11.533 1,997.663 ↓ 143.3 9,601 1

Sort (cost=47,693.05..47,693.21 rows=67 width=975) (actual time=1,996.920..1,997.663 rows=9,601 loops=1)

  • Sort Key: tmp_sn.serial_type, tmp_sn.serial
  • Sort Method: quicksort Memory: 17940kB
55. 39.843 1,986.130 ↓ 143.3 9,601 1

Nested Loop Left Join (cost=0.57..47,691.02 rows=67 width=975) (actual time=286.828..1,986.130 rows=9,601 loops=1)

  • Join Filter: (processes_1.name = $21)
56. 3.059 486.935 ↓ 143.3 9,601 1

Nested Loop (cost=0.57..47,606.42 rows=67 width=943) (actual time=286.512..486.935 rows=9,601 loops=1)

57. 61.448 426.270 ↓ 143.3 9,601 1

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

  • Join Filter: (m_logs_v2_1.process_id = processes_1.id)
  • Rows Removed by Join Filter: 440227
58. 12.578 364.822 ↓ 91.4 113,657 1

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

59. 289.831 289.831 ↓ 154.9 4,801 1

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

60. 62.413 62.413 ↑ 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.013 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
61. 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)

62. 0.037 0.037 ↑ 1.0 4 1

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

  • Filter: (name = ANY (ARRAY[$17, $18, $19]))
  • Rows Removed by Filter: 94
63. 57.606 57.606 ↑ 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.006..0.006 rows=1 loops=9,601)

  • Index Cond: (id = m_logs_v2_1.id)
64. 1,459.352 1,459.352 ↑ 1.0 1 9,601

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

  • Filter: (sub_test = $20)
  • Rows Removed by Filter: 325
65.          

CTE tmp_result

66. 1.723 2,102.417 ↓ 300.1 4,801 1

Nested Loop (cost=23.68..736.04 rows=16 width=328) (actual time=2,018.413..2,102.417 rows=4,801 loops=1)

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

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

68. 3.503 2,079.300 ↓ 154.9 4,801 1

Nested Loop Left Join (cost=23.68..735.63 rows=31 width=328) (actual time=1,997.014..2,079.300 rows=4,801 loops=1)

69. 2,032.588 2,032.588 ↓ 154.9 4,801 1

CTE Scan on tmp_his (cost=0.00..0.62 rows=31 width=296) (actual time=1,996.973..2,032.588 rows=4,801 loops=1)

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

71. 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)
72. 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)

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

75. 1.241 2,106.390 ↑ 1.0 2 1

HashAggregate (cost=0.60..0.97 rows=2 width=104) (actual time=2,106.389..2,106.390 rows=2 loops=1)

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

CTE Scan on tmp_result (cost=0.00..0.32 rows=16 width=40) (actual time=2,018.415..2,105.149 rows=4,801 loops=1)

Planning time : 3.016 ms
Execution time : 2,109.556 ms