explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M75n

Settings
# exclusive inclusive rows x rows loops node
1. 201.958 2,828.200 ↑ 1.0 10,571 1

WindowAgg (cost=3,636.19..4,197.99 rows=10,701 width=1,264) (actual time=2,532.679..2,828.200 rows=10,571 loops=1)

2. 273.805 2,626.242 ↑ 1.0 10,571 1

Sort (cost=3,636.19..3,662.94 rows=10,701 width=1,264) (actual time=2,532.630..2,626.242 rows=10,571 loops=1)

  • Sort Key: cqm.machine_code, ((((json_agg(his.event_id ORDER BY his.acquisition_time DESC, (unnest('{0,1,2,3}'::integer[])) DESC) ->> 0)))::bigint)
  • Sort Method: quicksort Memory: 3265kB
3. 190.828 2,352.437 ↑ 1.0 10,571 1

Hash Left Join (cost=2,027.38..2,920.00 rows=10,701 width=1,264) (actual time=1,498.810..2,352.437 rows=10,571 loops=1)

  • Hash Cond: (((cqm.company_code)::text = (imd.company_cd)::text) AND ((cqm.company_code)::text = (imd.department_set_cd)::text) AND ((cqm.company_code)::text = (imd.department_cd)::text))
4. 189.029 2,156.535 ↑ 1.0 10,571 1

Hash Left Join (cost=2,008.67..2,740.76 rows=10,701 width=1,242) (actual time=1,493.639..2,156.535 rows=10,571 loops=1)

  • Hash Cond: (cqmd.id = cqmo.model_id)
5. 183.974 1,967.317 ↑ 1.0 10,571 1

Hash Left Join (cost=2,007.53..2,688.80 rows=10,701 width=1,032) (actual time=1,493.362..1,967.317 rows=10,571 loops=1)

  • Hash Cond: (cqm.id = cmll.machine_id)
6. 193.631 1,757.934 ↑ 1.0 10,571 1

Hash Right Join (cost=1,955.60..2,592.00 rows=10,701 width=1,016) (actual time=1,467.872..1,757.934 rows=10,571 loops=1)

  • Hash Cond: (cqml.machine_id = cqm.id)
7. 96.530 96.530 ↓ 1.0 10,549 1

Seq Scan on cq_machines_location cqml (cost=0.00..438.66 rows=10,546 width=32) (actual time=0.046..96.530 rows=10,549 loops=1)

  • Filter: ((language_code)::text = 'ja'::text)
  • Rows Removed by Filter: 10552
8. 101.562 1,467.773 ↑ 1.0 10,571 1

