explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qsSZ

Settings
# exclusive inclusive rows x rows loops node
1. 1.136 11,748.165 ↑ 1.4 144 1

Limit (cost=32,488.99..32,489.49 rows=200 width=28) (actual time=11,746.473..11,748.165 rows=144 loops=1)

2. 1.302 11,747.029 ↑ 1.4 144 1

Sort (cost=32,488.99..32,489.49 rows=200 width=28) (actual time=11,746.462..11,747.029 rows=144 loops=1)

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

HashAggregate (cost=32,479.35..32,481.35 rows=200 width=28) (actual time=11,745.080..11,745.727 rows=144 loops=1)

  • Group Key: (row_number() OVER (?)), generate_series.generate_series
4. 1.261 11,744.339 ↑ 6.9 144 1

Hash Left Join (cost=32,433.75..32,471.85 rows=1,000 width=28) (actual time=11,741.342..11,744.339 rows=144 loops=1)

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

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

6. 0.662 0.662 ↑ 6.9 144 1

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

7. 0.059 11,741.189 ↑ 1.1 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
8. 0.098 11,741.130 ↑ 1.1 11 1

Subquery Scan on target (cost=32,432.72..32,433.59 rows=12 width=44) (actual time=11,740.994..11,741.130 rows=11 loops=1)

9. 0.000 11,741.032 ↑ 1.1 11 1

HashAggregate (cost=32,432.72..32,433.47 rows=12 width=19) (actual time=11,740.984..11,741.032 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.157 0.978 ↑ 1.0 1 1

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

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

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

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

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

15. 0.088 0.088 ↓ 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.088 rows=15 loops=1)

  • Filter: ((type)::text = 'PRODUCT_NAME'::text)
  • Rows Removed by Filter: 15
16. 0.135 0.135 ↓ 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.008..0.009 rows=0 loops=15)

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

Hash Left Join (cost=32,310.56..32,365.58 rows=12 width=19) (actual time=11,736.225..11,740.350 rows=108 loops=1)

  • Hash Cond: (od.operation_data_id = am.operation_data_id)
18. 1.197 11,738.806 ↓ 9.0 108 1

Nested Loop (cost=32,298.03..32,352.68 rows=12 width=19) (actual time=11,736.118..11,738.806 rows=108 loops=1)

  • Join Filter: (od.acquisition_time = cq_operation_data_history.acquisition_time)
19. 7.959 11,736.205 ↓ 36.0 36 1

HashAggregate (cost=32,297.46..32,297.47 rows=1 width=32) (actual time=11,736.046..11,736.205 rows=36 loops=1)

  • Group Key: main.machine_id, main.acquisition_time
20. 34.442 11,728.246 ↓ 1,476.0 1,476 1

Nested Loop (cost=1.69..32,297.46 rows=1 width=32) (actual time=58.800..11,728.246 rows=1,476 loops=1)

21. 38.825 11,476.048 ↓ 2,156.0 2,156 1

Nested Loop (cost=1.12..32,072.14 rows=1 width=32) (actual time=23.326..11,476.048 rows=2,156 loops=1)

22. 1,670.543 1,670.543 ↓ 46.9 2,156 1

Index Scan using cq_operation_data_history_index04 on cq_operation_data_history (cost=0.56..31,676.54 rows=46 width=16) (actual time=22.651..1,670.543 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: 114132
23. 9,766.680 9,766.680 ↑ 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=4.526..4.530 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. 217.756 217.756 ↑ 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.094..0.101 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
  • Rows Removed by Filter: 40
  • Heap Fetches: 88396
25. 1.404 1.404 ↓ 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.039 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.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.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)