explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dMaq

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 7,022.472 ↓ 6.0 12 1

Sort (cost=335,038.46..335,038.46 rows=2 width=104) (actual time=7,022.471..7,022.472 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.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.037..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.024 0.064 ↑ 25.5 29 1

Hash Join (cost=26.88..46.23 rows=740 width=80) (actual time=0.043..0.064 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.009 20.794 ↑ 1.0 1 1

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

16. 20.636 20.785 ↑ 92.5 2 1

Hash Anti Join (cost=225,504.50..225,532.78 rows=185 width=48) (actual time=20.765..20.785 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.078 0.078 ↑ 25.5 29 1

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

19. 0.001 0.050 ↑ 200.0 1 1

Hash (cost=92,252.00..92,252.00 rows=200 width=32) (actual time=0.050..0.050 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.004 0.007 ↑ 4,100,000.0 1 1

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

  • Buckets: 4194304 Batches: 1 Memory Usage: 32769kB
23. 0.003 0.003 ↑ 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.003..0.003 rows=1 loops=1)

24.          

CTE tmp_sn

25. 2.319 295.512 ↓ 154.9 4,801 1

Unique (cost=39,234.68..39,234.91 rows=31 width=24) (actual time=291.598..295.512 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.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.898 293.188 ↓ 223.8 6,937 1

Sort (cost=39,201.88..39,201.95 rows=31 width=24) (actual time=291.596..293.188 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.789 275.290 ↓ 223.8 6,937 1

Nested Loop (cost=0.72..39,201.11 rows=31 width=24) (actual time=143.065..275.290 rows=6,937 loops=1)

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

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

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

Nested Loop (cost=0.72..39,169.29 rows=2,311 width=40) (actual time=0.028..128.237 rows=114,826 loops=2)

35. 0.010 0.028 ↑ 3.0 1 2

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

  • Join Filter: (components.project_id = projects.id)
  • Rows Removed by Join Filter: 2
36. 0.012 0.012 ↑ 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.006 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. 236.376 236.376 ↑ 2.2 114,826 2

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

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

CTE tmp_his

41. 6.734 6,951.108 ↓ 154.9 4,801 1

GroupAggregate (cost=47,835.12..47,836.77 rows=31 width=136) (actual time=6,943.613..6,951.108 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.001..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. 9.340 6,944.367 ↓ 272.3 18,245 1

Sort (cost=47,777.72..47,777.89 rows=67 width=226) (actual time=6,943.587..6,944.367 rows=18,245 loops=1)

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

Nested Loop Left Join (cost=0.58..47,775.69 rows=67 width=226) (actual time=292.394..6,935.027 rows=18,245 loops=1)

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

Nested Loop Left Join (cost=0.57..47,691.10 rows=67 width=975) (actual time=291.991..3,454.056 rows=9,601 loops=1)

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

Nested Loop (cost=0.57..47,606.51 rows=67 width=943) (actual time=291.675..519.654 rows=9,601 loops=1)

54. 64.821 446.082 ↓ 143.3 9,601 1

Nested Loop (cost=0.00..47,539.05 rows=67 width=154) (actual time=291.662..446.082 rows=9,601 loops=1)

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

Nested Loop (cost=0.00..47,461.99 rows=1,244 width=162) (actual time=291.613..381.261 rows=113,657 loops=1)

56. 297.114 297.114 ↓ 154.9 4,801 1

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

57. 72.015 72.015 ↑ 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.015 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.017..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,851.497 2,851.497 ↑ 1.0 1 9,601

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

  • Filter: (sub_test = $14)
  • Rows Removed by Filter: 624
62. 3,398.754 3,398.754 ↓ 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.345..0.354 rows=2 loops=9,601)

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

CTE tmp_result

64. 1.551 7,019.498 ↓ 300.1 4,801 1

Nested Loop (cost=23.68..736.04 rows=16 width=168) (actual time=6,964.492..7,019.498 rows=4,801 loops=1)

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

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

66. 1.340 6,997.149 ↓ 154.9 4,801 1

Nested Loop Left Join (cost=23.68..735.63 rows=31 width=168) (actual time=6,943.690..6,997.149 rows=4,801 loops=1)

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

CTE Scan on tmp_his (cost=0.00..0.62 rows=31 width=136) (actual time=6,943.616..6,952.600 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.176 7,022.458 ↓ 6.0 12 1

HashAggregate (cost=0.60..0.97 rows=2 width=104) (actual time=7,022.441..7,022.458 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. 7,021.282 7,021.282 ↓ 300.1 4,801 1

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

Planning time : 3.051 ms
Execution time : 7,026.217 ms