explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UMj4

Settings
# exclusive inclusive rows x rows loops node
1. 1.038 43,990.706 ↑ 1.4 144 1

Limit (cost=160.29..160.79 rows=200 width=28) (actual time=43,989.142..43,990.706 rows=144 loops=1)

2. 1.249 43,989.668 ↑ 1.4 144 1

Sort (cost=160.29..160.79 rows=200 width=28) (actual time=43,989.133..43,989.668 rows=144 loops=1)

  • Sort Key: (row_number() OVER (?))
  • Sort Method: quicksort Memory: 32kB
3. 1.236 43,988.419 ↑ 1.4 144 1

HashAggregate (cost=150.64..152.64 rows=200 width=28) (actual time=43,987.858..43,988.419 rows=144 loops=1)

  • Group Key: (row_number() OVER (?)), generate_series.generate_series
4. 1.229 43,987.183 ↑ 6.9 144 1

Hash Left Join (cost=105.59..143.14 rows=1,000 width=28) (actual time=43,984.352..43,987.183 rows=144 loops=1)

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

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

6. 0.650 0.650 ↑ 6.9 144 1

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

7. 0.133 43,984.104 ↓ 27.0 27 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
8. 0.220 43,983.971 ↓ 27.0 27 1

Subquery Scan on target (cost=105.50..105.57 rows=1 width=44) (actual time=43,983.644..43,983.971 rows=27 loops=1)

9. 0.000 43,983.751 ↓ 27.0 27 1

HashAggregate (cost=105.50..105.56 rows=1 width=19) (actual time=43,983.634..43,983.751 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.423 2.007 ↑ 1.0 1 1

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

12. 0.986 0.986 ↓ 46.0 46 1

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

  • Filter: (model_id = 129)
  • Rows Removed by Filter: 688
13. 0.598 0.598 ↓ 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.013..0.013 rows=0 loops=46)

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

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

15. 0.343 0.343 ↓ 46.0 46 1

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

  • Filter: (model_id = 129)
  • 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. 7.869 43,981.359 ↓ 270.0 270 1

Nested Loop Left Join (cost=2.25..46.96 rows=1 width=19) (actual time=22.434..43,981.359 rows=270 loops=1)

  • Join Filter: (od.operation_data_id = am.operation_data_id)
18. 19.258 43,972.410 ↓ 270.0 270 1

Merge Semi Join (cost=2.25..34.40 rows=1 width=19) (actual time=21.952..43,972.410 rows=270 loops=1)

  • Merge Cond: (od.acquisition_time = cq_operation_data_history.acquisition_time)
19. 8.789 8.789 ↓ 553.0 553 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=0.775..8.789 rows=553 loops=1)

  • Index Cond: (machine_id = 25)
  • Filter: ((operation_data_id = 4599) OR (operation_data_id = 4595) OR (operation_data_id = 4594))
  • Rows Removed by Filter: 6992
20. 29.991 43,944.363 ↓ 3,690.0 3,690 1

Materialize (cost=1.69..25.79 rows=1 width=32) (actual time=16.424..43,944.363 rows=3,690 loops=1)

21. 1,418.346 43,914.372 ↓ 3,690.0 3,690 1

Nested Loop (cost=1.69..25.79 rows=1 width=32) (actual time=16.405..43,914.372 rows=3,690 loops=1)

  • Join Filter: (cq_operation_data_history.acquisition_time = cq_operation_data_history_1.acquisition_time)
  • Rows Removed by Join Filter: 339480
22. 3,042.808 30,071.796 ↓ 3,690.0 3,690 1

Nested Loop (cost=1.12..17.19 rows=1 width=24) (actual time=14.147..30,071.796 rows=3,690 loops=1)

  • Join Filter: (main.acquisition_time = cq_operation_data_history.acquisition_time)
  • Rows Removed by Join Filter: 736032
23. 96.744 96.744 ↓ 7,954.0 7,954 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=10.919..96.744 rows=7,954 loops=1)

  • Index Cond: (machine_id = 25)
  • 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: 3854
  • Heap Fetches: 11808
24. 26,932.244 26,932.244 ↓ 93.0 93 7,954

Index Scan using cq_operation_data_history_index01 on cq_operation_data_history (cost=0.56..8.59 rows=1 width=16) (actual time=0.938..3.386 rows=93 loops=7,954)

  • Index Cond: (machine_id = 25)
  • Filter: ((operation_data_id = $1) AND (COALESCE(number_data, 0::numeric) = 35.0))
  • Rows Removed by Filter: 11715
25. 12,424.230 12,424.230 ↓ 93.0 93 3,690

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=0.936..3.367 rows=93 loops=3,690)

  • Index Cond: (machine_id = 25)
  • Filter: ((operation_data_id = $3) AND ((COALESCE(string_data, ''::character varying))::text = '32429 AA030 01'::text))
  • Rows Removed by Filter: 11715
26. 1.080 1.080 ↓ 0.0 0 270

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

  • 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 = 4599)