Hash (cost=1,821.83..1,821.83 rows=10,701 width=992) (actual time=1,467.773..1,467.773 rows=10,571 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1125kB
9. 95.710 1,366.211 ↑ 1.0 10,571 1

Hash Right Join (cost=1,804.51..1,821.83 rows=10,701 width=992) (actual time=1,258.691..1,366.211 rows=10,571 loops=1)

  • Hash Cond: (sub1.machine_id = cqm.id)
10. 7.950 254.677 ↓ 1.0 452 1

Subquery Scan on sub1 (cost=478.75..487.69 rows=447 width=80) (actual time=242.814..254.677 rows=452 loops=1)

11. 128.445 246.727 ↓ 1.0 452 1

HashAggregate (cost=478.75..483.22 rows=447 width=23) (actual time=242.777..246.727 rows=452 loops=1)

  • Group Key: cq_maintenance_check_current_status.machine_id
12. 118.282 118.282 ↓ 1.0 13,398 1

Seq Scan on cq_maintenance_check_current_status (cost=0.00..277.90 rows=13,390 width=23) (actual time=0.023..118.282 rows=13,398 loops=1)

13. 100.126 1,015.824 ↑ 1.0 10,571 1

Hash (cost=1,192.00..1,192.00 rows=10,701 width=920) (actual time=1,015.824..1,015.824 rows=10,571 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1121kB
14. 185.515 915.698 ↑ 1.0 10,571 1

Hash Left Join (cost=1,005.23..1,192.00 rows=10,701 width=920) (actual time=446.925..915.698 rows=10,571 loops=1)

  • Hash Cond: (cqm.model_id = cqmd.id)
15. 182.172 730.010 ↑ 1.0 10,571 1

Hash Left Join (cost=1,004.12..1,061.58 rows=10,701 width=404) (actual time=446.688..730.010 rows=10,571 loops=1)

  • Hash Cond: (cqm.id = his.machine_id)
16. 97.292 536.399 ↑ 1.0 10,571 1

Hash Right Join (cost=927.05..944.37 rows=10,701 width=136) (actual time=435.214..536.399 rows=10,571 loops=1)

  • Hash Cond: (mccssub.machine_id = cqm.id)
17. 128.160 246.155 ↓ 1.0 452 1

HashAggregate (cost=445.27..449.75 rows=447 width=15) (actual time=242.224..246.155 rows=452 loops=1)

  • Group Key: mccssub.machine_id
18. 117.995 117.995 ↓ 1.0 13,398 1

Seq Scan on cq_maintenance_check_current_status mccssub (cost=0.00..277.90 rows=13,390 width=15) (actual time=0.015..117.995 rows=13,398 loops=1)

19. 97.512 192.952 ↑ 1.0 10,571 1

Hash (cost=348.01..348.01 rows=10,701 width=120) (actual time=192.952..192.952 rows=10,571 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 839kB
20. 95.440 95.440 ↑ 1.0 10,571 1

Seq Scan on cq_machines cqm (cost=0.00..348.01 rows=10,701 width=120) (actual time=0.015..95.440 rows=10,571 loops=1)

21. 0.057 11.439 ↓ 4.0 4 1

Hash (cost=77.06..77.06 rows=1 width=276) (actual time=11.439..11.439 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
22. 0.155 11.382 ↓ 4.0 4 1

Nested Loop Left Join (cost=67.90..77.06 rows=1 width=276) (actual time=10.792..11.382 rows=4 loops=1)

23. 0.173 11.151 ↓ 4.0 4 1

Nested Loop Left Join (cost=67.77..76.90 rows=1 width=48) (actual time=10.752..11.151 rows=4 loops=1)

24. 0.266 10.894 ↓ 4.0 4 1

GroupAggregate (cost=67.48..68.57 rows=1 width=28) (actual time=10.692..10.894 rows=4 loops=1)

  • Group Key: his.machine_id
25. 0.126 10.628 ↑ 18.0 6 1

Sort (cost=67.48..67.75 rows=108 width=28) (actual time=10.578..10.628 rows=6 loops=1)

  • Sort Key: his.machine_id
  • Sort Method: quicksort Memory: 25kB
26. 0.153 10.502 ↑ 18.0 6 1

Hash Join (cost=32.02..63.83 rows=108 width=28) (actual time=3.648..10.502 rows=6 loops=1)

  • Hash Cond: (e.event_level_id = el.id)
27. 3.925 9.962 ↑ 18.0 6 1

Merge Join (cost=28.95..59.28 rows=108 width=32) (actual time=3.212..9.962 rows=6 loops=1)

  • Merge Cond: (e.id = his.event_id)
28. 5.761 5.761 ↑ 23.2 441 1

Index Scan using cq_events_index01 on cq_events e (cost=0.29..451.93 rows=10,242 width=16) (actual time=0.025..5.761 rows=441 loops=1)

29. 0.152 0.276 ↑ 18.0 6 1

Sort (cost=28.58..28.85 rows=108 width=24) (actual time=0.226..0.276 rows=6 loops=1)

  • Sort Key: his.event_id
  • Sort Method: quicksort Memory: 25kB
30. 0.124 0.124 ↑ 18.0 6 1

Index Scan using cq_occurrence_history_index05 on cq_occurrence_history his (cost=0.44..24.93 rows=108 width=24) (actual time=0.065..0.124 rows=6 loops=1)

  • Index Cond: ((acquisition_time >= ((to_date(to_char(now(), 'yyyy/MM/dd'::text), 'yyyy/MM/dd'::text) - 7) + 1)) AND (acquisition_time <= now()))
31. 0.051 0.387 ↑ 1.0 4 1

Hash (cost=3.02..3.02 rows=4 width=12) (actual time=0.387..0.387 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
32. 0.164 0.336 ↑ 1.0 4 1

Hash Join (cost=1.09..3.02 rows=4 width=12) (actual time=0.230..0.336 rows=4 loops=1)

  • Hash Cond: ((unnest('{SYSTEMALERT,INFORMATION,CAUTION,CRITICAL}'::text[])) = (el.policy_attribute)::text)
33. 0.071 0.071 ↑ 25.0 4 1

Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.036..0.071 rows=4 loops=1)

34. 0.054 0.101 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=126) (actual time=0.101..0.101 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
35. 0.047 0.047 ↑ 1.0 4 1

Seq Scan on cq_event_levels el (cost=0.00..1.04 rows=4 width=126) (actual time=0.013..0.047 rows=4 loops=1)

36. 0.084 0.084 ↑ 1.0 1 4

Index Scan using cq_events_index01 on cq_events cqe (cost=0.29..8.31 rows=1 width=16) (actual time=0.012..0.021 rows=1 loops=4)

  • Index Cond: ((((json_agg(his.event_id ORDER BY his.acquisition_time DESC, (unnest('{0,1,2,3}'::integer[])) DESC) ->> 0)))::bigint = id)
37. 0.076 0.076 ↑ 1.0 1 4

Index Scan using cq_event_levels_index02 on cq_event_levels cqel (cost=0.13..0.15 rows=1 width=244) (actual time=0.010..0.019 rows=1 loops=4)

  • Index Cond: (cqe.event_level_id = id)
38. 0.090 0.173 ↓ 1.6 8 1

Hash (cost=1.05..1.05 rows=5 width=524) (actual time=0.173..0.173 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
39. 0.083 0.083 ↓ 1.6 8 1

Seq Scan on cq_models cqmd (cost=0.00..1.05 rows=5 width=524) (actual time=0.013..0.083 rows=8 loops=1)

40. 4.321 25.409 ↓ 1.0 475 1

Hash (cost=46.01..46.01 rows=474 width=24) (actual time=25.409..25.409 rows=475 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
41. 8.324 21.088 ↓ 1.0 475 1

Hash Join (cost=14.67..46.01 rows=474 width=24) (actual time=8.547..21.088 rows=475 loops=1)

  • Hash Cond: (cmlli.machine_id = cmll.machine_id)
42. 4.337 4.337 ↓ 1.0 475 1

Seq Scan on cq_machines_latest_location_international cmlli (cost=0.00..24.83 rows=474 width=8) (actual time=0.032..4.337 rows=475 loops=1)

  • Filter: ((language_code)::text = 'ja'::text)
  • Rows Removed by Filter: 473
43. 4.264 8.427 ↓ 1.0 475 1

Hash (cost=8.74..8.74 rows=474 width=24) (actual time=8.427..8.427 rows=475 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
44. 4.163 4.163 ↓ 1.0 475 1

Seq Scan on cq_machines_latest_location cmll (cost=0.00..8.74 rows=474 width=24) (actual time=0.014..4.163 rows=475 loops=1)

45. 0.088 0.189 ↓ 8.0 8 1

Hash (cost=1.12..1.12 rows=1 width=226) (actual time=0.189..0.189 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
46. 0.101 0.101 ↓ 8.0 8 1

Seq Scan on cq_models_international cqmo (cost=0.00..1.12 rows=1 width=226) (actual time=0.029..0.101 rows=8 loops=1)

  • Filter: ((language_code)::text = 'ja'::text)
  • Rows Removed by Filter: 9
47. 2.470 5.074 ↓ 1.1 247 1

Hash (cost=14.60..14.60 rows=235 width=46) (actual time=5.074..5.074 rows=247 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
48. 2.604 2.604 ↓ 1.1 247 1

Seq Scan on imm_department imd (cost=0.00..14.60 rows=235 width=46) (actual time=0.044..2.604 rows=247 loops=1)

  • Filter: (((locale_id)::text = 'ja'::text) AND (start_date <= ('now'::cstring)::date) AND (end_date > ('now'::cstring)::date))
  • Rows Removed by Filter: 15