explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8kPh

Settings
# exclusive inclusive rows x rows loops node
1. 5.222 5.222 ↑ 1.0 1 1

CTE Scan on tmp_his (cost=24,924.12..24,924.14 rows=1 width=168) (actual time=5.222..5.222 rows=1 loops=1)

2.          

CTE args

3. 0.009 0.009 ↑ 410.0 1 1

Seq Scan on sites (cost=0.00..14.10 rows=410 width=208) (actual time=0.008..0.009 rows=1 loops=1)

4.          

CTE args_choices

5. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.01..21,635.71 rows=4,100,000 width=64) (never executed)

6. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.01..828.21 rows=41,000 width=64) (never executed)

7. 0.000 0.000 ↓ 0.0 0

CTE Scan on args (cost=0.00..8.20 rows=410 width=32) (never executed)

8. 0.000 0.000 ↓ 0.0 0

Function Scan on jsonb_each configs (cost=0.01..1.01 rows=100 width=64) (never executed)

9.          

CTE option_choices

10. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=26.88..46.23 rows=740 width=80) (never executed)

  • Hash Cond: (choices.option_id = options.id)
11. 0.000 0.000 ↓ 0.0 0

Seq Scan on choices (cost=0.00..17.40 rows=740 width=64) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Hash (cost=17.50..17.50 rows=750 width=48) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Seq Scan on options (cost=0.00..17.50 rows=750 width=48) (never executed)

14.          

CTE tmp_sn

15. 0.000 0.206 ↑ 1.0 1 1

Unique (cost=1,577.37..1,577.37 rows=1 width=24) (actual time=0.205..0.206 rows=1 loops=1)

16.          

Initplan (forUnique)

17. 0.002 0.002 ↑ 410.0 1 1

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

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

19. 0.015 0.015 ↑ 410.0 1 1

CTE Scan on args args_3 (cost=0.00..8.20 rows=410 width=32) (actual time=0.013..0.015 rows=1 loops=1)

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

21. 0.005 0.205 ↑ 1.0 1 1

Sort (cost=1,544.57..1,544.57 rows=1 width=24) (actual time=0.204..0.205 rows=1 loops=1)

  • Sort Key: m_logs_v2.root_serial_type
  • Sort Method: quicksort Memory: 25kB
22. 0.002 0.200 ↑ 1.0 1 1

Nested Loop (cost=0.30..1,544.56 rows=1 width=24) (actual time=0.169..0.200 rows=1 loops=1)

23. 0.003 0.188 ↑ 1.0 1 1

Nested Loop (cost=0.15..1,544.35 rows=1 width=40) (actual time=0.157..0.188 rows=1 loops=1)

24. 0.015 0.176 ↑ 1.0 1 1

Nested Loop (cost=0.00..1,542.93 rows=1 width=40) (actual time=0.146..0.176 rows=1 loops=1)

  • Join Filter: (m_logs_v2.process_id = processes.id)
  • Rows Removed by Join Filter: 47
25. 0.057 0.057 ↓ 2.0 2 1

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

  • Filter: (name = $6)
  • Rows Removed by Filter: 96
26. 0.104 0.104 ↓ 3.4 24 2

Index Scan using m_logs_v2_root_serial_idx on m_logs_v2 (cost=0.00..1,539.91 rows=7 width=56) (actual time=0.012..0.052 rows=24 loops=2)

  • Index Cond: (root_serial = 'FP293471Z21MRPQR804'::text)
  • Filter: ((first_time >= $4) AND (first_time < $5))
27. 0.009 0.009 ↑ 1.0 1 1

Index Scan using components_pkey on components (cost=0.15..1.42 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (id = m_logs_v2.component_id)
28. 0.010 0.010 ↑ 1.0 1 1

Index Scan using projects_pkey on projects (cost=0.15..0.19 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (id = components.project_id)
  • Filter: (name = $7)
29.          

CTE tmp_his

30. 0.012 5.219 ↑ 1.0 1 1

GroupAggregate (cost=1,650.27..1,650.71 rows=1 width=168) (actual time=5.219..5.219 rows=1 loops=1)

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

Initplan (forGroupAggregate)

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

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

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

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

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

37. 0.012 5.201 ↑ 8.5 2 1

Sort (cost=1,609.27..1,609.32 rows=17 width=290) (actual time=5.200..5.201 rows=2 loops=1)

  • Sort Key: tmp_sn.serial_type, tmp_sn.serial
  • Sort Method: quicksort Memory: 25kB
38. 0.024 5.189 ↑ 8.5 2 1

Nested Loop Left Join (cost=3.64..1,608.93 rows=17 width=290) (actual time=2.909..5.189 rows=2 loops=1)

  • Join Filter: (processes_1.name = 'ump-t1'::text)
  • Rows Removed by Join Filter: 1
39. 0.026 2.829 ↑ 8.5 2 1

Nested Loop Left Join (cost=3.64..1,583.21 rows=17 width=1,039) (actual time=1.608..2.829 rows=2 loops=1)

  • Join Filter: (processes_1.name = $15)
40. 0.005 0.419 ↑ 8.5 2 1

Nested Loop (cost=3.63..1,561.74 rows=17 width=943) (actual time=0.353..0.419 rows=2 loops=1)

41. 0.021 0.380 ↑ 8.5 2 1

Hash Join (cost=3.07..1,544.67 rows=17 width=154) (actual time=0.333..0.380 rows=2 loops=1)

  • Hash Cond: (m_logs_v2_1.process_id = processes_1.id)
42. 0.010 0.263 ↑ 12.9 24 1

Nested Loop (cost=0.00..1,540.74 rows=310 width=162) (actual time=0.216..0.263 rows=24 loops=1)

43. 0.208 0.208 ↑ 1.0 1 1

CTE Scan on tmp_sn (cost=0.00..0.02 rows=1 width=64) (actual time=0.206..0.208 rows=1 loops=1)

44. 0.045 0.045 ↑ 12.9 24 1

Index Scan using m_logs_v2_root_serial_idx on m_logs_v2 m_logs_v2_1 (cost=0.00..1,537.62 rows=310 width=122) (actual time=0.007..0.045 rows=24 loops=1)

  • Index Cond: (root_serial = tmp_sn.serial)
  • Filter: (last AND (tmp_sn.serial_type = root_serial_type))
45. 0.005 0.096 ↑ 1.0 4 1

Hash (cost=3.02..3.02 rows=4 width=24) (actual time=0.095..0.096 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 0.091 0.091 ↑ 1.0 4 1

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

  • Filter: (name = ANY (ARRAY[$11, $12, $13]))
  • Rows Removed by Filter: 94
47. 0.034 0.034 ↑ 1.0 1 2

Index Scan using logs_v2_pkey on logs_v2 (cost=0.57..1.00 rows=1 width=821) (actual time=0.017..0.017 rows=1 loops=2)

  • Index Cond: (id = m_logs_v2_1.id)
48. 2.384 2.384 ↓ 0.0 0 2

Function Scan on jsonb_to_recordset station (cost=0.01..1.25 rows=1 width=96) (actual time=1.143..1.192 rows=0 loops=2)

  • Filter: (sub_test = $14)
  • Rows Removed by Filter: 626
49. 2.336 2.336 ↑ 1.0 1 2

Function Scan on jsonb_to_recordset raw_data (cost=0.01..1.50 rows=1 width=64) (actual time=1.142..1.168 rows=1 loops=2)

  • Filter: ((test = 'CNC6'::text) AND (sub_test = 'MACHINE'::text))
  • Rows Removed by Filter: 626
Planning time : 3.242 ms
Execution time : 5.620 ms