explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sGOT

Settings
# exclusive inclusive rows x rows loops node
1. 61.702 310,641.460 ↓ 2.5 7,068 1

Limit (cost=282,252.36..294,038.41 rows=2,772 width=218) (actual time=106,979.618..310,641.460 rows=7,068 loops=1)

2. 173,501.905 310,579.758 ↓ 2.5 7,068 1

Nested Loop (cost=282,252.36..294,038.41 rows=2,772 width=218) (actual time=106,979.610..310,579.758 rows=7,068 loops=1)

3. 0.095 0.381 ↑ 1.0 1 1

Aggregate (cost=11.83..11.84 rows=1 width=218) (actual time=0.378..0.381 rows=1 loops=1)

4. 0.159 0.286 ↓ 5.0 15 1

Hash Join (cost=1.10..11.82 rows=3 width=218) (actual time=0.079..0.286 rows=15 loops=1)

  • Hash Cond: ((ida1.company_cd)::text = (cx1.company_cd)::text)
5. 0.080 0.080 ↑ 2.6 19 1

Seq Scan on imm_department_ath ida1 (cost=0.00..10.50 rows=50 width=436) (actual time=0.009..0.080 rows=19 loops=1)

6. 0.021 0.047 ↑ 1.0 3 1

Hash (cost=1.06..1.06 rows=3 width=6) (actual time=0.047..0.047 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
7. 0.026 0.026 ↑ 1.0 3 1

Seq Scan on cq_company_extension cx1 (cost=0.00..1.06 rows=3 width=6) (actual time=0.011..0.026 rows=3 loops=1)

  • Filter: ((super_company_flag)::text = '1'::text)
  • Rows Removed by Filter: 17
8. 231.874 137,077.472 ↓ 2.5 7,068 1

Nested Loop Left Join (cost=282,240.53..289,258.72 rows=2,772 width=186) (actual time=106,955.096..137,077.472 rows=7,068 loops=1)

  • Join Filter: (view1.machine = view2.machine)
  • Rows Removed by Join Filter: 35340
9. 74.654 136,675.966 ↓ 2.5 7,068 1

Hash Join (cost=282,227.45..289,120.82 rows=2,772 width=162) (actual time=106,954.289..136,675.966 rows=7,068 loops=1)

  • Hash Cond: (history.machine_id = view1.machine)
10. 19,355.851 136,592.027 ↑ 3.1 7,068 1

GroupAggregate (cost=282,217.71..288,778.49 rows=22,173 width=61) (actual time=106,944.977..136,592.027 rows=7,068 loops=1)

  • Group Key: history.machine_id, history.acquisition_time
11. 31,753.752 117,236.176 ↓ 8.0 2,417,256 1

Sort (cost=282,217.71..282,975.45 rows=303,094 width=61) (actual time=106,940.932..117,236.176 rows=2,417,256 loops=1)

  • Sort Key: history.machine_id, history.acquisition_time
  • Sort Method: external sort Disk: 102120kB
12. 19,205.050 85,482.424 ↓ 8.0 2,417,256 1

Nested Loop (cost=124.65..254,621.90 rows=303,094 width=61) (actual time=11.749..85,482.424 rows=2,417,256 loops=1)

13. 16.678 34.368 ↑ 1.1 342 1

Hash Join (cost=124.09..196.25 rows=369 width=16) (actual time=11.580..34.368 rows=342 loops=1)

  • Hash Cond: (mf_mapping.message_format_id = m_format.id)
14. 14.387 14.387 ↓ 1.0 3,573 1

Seq Scan on cq_message_format_mappings mf_mapping (cost=0.00..55.25 rows=3,525 width=16) (actual time=0.009..14.387 rows=3,573 loops=1)

15. 1.411 3.303 ↑ 1.0 342 1

Hash (cost=119.81..119.81 rows=342 width=8) (actual time=3.303..3.303 rows=342 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
16. 1.892 1.892 ↑ 1.0 342 1

Seq Scan on cq_message_formats m_format (cost=0.00..119.81 rows=342 width=8) (actual time=0.110..1.892 rows=342 loops=1)

  • Filter: (data_class_id = 484)
  • Rows Removed by Filter: 2923
17. 66,243.006 66,243.006 ↓ 3.9 7,068 342

Index Scan using cq_operation_data_history_index04 on cq_operation_data_history history (cost=0.56..671.34 rows=1,816 width=61) (actual time=0.085..193.693 rows=7,068 loops=342)

  • Index Cond: ((operation_data_id = mf_mapping.operation_data_id) AND (machine_id = 55))
  • Filter: ((acquisition_time >= '2000-01-01 00:00:00+00'::timestamp with time zone) AND (acquisition_time < '2100-01-01 00:00:00+00'::timestamp with time zone))
18. 0.123 9.285 ↓ 1.0 26 1

Hash (cost=9.43..9.43 rows=25 width=90) (actual time=9.285..9.285 rows=26 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.201 9.162 ↓ 1.0 26 1

Subquery Scan on view1 (cost=8.86..9.43 rows=25 width=90) (actual time=8.833..9.162 rows=26 loops=1)

20. 2.662 8.961 ↓ 1.0 26 1

HashAggregate (cost=8.86..9.18 rows=25 width=66) (actual time=8.826..8.961 rows=26 loops=1)

  • Group Key: machine.id, machine.machine_code, model_i.model_name, department.department_name, model_i.language_code
21. 2.469 6.299 ↓ 20.2 505 1

Hash Join (cost=5.34..8.49 rows=25 width=66) (actual time=3.593..6.299 rows=505 loops=1)

  • Hash Cond: ((arb.role_id)::text = (m_role.role_id)::text)
22. 0.276 0.276 ↑ 1.0 65 1

Seq Scan on b_m_account_role_b arb (cost=0.00..2.65 rows=65 width=23) (actual time=0.011..0.276 rows=65 loops=1)

23. 0.411 3.554 ↓ 20.8 83 1

Hash (cost=5.29..5.29 rows=4 width=71) (actual time=3.554..3.554 rows=83 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
24. 0.906 3.143 ↓ 20.8 83 1

Nested Loop (cost=1.59..5.29 rows=4 width=71) (actual time=0.318..3.143 rows=83 loops=1)

25. 0.615 1.639 ↓ 26.0 26 1

Nested Loop (cost=1.45..3.86 rows=1 width=58) (actual time=0.295..1.639 rows=26 loops=1)

  • Join Filter: ((department.locale_id)::text = (model_i.language_code)::text)
  • Rows Removed by Join Filter: 26
26. 0.262 0.608 ↓ 26.0 26 1

Hash Join (cost=1.31..2.46 rows=1 width=60) (actual time=0.269..0.608 rows=26 loops=1)

  • Hash Cond: ((machine.company_code)::text = (department.company_cd)::text)
27. 0.103 0.103 ↓ 2.2 22 1

Seq Scan on cq_machines machine (cost=0.00..1.10 rows=10 width=34) (actual time=0.007..0.103 rows=22 loops=1)

28. 0.120 0.243 ↓ 23.0 23 1

Hash (cost=1.30..1.30 rows=1 width=50) (actual time=0.243..0.243 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
29. 0.123 0.123 ↓ 23.0 23 1

Seq Scan on imm_department department (cost=0.00..1.30 rows=1 width=50) (actual time=0.013..0.123 rows=23 loops=1)

  • Filter: (((company_cd)::text = (department_set_cd)::text) AND ((department_set_cd)::text = (department_cd)::text) AND (start_date < now()) AND (end_date >= now()))
30. 0.416 0.416 ↑ 1.0 2 26

Index Scan using cq_models_international_index01 on cq_models_international model_i (cost=0.14..1.38 rows=2 width=17) (actual time=0.007..0.016 rows=2 loops=26)

  • Index Cond: (model_id = machine.model_id)
31. 0.598 0.598 ↑ 1.0 3 26

Index Only Scan using cq_machine_role_index01 on cq_machine_role m_role (cost=0.14..1.40 rows=3 width=21) (actual time=0.007..0.023 rows=3 loops=26)

  • Index Cond: (machine_id = machine.id)
  • Heap Fetches: 83
32. 168.906 169.632 ↓ 1.7 5 7,068

Materialize (cost=13.08..13.16 rows=3 width=40) (actual time=0.004..0.024 rows=5 loops=7,068)

33. 0.043 0.726 ↓ 1.7 5 1

Subquery Scan on view2 (cost=13.08..13.15 rows=3 width=40) (actual time=0.669..0.726 rows=5 loops=1)

34. 0.066 0.683 ↓ 1.7 5 1

HashAggregate (cost=13.08..13.12 rows=3 width=226) (actual time=0.661..0.683 rows=5 loops=1)

  • Group Key: machine_1.id
35. 0.062 0.617 ↓ 1.7 5 1

Hash Join (cost=2.31..13.06 rows=3 width=226) (actual time=0.433..0.617 rows=5 loops=1)

  • Hash Cond: ((cx1_1.company_cd)::text = (machine_1.company_code)::text)
36. 0.112 0.361 ↓ 1.5 3 1

Hash Join (cost=1.09..11.79 rows=2 width=442) (actual time=0.220..0.361 rows=3 loops=1)

  • Hash Cond: ((ida1_1.company_cd)::text = (cx1_1.company_cd)::text)
37. 0.086 0.086 ↑ 2.6 19 1

Seq Scan on imm_department_ath ida1_1 (cost=0.00..10.50 rows=50 width=436) (actual time=0.013..0.086 rows=19 loops=1)

38. 0.080 0.163 ↓ 8.5 17 1

Hash (cost=1.06..1.06 rows=2 width=6) (actual time=0.163..0.163 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
39. 0.083 0.083 ↓ 8.5 17 1

Seq Scan on cq_company_extension cx1_1 (cost=0.00..1.06 rows=2 width=6) (actual time=0.012..0.083 rows=17 loops=1)

  • Filter: ((super_company_flag)::text = '0'::text)
  • Rows Removed by Filter: 3
40. 0.096 0.194 ↓ 2.2 22 1

Hash (cost=1.10..1.10 rows=10 width=14) (actual time=0.194..0.194 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
41. 0.098 0.098 ↓ 2.2 22 1

Seq Scan on cq_machines machine_1 (cost=0.00..1.10 rows=10 width=14) (actual time=0.010..0.098 rows=22 loops=1)