explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rQpw

Settings
# exclusive inclusive rows x rows loops node
1. 0.148 47.984 ↑ 1.4 144 1

Limit (cost=481.16..481.66 rows=200 width=28) (actual time=47.768..47.984 rows=144 loops=1)

2. 0.179 47.836 ↑ 1.4 144 1

Sort (cost=481.16..481.66 rows=200 width=28) (actual time=47.765..47.836 rows=144 loops=1)

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

HashAggregate (cost=471.52..473.52 rows=200 width=28) (actual time=47.575..47.657 rows=144 loops=1)

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

Hash Left Join (cost=426.47..464.02 rows=1,000 width=28) (actual time=46.951..47.416 rows=144 loops=1)

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

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

6. 0.138 0.138 ↑ 6.9 144 1

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

7. 0.028 46.805 ↓ 27.0 27 1

Hash (cost=426.45..426.45 rows=1 width=44) (actual time=46.805..46.805 rows=27 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
8. 0.034 46.777 ↓ 27.0 27 1

Subquery Scan on target (cost=426.37..426.45 rows=1 width=44) (actual time=46.721..46.777 rows=27 loops=1)

9. 0.225 46.743 ↓ 27.0 27 1

HashAggregate (cost=426.37..426.44 rows=1 width=20) (actual time=46.719..46.743 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.037 0.063 ↑ 1.0 1 1

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

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

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

  • Index Cond: ((id = i.operation_data_id) AND (model_id = 128))
  • Heap Fetches: 1
14. 0.030 0.097 ↑ 1.0 1 1

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

15. 0.022 0.022 ↓ 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.013..0.022 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 = 128))
  • Heap Fetches: 1
17. 2.545 46.358 ↓ 258.0 258 1

Nested Loop Left Join (cost=376.30..406.98 rows=1 width=20) (actual time=41.944..46.358 rows=258 loops=1)

  • Join Filter: (od.operation_data_id = am.operation_data_id)
18. 0.507 43.813 ↓ 258.0 258 1

Nested Loop (cost=376.30..393.35 rows=1 width=20) (actual time=41.899..43.813 rows=258 loops=1)

  • Join Filter: (od.acquisition_time = cq_operation_data_history.acquisition_time)
19. 2.274 41.930 ↓ 86.0 86 1

HashAggregate (cost=376.01..376.02 rows=1 width=32) (actual time=41.871..41.930 rows=86 loops=1)

  • Group Key: main.machine_id, main.acquisition_time
20. 9.867 39.656 ↓ 3,526.0 3,526 1

Nested Loop (cost=11.09..376.00 rows=1 width=32) (actual time=0.304..39.656 rows=3,526 loops=1)

21. 4.208 19.211 ↓ 3,526.0 3,526 1

Nested Loop (cost=10.81..368.80 rows=1 width=24) (actual time=0.223..19.211 rows=3,526 loops=1)

22. 0.482 0.641 ↓ 86.0 86 1

Bitmap Heap Scan on cq_operation_data_history cq_operation_data_history_1 (cost=6.28..288.08 rows=1 width=16) (actual time=0.180..0.641 rows=86 loops=1)

  • Recheck Cond: ((operation_data_id = $3) AND (machine_id = 22))
  • Filter: ((COALESCE(string_data, ''::character varying))::text = '34315 x106T 01 CWA01'::text)
  • Rows Removed by Filter: 312
  • Heap Blocks: exact=172
23. 0.159 0.159 ↓ 2.0 398 1

Bitmap Index Scan on cq_operation_data_history_index04 (cost=0.00..6.28 rows=199 width=0) (actual time=0.159..0.159 rows=398 loops=1)

  • Index Cond: ((operation_data_id = $3) AND (machine_id = 22))
24. 13.846 14.362 ↓ 41.0 41 86

Bitmap Heap Scan on cq_operation_data_history main (cost=4.53..80.71 rows=1 width=16) (actual time=0.014..0.167 rows=41 loops=86)

  • Recheck Cond: ((machine_id = 22) AND (acquisition_time = cq_operation_data_history_1.acquisition_time))
  • Filter: ((timezone('Asia/Tokyo'::text, acquisition_time) >= to_timestamp('2019/04/04
  • Heap Blocks: exact=122
25. 0.516 0.516 ↓ 1.7 41 86

Bitmap Index Scan on cq_operation_data_history_index02 (cost=0.00..4.53 rows=24 width=0) (actual time=0.006..0.006 rows=41 loops=86)

  • Index Cond: ((machine_id = 22) AND (acquisition_time = cq_operation_data_history_1.acquisition_time))
26. 10.578 10.578 ↑ 1.0 1 3,526

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

  • Index Cond: ((machine_id = 22) AND (acquisition_time = main.acquisition_time) AND (operation_data_id = $1))
  • Filter: (COALESCE(number_data, 0::numeric) = 37.0)
27. 1.376 1.376 ↓ 3.0 3 86

Index Scan using cq_operation_data_history_index01 on cq_operation_data_history od (cost=0.29..17.31 rows=1 width=28) (actual time=0.008..0.016 rows=3 loops=86)

  • Index Cond: ((machine_id = 22) AND (acquisition_time = main.acquisition_time))
  • Filter: ((operation_data_id = 4507) OR (operation_data_id = 4503) OR (operation_data_id = 4502))
  • Rows Removed by Filter: 38
28. 0.000 0.000 ↓ 0.0 0 258

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

  • 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 = 4507)
Planning time : 1.450 ms
Execution time : 48.389 ms