explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uJj7

Settings
# exclusive inclusive rows x rows loops node
1. 0.407 10,536.867 ↑ 1,085,075.6 119 1

Nested Loop Left Join (cost=1,899,740.75..1,002,049,280.21 rows=129,124,000 width=739) (actual time=9,790.896..10,536.867 rows=119 loops=1)

2.          

CTE args

3. 0.020 0.020 ↑ 1.0 1 1

Seq Scan on sites (cost=0.00..1.03 rows=1 width=96) (actual time=0.019..0.020 rows=1 loops=1)

4.          

CTE proj_process

5. 0.022 0.046 ↓ 12.0 12 1

Hash Join (cost=0.04..0.29 rows=1 width=128) (actual time=0.027..0.046 rows=12 loops=1)

  • Hash Cond: (("*VALUES*".column1 = args.site) AND ("*VALUES*".column2 = args.project))
6. 0.021 0.021 ↑ 1.0 12 1

Values Scan on "*VALUES*" (cost=0.00..0.15 rows=12 width=128) (actual time=0.008..0.021 rows=12 loops=1)

7. 0.002 0.003 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=64) (actual time=0.003..0.003 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.001 0.001 ↑ 1.0 1 1

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

9.          

CTE t_logs

10. 0.246 10,507.136 ↑ 71.4 119 1

Nested Loop (cost=1.20..1,899,734.65 rows=8,495 width=847) (actual time=9,790.763..10,507.136 rows=119 loops=1)

11.          

Initplan (for Nested Loop)

12. 0.025 0.025 ↑ 1.0 1 1

CTE Scan on args args_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.023..0.025 rows=1 loops=1)

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

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

15. 23.166 10,504.721 ↑ 71.4 119 1

Nested Loop (cost=0.57..1,842,840.71 rows=8,495 width=201) (actual time=9,790.682..10,504.721 rows=119 loops=1)

  • Join Filter: (m_logs_v2_1.process_id = processes_1.id)
  • Rows Removed by Join Filter: 100,328
16. 10.221 10,481.555 ↑ 8.3 50,229 1

Nested Loop (cost=0.57..1,830,392.04 rows=416,264 width=210) (actual time=9,790.626..10,481.555 rows=50,229 loops=1)

17. 0.004 0.052 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.07 rows=1 width=80) (actual time=0.048..0.052 rows=1 loops=1)

  • Join Filter: (components_1.project_id = projects_1.id)
  • Rows Removed by Join Filter: 1
18. 0.042 0.042 ↑ 1.0 1 1

Seq Scan on projects projects_1 (cost=0.00..1.02 rows=1 width=48) (actual time=0.040..0.042 rows=1 loops=1)

  • Filter: (name = $3)
  • Rows Removed by Filter: 1
19. 0.006 0.006 ↑ 1.0 2 1

Seq Scan on components components_1 (cost=0.00..1.02 rows=2 width=64) (actual time=0.005..0.006 rows=2 loops=1)

20. 10,471.282 10,471.282 ↑ 8.3 50,229 1

Index Scan using m_logs_v2_component_id_result_created_idx on m_logs_v2 m_logs_v2_1 (cost=0.57..1,826,237.33 rows=415,264 width=162) (actual time=9,790.570..10,471.282 rows=50,229 loops=1)

  • Index Cond: ((component_id = components_1.id) AND (created >= $4) AND (created < $5))
21. 0.000 0.000 ↑ 1.0 2 50,229

Materialize (cost=0.00..3.23 rows=2 width=23) (actual time=0.000..0.000 rows=2 loops=50,229)

22. 0.017 0.017 ↑ 1.0 2 1

Seq Scan on processes processes_1 (cost=0.00..3.23 rows=2 width=23) (actual time=0.013..0.017 rows=2 loops=1)

  • Filter: (name = 'ort-final'::text)
  • Rows Removed by Filter: 103
23. 0.714 0.714 ↑ 1.0 1 119

Index Scan using logs_v2_pkey on logs_v2 lv_1 (cost=0.57..2.38 rows=1 width=714) (actual time=0.006..0.006 rows=1 loops=119)

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

SubPlan (for Nested Loop)

25. 0.714 1.428 ↑ 1.0 1 119

Aggregate (cost=4.30..4.31 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=119)

26. 0.230 0.714 ↑ 7.5 4 119

Hash Join (cost=2.86..4.22 rows=30 width=64) (actual time=0.003..0.006 rows=4 loops=119)

  • Hash Cond: (choices.option_id = options.id)
27. 0.223 0.476 ↑ 7.5 4 119

Hash Join (cost=1.68..2.95 rows=30 width=48) (actual time=0.003..0.004 rows=4 loops=119)

  • Hash Cond: (u.choice_id = choices.id)
