explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hQaY

Settings
# exclusive inclusive rows x rows loops node
1. 1.063 1,808.958 ↑ 1.4 144 1

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

2. 1.127 1,807.895 ↑ 1.4 144 1

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

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

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

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

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

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

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

6. 0.584 0.584 ↑ 6.9 144 1

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

7. 0.053 1,802.593 ↑ 1.1 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
8. 0.101 1,802.540 ↑ 1.1 11 1

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

9. 0.000 1,802.439 ↑ 1.1 11 1

HashAggregate (cost=32,443.22..32,443.97 rows=12 width=19) (actual time=1,802.393..1,802.439 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.144 0.864 ↑ 1.0 1 1

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

12. 0.075 0.075 ↓ 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.011..0.075 rows=15 loops=1)

  • Filter: ((type)::text = 'PRODUCT_NO'::text)
  • Rows Removed by Filter: 15
13. 0.645 0.645 ↓ 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.043..0.043 rows=0 loops=15)

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

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

15. 0.087 0.087 ↓ 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.087 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.006..0.007 rows=0 loops=15)

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

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

  • Hash Cond: (od.operation_data_id = am.operation_data_id)
18. 1.155 1,799.965 ↓ 9.0 108 1

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

  • Join Filter: (od.acquisition_time = cq_operation_data_history.acquisition_time)
19. 6.246 1,797.478 ↓ 36.0 36 1

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

  • Group Key: main.machine_id, main.acquisition_time
20. 30.579 1,791.232 ↓ 1,476.0 1,476 1

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

21. 34.091 1,570.925 ↓ 2,156.0 2,156 1

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

22. 1,465.686 1,465.686 ↓ 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=22.925..1,465.686 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. 71.148 71.148 ↑ 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.029..0.033 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. 189.728 189.728 ↑ 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.083..0.088 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.332 1.332 ↓ 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.014..0.037 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.006 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.005 0.005 ↓ 0.0 0 1

Result (cost=0.00..12.53 rows=1 width=8) (actual time=0.005..0.005 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)
Planning time : 3.194 ms
Execution time : 1,809.737 ms