explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y55s

Settings
# exclusive inclusive rows x rows loops node
1. 1.065 398.504 ↑ 1.4 144 1

Limit (cost=32,499.49..32,499.99 rows=200 width=28) (actual time=396.908..398.504 rows=144 loops=1)

2. 1.124 397.439 ↑ 1.4 144 1

Sort (cost=32,499.49..32,499.99 rows=200 width=28) (actual time=396.900..397.439 rows=144 loops=1)

  • Sort Key: (row_number() OVER (?))
  • Sort Method: quicksort Memory: 32kB
3. 1.220 396.315 ↑ 1.4 144 1

HashAggregate (cost=32,489.85..32,491.85 rows=200 width=28) (actual time=395.738..396.315 rows=144 loops=1)

  • Group Key: (row_number() OVER (?)), generate_series.generate_series
4. 1.203 395.095 ↑ 6.9 144 1

Hash Left Join (cost=32,444.25..32,482.35 rows=1,000 width=28) (actual time=392.194..395.095 rows=144 loops=1)

  • Hash Cond: (((row_number() OVER (?)))::numeric = target.hour_index)
5. 1.156 1.763 ↑ 6.9 144 1

WindowAgg (cost=0.01..22.51 rows=1,000 width=8) (actual time=0.045..1.763 rows=144 loops=1)

6. 0.607 0.607 ↑ 6.9 144 1

Function Scan on generate_series (cost=0.01..10.01 rows=1,000 width=8) (actual time=0.034..0.607 rows=144 loops=1)

7. 0.059 392.129 ↑ 1.1 11 1