28. 0.238 0.238 ↑ 25.0 4 119

Function Scan on unnest u (cost=0.00..1.00 rows=100 width=16) (actual time=0.002..0.002 rows=4 loops=119)

29. 0.007 0.015 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=64) (actual time=0.014..0.015 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
30. 0.008 0.008 ↑ 1.0 30 1

Seq Scan on choices (cost=0.00..1.30 rows=30 width=64) (actual time=0.003..0.008 rows=30 loops=1)

31. 0.003 0.008 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=48) (actual time=0.007..0.008 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
32. 0.005 0.005 ↑ 1.0 8 1

Seq Scan on options (cost=0.00..1.08 rows=8 width=48) (actual time=0.004..0.005 rows=8 loops=1)

33. 0.119 10,507.781 ↑ 7,138.7 119 1

Nested Loop (cost=0.01..17,159.90 rows=849,500 width=438) (actual time=9,790.779..10,507.781 rows=119 loops=1)

34. 10,507.305 10,507.305 ↑ 71.4 119 1

CTE Scan on t_logs (cost=0.00..169.90 rows=8,495 width=246) (actual time=9,790.767..10,507.305 rows=119 loops=1)

35. 0.357 0.357 ↑ 100.0 1 119

Function Scan on jsonb_to_recordset measurement_data (cost=0.01..1.00 rows=100 width=192) (actual time=0.003..0.003 rows=1 loops=119)

36. 0.142 28.679 ↓ 0.0 0 119

Nested Loop (cost=4.78..1,167.06 rows=152 width=77) (actual time=0.182..0.241 rows=0 loops=119)

37. 4.845 28.322 ↓ 0.0 0 119

Hash Left Join (cost=4.21..741.08 rows=152 width=29) (actual time=0.179..0.238 rows=0 loops=119)

  • Hash Cond: (m_logs_v2.process_id = processes.id)
  • Filter: CASE WHEN (measurement_data.test = proj_process.ort) THEN (processes.name = proj_process.pro) ELSE (processes.name = ''::text) END
  • Rows Removed by Filter: 145
38. 4.760 23.443 ↑ 2.1 145 119

Nested Loop (cost=0.00..736.02 rows=304 width=102) (actual time=0.007..0.197 rows=145 loops=119)

  • Join Filter: (components.id = m_logs_v2.component_id)
39. 1.904 4.403 ↓ 12.0 12 119

Nested Loop (cost=0.00..2.11 rows=1 width=80) (actual time=0.004..0.037 rows=12 loops=119)

  • Join Filter: (projects.id = components.project_id)
  • Rows Removed by Join Filter: 12
40. 2.142 2.499 ↓ 12.0 12 119

Nested Loop (cost=0.00..1.06 rows=1 width=80) (actual time=0.003..0.021 rows=12 loops=119)

  • Join Filter: (projects.name = proj_process.project)
  • Rows Removed by Join Filter: 12
41. 0.357 0.357 ↓ 12.0 12 119

CTE Scan on proj_process (cost=0.00..0.02 rows=1 width=96) (actual time=0.000..0.003 rows=12 loops=119)

42. 0.000 0.000 ↑ 1.0 2 1,428

Seq Scan on projects (cost=0.00..1.02 rows=2 width=48) (actual time=0.000..0.000 rows=2 loops=1,428)

43. 0.000 0.000 ↑ 1.0 2 1,428

Seq Scan on components (cost=0.00..1.02 rows=2 width=32) (actual time=0.000..0.000 rows=2 loops=1,428)

44. 14.280 14.280 ↑ 50.6 12 1,428

Index Scan using m_logs_v2_root_serial_idx on m_logs_v2 (cost=0.00..726.32 rows=607 width=54) (actual time=0.001..0.010 rows=12 loops=1,428)

  • Index Cond: (t_logs.root_serial = root_serial)
  • Filter: last
  • Rows Removed by Filter: 7
45. 0.018 0.034 ↓ 1.1 105 1

Hash (cost=2.98..2.98 rows=98 width=23) (actual time=0.034..0.034 rows=105 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
46. 0.016 0.016 ↓ 1.1 105 1

Seq Scan on processes (cost=0.00..2.98 rows=98 width=23) (actual time=0.003..0.016 rows=105 loops=1)

47. 0.215 0.215 ↑ 1.0 1 43

Index Scan using logs_v2_pkey on logs_v2 lv (cost=0.57..2.79 rows=1 width=714) (actual time=0.005..0.005 rows=1 loops=43)

  • Index Cond: (id = m_logs_v2.id)
Planning time : 5.456 ms
Execution time : 10,537.208 ms