explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qSUS

Settings
# exclusive inclusive rows x rows loops node
1. 1.320 11,235.056 ↑ 159.9 6,555 1

Nested Loop Left Join (cost=7,456,019.04..7,056,721,564.52 rows=1,048,200 width=128) (actual time=3,934.687..11,235.056 rows=6,555 loops=1)

2.          

CTE args

3. 0.990 0.990 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=64) (actual time=0.989..0.990 rows=1 loops=1)

4.          

CTE process_ids1

5. 0.040 1.033 ↓ 3.0 3 1

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

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

Initplan (for Seq Scan)

7. 0.993 0.993 ↑ 1.0 1 1

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

8.          

CTE tmp_sn

9. 1.141 17.562 ↑ 76.0 69 1

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

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

Initplan (for Hash Semi Join)

11. 0.001 0.001 ↑ 1.0 1 1

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

12. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on args args_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 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. 15.370 15.370 ↑ 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.053..15.370 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.003 1.048 ↓ 3.0 3 1

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

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

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

17.          

CTE process_ids

18. 0.037 0.037 ↑ 1.3 18 1

Seq Scan on processes processes_1 (cost=0.00..5.55 rows=24 width=16) (actual time=0.010..0.037 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,600.414 3,628.672 ↑ 1,163.1 46,161 1

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

21. 0.037 0.037 ↑ 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..0.037 rows=69 loops=1)

22. 3.782 28.221 ↑ 15.3 669 69

Materialize (cost=0.57..4,029,592.52 rows=10,244 width=794) (actual time=0.172..0.409 rows=669 loops=69)

23. 0.597 24.439 ↑ 15.3 669 1

Nested Loop (cost=0.57..4,029,541.30 rows=10,244 width=794) (actual time=11.878..24.439 rows=669 loops=1)

24. 2.188 19.159 ↑ 15.3 669 1

Nested Loop (cost=0.00..4,014,811.51 rows=10,244 width=111) (actual time=11.863..19.159 rows=669 loops=1)

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

Nested Loop (cost=0.00..3,990,681.36 rows=10,244 width=136) (actual time=11.849..16.302 rows=669 loops=1)

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

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

27. 2.192 12.942 ↑ 18.1 1,651 18

Materialize (cost=0.00..3,979,999.86 rows=29,877 width=120) (actual time=0.001..0.719 rows=1,651 loops=18)

28. 0.507 10.750 ↑ 18.1 1,651 1

Nested Loop (cost=0.00..3,979,850.47 rows=29,877 width=120) (actual time=0.012..10.750 rows=1,651 loops=1)

29. 8.656 8.656 ↑ 76.0 69 1

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

30. 1.587 1.587 ↑ 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.023 rows=24 loops=69)

  • Index Cond: (root_serial = tmp_sn_2.serial)
  • Filter: (tmp_sn_2.serial_type = root_serial_type)
31. 0.659 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.010 0.010 ↑ 4.0 40 1

Seq Scan on processes processes_2 (cost=0.00..3.58 rows=158 width=23) (actual time=0.005..0.010 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. 9.023 9.023 ↑ 76.0 69 1

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

35. 280.485 11,224.713 ↑ 2.1 95 69

GroupAggregate (cost=1,345,211.76..1,345,275.65 rows=200 width=64) (actual time=158.634..162.677 rows=95 loops=69)

  • Group Key: (to_char((tmp_data.created + '08:00:00'::interval), 'YYYY-MM-DD HH24:MI:SS'::text))
36. 50.577 10,944.228 ↑ 13.6 594 69

Sort (cost=1,345,211.76..1,345,231.89 rows=8,052 width=120) (actual time=158.579..158.612 rows=594 loops=69)

  • Sort Key: (to_char((tmp_data.created + '08:00:00'::interval), 'YYYY-MM-DD HH24:MI:SS'::text))
  • Sort Method: quicksort Memory: 108kB
37. 57.408 10,893.651 ↑ 13.6 594 69

Nested Loop (cost=0.00..1,344,689.38 rows=8,052 width=120) (actual time=34.832..157.879 rows=594 loops=69)

38. 8,616.168 8,616.168 ↑ 13.6 99 69

CTE Scan on tmp_data (cost=0.00..1,342,220.10 rows=1,342 width=40) (actual time=34.496..124.872 rows=99 loops=69)

  • Filter: ((serial = tmp_sn.serial) AND (process = 'ump-t1'::text))
  • Rows Removed by Filter: 46062
39. 2,220.075 2,220.075 ↑ 1.0 6 6,831

Function Scan on jsonb_to_recordset raw_data (cost=0.00..1.75 rows=6 width=88) (actual time=0.319..0.325 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.469 ms
Execution time : 11,244.539 ms