Hash (cost=32,444.09..32,444.09 rows=12 width=44) (actual time=392.129..392.129 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
8. 0.087 392.070 ↑ 1.1 11 1

Subquery Scan on target (cost=32,443.22..32,444.09 rows=12 width=44) (actual time=391.941..392.070 rows=11 loops=1)

9. 0.000 391.983 ↑ 1.1 11 1

HashAggregate (cost=32,443.22..32,443.97 rows=12 width=19) (actual time=391.932..391.983 rows=11 loops=1)

  • Group Key: ((to_number(to_char(timezone('Asia/Tokyo'::text, od.acquisition_time), 'hh24'::text), '99'::text) * 6::numeric) + trunc((to_number(to_char(timezone('Asia/Tokyo'::text, od.acquisition_time), 'mi'::text), '99'::text) / 10::numeric), 0))
10.          

Initplan (forHashAggregate)

11. 0.133 0.343 ↑ 1.0 1 1

Nested Loop (cost=0.28..33.42 rows=1 width=8) (actual time=0.286..0.343 rows=1 loops=1)

12. 0.090 0.090 ↓ 5.0 15 1

Seq Scan on cqc_operation_data_of_product_information i (cost=0.00..16.50 rows=3 width=8) (actual time=0.012..0.090 rows=15 loops=1)

  • Filter: ((type)::text = 'PRODUCT_NO'::text)
  • Rows Removed by Filter: 15
13. 0.120 0.120 ↓ 0.0 0 15

Index Only Scan using cq_operation_data_index02 on cq_operation_data d (cost=0.28..5.63 rows=1 width=8) (actual time=0.007..0.008 rows=0 loops=15)

  • Index Cond: ((id = i.operation_data_id) AND (model_id = 100))
  • Heap Fetches: 1
14. 0.154 0.338 ↑ 1.0 1 1

Nested Loop (cost=0.28..33.42 rows=1 width=8) (actual time=0.249..0.338 rows=1 loops=1)

15. 0.079 0.079 ↓ 5.0 15 1

Seq Scan on cqc_operation_data_of_product_information i_1 (cost=0.00..16.50 rows=3 width=8) (actual time=0.009..0.079 rows=15 loops=1)

  • Filter: ((type)::text = 'PRODUCT_NAME'::text)
  • Rows Removed by Filter: 15
16. 0.105 0.105 ↓ 0.0 0 15

Index Only Scan using cq_operation_data_index02 on cq_operation_data d_1 (cost=0.28..5.63 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=15)

  • Index Cond: ((id = i_1.operation_data_id) AND (model_id = 100))
  • Heap Fetches: 1
17. 1.430 391.329 ↓ 9.0 108 1

Hash Left Join (cost=32,321.06..32,376.07 rows=12 width=19) (actual time=387.338..391.329 rows=108 loops=1)

  • Hash Cond: (od.operation_data_id = am.operation_data_id)
18. 1.165 389.888 ↓ 9.0 108 1

Nested Loop (cost=32,308.52..32,363.18 rows=12 width=19) (actual time=387.272..389.888 rows=108 loops=1)

  • Join Filter: (od.acquisition_time = cq_operation_data_history.acquisition_time)
19. 6.336 387.355 ↓ 36.0 36 1

HashAggregate (cost=32,307.96..32,307.97 rows=1 width=32) (actual time=387.213..387.355 rows=36 loops=1)

  • Group Key: main.machine_id, main.acquisition_time
20. 30.905 381.019 ↓ 1,476.0 1,476 1

Nested Loop (cost=1.69..32,307.95 rows=1 width=32) (actual time=9.517..381.019 rows=1,476 loops=1)

21. 37.209 184.102 ↓ 2,156.0 2,156 1

Nested Loop (cost=1.12..32,082.64 rows=1 width=32) (actual time=1.785..184.102 rows=2,156 loops=1)

22. 116.709 116.709 ↓ 46.9 2,156 1

Index Scan using cq_operation_data_history_index04 on cq_operation_data_history (cost=0.56..31,687.04 rows=46 width=16) (actual time=1.395..116.709 rows=2,156 loops=1)

  • Index Cond: ((operation_data_id = $1) AND (machine_id = 18))
  • Filter: (COALESCE(number_data, 0::numeric) = 222.0)
  • Rows Removed by Filter: 114192
23. 30.184 30.184 ↑ 1.0 1 2,156

Index Scan using cq_operation_data_history_index02 on cq_operation_data_history cq_operation_data_history_1 (cost=0.56..8.59 rows=1 width=16) (actual time=0.010..0.014 rows=1 loops=2,156)

  • Index Cond: ((machine_id = 18) AND (acquisition_time = cq_operation_data_history.acquisition_time) AND (operation_data_id = $3))
  • Filter: ((COALESCE(string_data, ''::character varying))::text = '65632/682 TDK '::text)
24. 166.012 166.012 ↑ 1.0 1 2,156

Index Only Scan using cq_operation_data_history_index01 on cq_operation_data_history main (cost=0.56..225.30 rows=1 width=16) (actual time=0.072..0.077 rows=1 loops=2,156)

  • Index Cond: ((machine_id = 18) AND (acquisition_time = cq_operation_data_history.acquisition_time))
  • Filter: ((timezone('Asia/Tokyo'::text, acquisition_time) >= to_timestamp('2019/04/01 00:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss'::text)) AND (timezone('Asia/Tokyo'::text, acquisition_time) < to_timestamp('2019/04/02 00:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss'::text)))
  • Rows Removed by Filter: 40
  • Heap Fetches: 88396
25. 1.368 1.368 ↓ 3.0 3 36

Index Scan using cq_operation_data_history_index01 on cq_operation_data_history od (cost=0.56..55.20 rows=1 width=27) (actual time=0.015..0.038 rows=3 loops=36)

  • Index Cond: ((machine_id = 18) AND (acquisition_time = main.acquisition_time))
  • Filter: ((operation_data_id = 3219) OR (operation_data_id = 3215) OR (operation_data_id = 3214))
  • Rows Removed by Filter: 38
26. 0.007 0.011 ↓ 0.0 0 1

Hash (cost=12.53..12.53 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
27. 0.004 0.004 ↓ 0.0 0 1

Result (cost=0.00..12.53 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • One-Time Filter: (false AND false)
28. 0.000 0.000 ↓ 0.0 0

Seq Scan on cq_aggregate_methods am (cost=0.00..12.53 rows=1 width=8) (never executed)

  • Filter: (operation_data_id = 3219)