explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4c4G

Settings
# exclusive inclusive rows x rows loops node
1. 1.044 818,347.773 ↑ 1.4 144 1

Limit (cost=160.29..160.79 rows=200 width=28) (actual time=818,346.192..818,347.773 rows=144 loops=1)

2. 1.279 818,346.729 ↑ 1.4 144 1

Sort (cost=160.29..160.79 rows=200 width=28) (actual time=818,346.184..818,346.729 rows=144 loops=1)

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

HashAggregate (cost=150.64..152.64 rows=200 width=28) (actual time=818,344.894..818,345.450 rows=144 loops=1)

  • Group Key: (row_number() OVER (?)), generate_series.generate_series
4. 1.242 818,344.221 ↑ 6.9 144 1

Hash Left Join (cost=105.59..143.14 rows=1,000 width=28) (actual time=818,341.345..818,344.221 rows=144 loops=1)

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

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

6. 0.671 0.671 ↑ 6.9 144 1

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

7. 0.131 818,341.096 ↓ 27.0 27 1

Hash (cost=105.57..105.57 rows=1 width=44) (actual time=818,341.096..818,341.096 rows=27 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
8. 0.221 818,340.965 ↓ 27.0 27 1

Subquery Scan on target (cost=105.50..105.57 rows=1 width=44) (actual time=818,340.642..818,340.965 rows=27 loops=1)

9. 0.000 818,340.744 ↓ 27.0 27 1

HashAggregate (cost=105.50..105.56 rows=1 width=19) (actual time=818,340.631..818,340.744 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.392 1.832 ↑ 1.0 1 1

Nested Loop (cost=0.15..29.26 rows=1 width=8) (actual time=1.046..1.832 rows=1 loops=1)

12. 0.888 0.888 ↓ 46.0 46 1

Seq Scan on cq_operation_data d (cost=0.00..21.08 rows=1 width=8) (actual time=0.553..0.888 rows=46 loops=1)

  • Filter: (model_id = 128)
  • Rows Removed by Filter: 688
13. 0.552 0.552 ↓ 0.0 0 46

Index Only Scan using cqc_operation_data_of_product_information_index02 on cqc_operation_data_of_product_information i (cost=0.15..8.17 rows=1 width=8) (actual time=0.012..0.012 rows=0 loops=46)

  • Index Cond: ((operation_data_id = d.id) AND (type = 'PRODUCT_NO'::text))
  • Heap Fetches: 1
14. 0.378 0.963 ↑ 1.0 1 1

Nested Loop (cost=0.15..29.26 rows=1 width=8) (actual time=0.341..0.963 rows=1 loops=1)

15. 0.309 0.309 ↓ 46.0 46 1

Seq Scan on cq_operation_data d_1 (cost=0.00..21.08 rows=1 width=8) (actual time=0.103..0.309 rows=46 loops=1)

  • Filter: (model_id = 128)
  • Rows Removed by Filter: 688
16. 0.276 0.276 ↓ 0.0 0 46

Index Only Scan using cqc_operation_data_of_product_information_index02 on cqc_operation_data_of_product_information i_1 (cost=0.15..8.17 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=46)

  • Index Cond: ((operation_data_id = d_1.id) AND (type = 'PRODUCT_NAME'::text))
  • Heap Fetches: 1
17. 8.348 818,338.306 ↓ 258.0 258 1

Nested Loop Left Join (cost=2.25..46.96 rows=1 width=19) (actual time=2,352.507..818,338.306 rows=258 loops=1)

  • Join Filter: (od.operation_data_id = am.operation_data_id)
18. 53.344 818,328.926 ↓ 258.0 258 1

Merge Semi Join (cost=2.25..34.40 rows=1 width=19) (actual time=2,352.044..818,328.926 rows=258 loops=1)

  • Merge Cond: (od.acquisition_time = cq_operation_data_history.acquisition_time)
19. 1,418.842 1,418.842 ↓ 8,104.0 8,104 1

Index Scan using cq_operation_data_history_index02 on cq_operation_data_history od (cost=0.56..8.59 rows=1 width=27) (actual time=15.643..1,418.842 rows=8,104 loops=1)

  • Index Cond: (machine_id = 22)
  • Filter: ((operation_data_id = 4507) OR (operation_data_id = 4503) OR (operation_data_id = 4502))
  • Rows Removed by Filter: 102638
20. 33.397 816,856.740 ↓ 3,526.0 3,526 1

Materialize (cost=1.69..25.79 rows=1 width=32) (actual time=928.657..816,856.740 rows=3,526 loops=1)

21. 4,026.140 816,823.343 ↓ 3,526.0 3,526 1

Nested Loop (cost=1.69..25.79 rows=1 width=32) (actual time=928.634..816,823.343 rows=3,526 loops=1)

  • Join Filter: (cq_operation_data_history.acquisition_time = cq_operation_data_history_1.acquisition_time)
  • Rows Removed by Join Filter: 983754
22. 18,764.724 675,790.947 ↓ 3,526.0 3,526 1

Nested Loop (cost=1.12..17.19 rows=1 width=24) (actual time=898.036..675,790.947 rows=3,526 loops=1)

  • Join Filter: (main.acquisition_time = cq_operation_data_history.acquisition_time)
  • Rows Removed by Join Filter: 4554034
23. 1,242.170 1,242.170 ↓ 16,277.0 16,277 1

Index Only Scan using cq_operation_data_history_index01 on cq_operation_data_history main (cost=0.56..8.60 rows=1 width=16) (actual time=865.964..1,242.170 rows=16,277 loops=1)

  • Index Cond: (machine_id = 22)
  • Filter: ((timezone('Asia/Tokyo'::text, acquisition_time) >= to_timestamp('2019/04/04
  • Rows Removed by Filter: 129765
  • Heap Fetches: 146042
24. 655,784.053 655,784.053 ↓ 280.0 280 16,277

Index Scan using cq_operation_data_history_index01 on cq_operation_data_history (cost=0.56..8.59 rows=1 width=16) (actual time=10.099..40.289 rows=280 loops=16,277)

  • Index Cond: (machine_id = 22)
  • Filter: ((operation_data_id = $1) AND (COALESCE(number_data, 0::numeric) = 37.0))
  • Rows Removed by Filter: 145762
25. 137,006.256 137,006.256 ↓ 280.0 280 3,526

Index Scan using cq_operation_data_history_index01 on cq_operation_data_history cq_operation_data_history_1 (cost=0.56..8.59 rows=1 width=16) (actual time=9.656..38.856 rows=280 loops=3,526)

  • Index Cond: (machine_id = 22)
  • Filter: ((operation_data_id = $3) AND ((COALESCE(string_data, ''::character varying))::text = '34315 x106T 01 CWA01'::text))
  • Rows Removed by Filter: 145762
26. 1.032 1.032 ↓ 0.0 0 258

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

  • One-Time Filter: (false AND false)
27. 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 = 4507)