explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nWaB

Settings
# exclusive inclusive rows x rows loops node
1. 6,219.240 6,219.240 ↑ 2.9 69 1

CTE Scan on t1_data (cost=7,905,360.83..7,905,364.83 rows=200 width=416) (actual time=6,219.147..6,219.240 rows=69 loops=1)

2.          

CTE args

3. 1.017 1.017 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=64) (actual time=1.016..1.017 rows=1 loops=1)

4.          

CTE process_ids1

5. 0.044 1.065 ↓ 3.0 3 1

Seq Scan on processes (cost=0.02..4.00 rows=1 width=16) (actual time=1.045..1.065 rows=3 loops=1)

  • Filter: (name = $1)
  • Rows Removed by Filter: 94
6.          

Initplan (for Seq Scan)

7. 1.021 1.021 ↑ 1.0 1 1

CTE Scan on args (cost=0.00..0.02 rows=1 width=32) (actual time=1.020..1.021 rows=1 loops=1)

8.          

CTE tmp_sn

9. 1.121 18.680 ↑ 76.0 69 1

Hash Semi Join (cost=0.66..202,017.60 rows=5,241 width=24) (actual time=9.515..18.680 rows=69 loops=1)

  • Hash Cond: (m_logs_v2.process_id = process_ids1.id)
10.          

Initplan (for Hash Semi Join)

11. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on args args_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1)

12. 0.002 0.002 ↑ 1.0 1 1

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

13. 0.001 0.001 ↑ 1.0 1 1

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

14. 16.480 16.480 ↑ 44.9 8,173 1

Index Scan using m_logs_v2_component_id_result_first_time_idx on m_logs_v2 (cost=0.57..200,996.23 rows=366,845 width=40) (actual time=0.080..16.480 rows=8,173 loops=1)

  • Index Cond: ((component_id = $3) AND (result = ANY ('{passed,failed,scrapped}'::log_state[])) AND (first_time >= $4) AND (first_time < $5))
  • Filter: last
  • Rows Removed by Filter: 1718
15. 0.005 1.074 ↓ 3.0 3 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=1.073..1.074 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 1.069 1.069 ↓ 3.0 3 1

CTE Scan on process_ids1 (cost=0.00..0.02 rows=1 width=16) (actual time=1.047..1.069 rows=3 loops=1)

17.          

CTE process_ids

18. 0.038 0.038 ↑ 1.3 18 1

Seq Scan on processes processes_1 (cost=0.00..5.55 rows=24 width=16) (actual time=0.011..0.038 rows=18 loops=1)

  • Filter: (name = ANY ('{sp-wld,mlb-nut-wld,e75-nut-wld,spk-nut-wld,arc-nut-wld,bg-assy,ump-t1,ump-t3,isra,alt-bg}'::text[]))
  • Rows Removed by Filter: 79
19.          

CTE tmp_data

20. 3,609.885 3,649.195 ↑ 1,163.1 46,161 1

Nested Loop Left Join (cost=0.57..5,908,783.19 rows=53,688,804 width=415) (actual time=22.369..3,649.195 rows=46,161 loops=1)

21. 9.571 9.571 ↑ 76.0 69 1

CTE Scan on tmp_sn (cost=0.00..104.82 rows=5,241 width=64) (actual time=9.519..9.571 rows=69 loops=1)

22. 4.513 29.739 ↑ 15.3 669 69

Materialize (cost=0.57..4,029,595.84 rows=10,244 width=794) (actual time=0.183..0.431 rows=669 loops=69)

23. 0.663 25.226 ↑ 15.3 669 1

Nested Loop (cost=0.57..4,029,544.62 rows=10,244 width=794) (actual time=12.581..25.226 rows=669 loops=1)

24. 2.175 19.880 ↑ 15.3 669 1

Nested Loop (cost=0.00..4,014,814.81 rows=10,244 width=111) (actual time=12.566..19.880 rows=669 loops=1)

  • Join Filter: (m_logs_v2_1.process_id = processes_2.id)
  • Rows Removed by Join Filter: 17862
25. 3.363 17.036 ↑ 15.3 669 1

Nested Loop (cost=0.00..3,990,684.66 rows=10,244 width=136) (actual time=12.555..17.036 rows=669 loops=1)

  • Join Filter: (m_logs_v2_1.process_id = process_ids.id)
  • Rows Removed by Join Filter: 29049
26. 0.047 0.047 ↑ 1.3 18 1

CTE Scan on process_ids (cost=0.00..0.48 rows=24 width=16) (actual time=0.012..0.047 rows=18 loops=1)

27. 2.256 13.626 ↑ 18.1 1,651 18

Materialize (cost=0.00..3,980,003.16 rows=29,877 width=120) (actual time=0.001..0.757 rows=1,651 loops=18)

28. 0.501 11.370 ↑ 18.1 1,651 1

Nested Loop (cost=0.00..3,979,853.77 rows=29,877 width=120) (actual time=0.023..11.370 rows=1,651 loops=1)

29. 9.213 9.213 ↑ 76.0 69 1

CTE Scan on tmp_sn tmp_sn_1 (cost=0.00..104.82 rows=5,241 width=64) (actual time=0.000..9.213 rows=69 loops=1)

30. 1.656 1.656 ↑ 11.9 24 69

Index Scan using m_logs_v2_root_serial_idx on m_logs_v2 m_logs_v2_1 (cost=0.00..756.50 rows=285 width=144) (actual time=0.005..0.024 rows=24 loops=69)

  • Index Cond: (root_serial = tmp_sn_1.serial)
  • Filter: (tmp_sn_1.serial_type = root_serial_type)
31. 0.658 0.669 ↑ 5.6 28 669

Materialize (cost=0.00..4.37 rows=158 width=23) (actual time=0.000..0.001 rows=28 loops=669)

32. 0.011 0.011 ↑ 4.0 40 1

Seq Scan on processes processes_2 (cost=0.00..3.58 rows=158 width=23) (actual time=0.005..0.011 rows=40 loops=1)

33. 4.683 4.683 ↑ 1.0 1 669

Index Scan using logs_v2_pkey on logs_v2 (cost=0.57..1.44 rows=1 width=715) (actual time=0.007..0.007 rows=1 loops=669)

  • Index Cond: (id = m_logs_v2_1.id)
34.          

CTE t1_data

35. 42.154 6,219.206 ↑ 2.9 69 1

HashAggregate (cost=1,794,548.23..1,794,550.23 rows=200 width=416) (actual time=6,219.145..6,219.206 rows=69 loops=1)

  • Group Key: tmp_data.serial
36. 22.525 6,177.052 ↑ 39.3 40,986 1

Nested Loop (cost=0.00..1,693,881.73 rows=1,610,664 width=128) (actual time=22.741..6,177.052 rows=40,986 loops=1)

37. 3,859.311 3,859.311 ↑ 39.3 6,831 1

CTE Scan on tmp_data (cost=0.00..1,207,998.09 rows=268,444 width=64) (actual time=22.386..3,859.311 rows=6,831 loops=1)

  • Filter: (process = 'ump-t1'::text)
  • Rows Removed by Filter: 39330
38. 2,295.216 2,295.216 ↑ 1.0 6 6,831

Function Scan on jsonb_to_recordset raw_data (cost=0.00..1.75 rows=6 width=96) (actual time=0.331..0.336 rows=6 loops=6,831)

  • Filter: (sub_test = ANY ('{413-1.L,413-2.L,413-3.L,413.L,414-1.L,414-2.L}'::text[]))
  • Rows Removed by Filter: 222
Planning time : 3.591 ms
Execution time : 6,228.622 ms