explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xjkq

Settings
# exclusive inclusive rows x rows loops node
1. 0.127 45.032 ↑ 1.4 144 1

Limit (cost=314.66..315.16 rows=200 width=28) (actual time=44.842..45.032 rows=144 loops=1)

2. 0.163 44.905 ↑ 1.4 144 1

Sort (cost=314.66..315.16 rows=200 width=28) (actual time=44.839..44.905 rows=144 loops=1)

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

HashAggregate (cost=305.02..307.02 rows=200 width=28) (actual time=44.667..44.742 rows=144 loops=1)

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

Hash Left Join (cost=259.87..297.52 rows=1,000 width=28) (actual time=44.077..44.485 rows=144 loops=1)

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

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

6. 0.108 0.108 ↑ 6.9 144 1

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

7. 0.021 44.017 ↓ 9.0 27 1

Hash (cost=259.82..259.82 rows=3 width=44) (actual time=44.017..44.017 rows=27 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
8. 0.027 43.996 ↓ 9.0 27 1

Subquery Scan on target (cost=259.60..259.82 rows=3 width=44) (actual time=43.953..43.996 rows=27 loops=1)

9. 0.178 43.969 ↓ 9.0 27 1

HashAggregate (cost=259.60..259.79 rows=3 width=20) (actual time=43.951..43.969 rows=27 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.064 0.107 ↑ 1.0 1 1

Nested Loop (cost=0.28..9.68 rows=1 width=8) (actual time=0.104..0.107 rows=1 loops=1)

12. 0.013 0.013 ↓ 15.0 15 1

Seq Scan on cqc_operation_data_of_product_information i (cost=0.00..1.38 rows=1 width=8) (actual time=0.003..0.013 rows=15 loops=1)

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

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

  • Index Cond: ((id = i.operation_data_id) AND (model_id = 129))
  • Heap Fetches: 1
14. 0.029 0.093 ↑ 1.0 1 1

Nested Loop (cost=0.28..9.68 rows=1 width=8) (actual time=0.090..0.093 rows=1 loops=1)

15. 0.019 0.019 ↓ 15.0 15 1

Seq Scan on cqc_operation_data_of_product_information i_1 (cost=0.00..1.38 rows=1 width=8) (actual time=0.010..0.019 rows=15 loops=1)

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

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

  • Index Cond: ((id = i_1.operation_data_id) AND (model_id = 129))
  • Heap Fetches: 1
17. 1.115 43.591 ↓ 90.0 270 1

Nested Loop Left Join (cost=217.15..240.16 rows=3 width=20) (actual time=40.633..43.591 rows=270 loops=1)

  • Join Filter: (od.operation_data_id = am.operation_data_id)
18. 0.410 42.206 ↓ 90.0 270 1

Nested Loop (cost=217.15..226.44 rows=3 width=20) (actual time=40.591..42.206 rows=270 loops=1)

  • Join Filter: (od.acquisition_time = cq_operation_data_history.acquisition_time)
19. 2.346 40.626 ↓ 90.0 90 1

HashAggregate (cost=216.86..216.87 rows=1 width=32) (actual time=40.567..40.626 rows=90 loops=1)

  • Group Key: main.machine_id, main.acquisition_time
20. 13.004 38.280 ↓ 3,690.0 3,690 1

Nested Loop (cost=11.02..216.86 rows=1 width=32) (actual time=0.602..38.280 rows=3,690 loops=1)

21. 3.764 17.896 ↓ 3,690.0 3,690 1

Nested Loop (cost=10.73..210.57 rows=1 width=24) (actual time=0.481..17.896 rows=3,690 loops=1)

22. 0.329 0.461 ↓ 93.0 93 1

Bitmap Heap Scan on cq_operation_data_history cq_operation_data_history_1 (cost=10.45..139.49 rows=1 width=16) (actual time=0.231..0.461 rows=93 loops=1)

  • Recheck Cond: (operation_data_id = $3)
  • Filter: ((machine_id = 25) AND ((COALESCE(string_data, ''::character varying))::text = '32429 AA030 01'::text))
  • Rows Removed by Filter: 195
  • Heap Blocks: exact=124
23. 0.132 0.132 ↑ 1.0 288 1

Bitmap Index Scan on cq_operation_data_history_index03 (cost=0.00..10.45 rows=288 width=0) (actual time=0.132..0.132 rows=288 loops=1)

  • Index Cond: (operation_data_id = $3)
24. 13.671 13.671 ↓ 40.0 40 93

Index Only Scan using cq_operation_data_history_index02 on cq_operation_data_history main (cost=0.29..71.07 rows=1 width=16) (actual time=0.008..0.147 rows=40 loops=93)

  • Index Cond: ((machine_id = 25) AND (acquisition_time = cq_operation_data_history_1.acquisition_time))
  • Filter: ((timezone('Asia/Tokyo'::text, acquisition_time) >= to_timestamp('2019/04/10 00:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss'::text)) AND (timezone('Asia/Tokyo'::text, acquisition_time) < to_timestamp('2019/04/11 00:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss'::text)))
  • Rows Removed by Filter: 1
  • Heap Fetches: 3813
25. 7.380 7.380 ↑ 1.0 1 3,690

Index Scan using cq_operation_data_history_index02 on cq_operation_data_history (cost=0.29..6.28 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=3,690)

  • Index Cond: ((machine_id = 25) AND (acquisition_time = main.acquisition_time) AND (operation_data_id = $1))
  • Filter: (COALESCE(number_data, 0::numeric) = 35.0)
26. 1.170 1.170 ↑ 1.0 3 90

Index Scan using cq_operation_data_history_index01 on cq_operation_data_history od (cost=0.29..9.53 rows=3 width=28) (actual time=0.005..0.013 rows=3 loops=90)

  • Index Cond: ((machine_id = 25) AND (acquisition_time = main.acquisition_time))
  • Filter: ((operation_data_id = 4599) OR (operation_data_id = 4595) OR (operation_data_id = 4594))
  • Rows Removed by Filter: 38
27. 0.269 0.270 ↓ 0.0 0 270

Materialize (cost=0.00..13.61 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=270)

28. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..13.60 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)

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

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

  • Filter: (operation_data_id = 4599)