explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6mNc

Settings
# exclusive inclusive rows x rows loops node
1. 1.044 2,708.926 ↑ 1.4 144 1

Limit (cost=32,482.00..32,482.50 rows=200 width=28) (actual time=2,707.361..2,708.926 rows=144 loops=1)

2. 1.124 2,707.882 ↑ 1.4 144 1

Sort (cost=32,482.00..32,482.50 rows=200 width=28) (actual time=2,707.353..2,707.882 rows=144 loops=1)

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

HashAggregate (cost=32,472.35..32,474.35 rows=200 width=28) (actual time=2,706.191..2,706.758 rows=144 loops=1)

  • Group Key: (row_number() OVER (?)), generate_series.generate_series
4. 1.206 2,705.559 ↑ 6.9 144 1

Hash Left Join (cost=32,426.75..32,464.85 rows=1,000 width=28) (actual time=2,702.701..2,705.559 rows=144 loops=1)

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

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

6. 0.601 0.601 ↑ 6.9 144 1

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

7. 0.049 2,702.600 ↑ 1.2 10 1

Hash (cost=32,426.59..32,426.59 rows=12 width=44) (actual time=2,702.600..2,702.600 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
8. 0.083 2,702.551 ↑ 1.2 10 1

Subquery Scan on target (cost=32,425.72..32,426.59 rows=12 width=44) (actual time=2,702.433..2,702.551 rows=10 loops=1)

9. 0.000 2,702.468 ↑ 1.2 10 1

HashAggregate (cost=32,425.72..32,426.47 rows=12 width=19) (actual time=2,702.425..2,702.468 rows=10 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.139 0.862 ↑ 1.0 1 1

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

12. 0.078 0.078 ↓ 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.014..0.078 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.146 0.337 ↑ 1.0 1 1

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

15. 0.086 0.086 ↓ 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.023..0.086 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.169 2,701.945 ↓ 7.0 84 1

Hash Left Join (cost=32,303.57..32,358.58 rows=12 width=19) (actual time=2,698.826..2,701.945 rows=84 loops=1)

  • Hash Cond: (od.operation_data_id = am.operation_data_id)
18. 0.900 2,700.764 ↓ 7.0 84 1

Nested Loop (cost=32,291.03..32,345.69 rows=12 width=19) (actual time=2,698.736..2,700.764 rows=84 loops=1)

  • Join Filter: (od.acquisition_time = cq_operation_data_history.acquisition_time)
19. 4.756 2,698.772 ↓ 28.0 28 1

HashAggregate (cost=32,290.47..32,290.48 rows=1 width=32) (actual time=2,698.661..2,698.772 rows=28 loops=1)

  • Group Key: main.machine_id, main.acquisition_time
20. 12.901 2,694.016 ↓ 1,148.0 1,148 1

Nested Loop (cost=1.69..32,290.46 rows=1 width=32) (actual time=28.463..2,694.016 rows=1,148 loops=1)

21. 8.405 2,624.253 ↓ 486.0 486 1

Nested Loop (cost=1.12..32,065.15 rows=1 width=32) (actual time=14.994..2,624.253 rows=486 loops=1)

22. 1,540.816 1,540.816 ↓ 10.6 486 1

Index Scan using cq_operation_data_history_index04 on cq_operation_data_history (cost=0.56..31,669.55 rows=46 width=16) (actual time=14.380..1,540.816 rows=486 loops=1)

  • Index Cond: ((operation_data_id = $1) AND (machine_id = 18))
  • Filter: (COALESCE(number_data, 0::numeric) = 0.0)
  • Rows Removed by Filter: 115782
23. 1,075.032 1,075.032 ↑ 1.0 1 486

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=2.207..2.212 rows=1 loops=486)

  • 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 = ''::text)
24. 56.862 56.862 ↓ 2.0 2 486

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.099..0.117 rows=2 loops=486)

  • 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/04 00:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss'::text)) AND (timezone('Asia/Tokyo'::text, acquisition_time) < to_timestamp('2019/04/05 00:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss'::text)))
  • Rows Removed by Filter: 39
  • Heap Fetches: 19926
25. 1.092 1.092 ↓ 3.0 3 28

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.039 rows=3 loops=28)

  • 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.008 0.012 ↓ 0.0 0 1

Hash (cost=12.53..12.53 rows=1 width=8) (actual time=0.012..0.012 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)