explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jFON

Settings
# exclusive inclusive rows x rows loops node
1. 1.583 45.158 ↑ 1.4 144 1

Limit (cost=929.69..930.19 rows=200 width=28) (actual time=42.756..45.158 rows=144 loops=1)

2. 2.108 43.575 ↑ 1.4 144 1

Sort (cost=929.69..930.19 rows=200 width=28) (actual time=42.745..43.575 rows=144 loops=1)

  • Sort Key: (row_number() OVER (?))
  • Sort Method: quicksort Memory: 31kB
3. 1.947 41.467 ↑ 1.4 144 1

HashAggregate (cost=920.05..922.05 rows=200 width=28) (actual time=40.582..41.467 rows=144 loops=1)

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

Hash Left Join (cost=875.00..912.55 rows=1,000 width=28) (actual time=35.253..39.520 rows=144 loops=1)

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

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

6. 0.931 0.931 ↑ 6.9 144 1

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

7. 0.013 20.225 ↓ 0.0 0 1

Hash (cost=874.97..874.97 rows=1 width=44) (actual time=20.225..20.225 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
8. 0.011 20.212 ↓ 0.0 0 1

Subquery Scan on target (cost=874.90..874.97 rows=1 width=44) (actual time=20.212..20.212 rows=0 loops=1)

9. 0.000 20.201 ↓ 0.0 0 1

HashAggregate (cost=874.90..874.96 rows=1 width=19) (actual time=20.201..20.201 rows=0 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.000 0.000 ↓ 0.0 0

Hash Join (cost=1.49..21.27 rows=1 width=8) (never executed)

  • Hash Cond: (d.id = i.operation_data_id)
12. 0.000 0.000 ↓ 0.0 0

Seq Scan on cq_operation_data d (cost=0.00..19.60 rows=46 width=8) (never executed)

  • Filter: (model_id = 128)
13. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.32..1.32 rows=13 width=8) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Seq Scan on cqc_operation_data_of_product_information i (cost=0.00..1.32 rows=13 width=8) (never executed)

  • Filter: ((type)::text = 'PRODUCT_NO'::text)
15. 0.350 20.102 ↓ 0.0 0 1

Hash Join (cost=1.49..21.27 rows=1 width=8) (actual time=20.102..20.102 rows=0 loops=1)

  • Hash Cond: (d_1.id = i_1.operation_data_id)
16. 19.509 19.509 ↑ 1.0 46 1

Seq Scan on cq_operation_data d_1 (cost=0.00..19.60 rows=46 width=8) (actual time=18.916..19.509 rows=46 loops=1)

  • Filter: (model_id = 128)
  • Rows Removed by Filter: 642
17. 0.106 0.243 ↑ 1.0 13 1

Hash (cost=1.32..1.32 rows=13 width=8) (actual time=0.243..0.243 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
18. 0.137 0.137 ↑ 1.0 13 1

Seq Scan on cqc_operation_data_of_product_information i_1 (cost=0.00..1.32 rows=13 width=8) (actual time=0.028..0.137 rows=13 loops=1)

  • Filter: ((type)::text = 'PRODUCT_NAME'::text)
  • Rows Removed by Filter: 13
19. 0.010 20.190 ↓ 0.0 0 1

Nested Loop Left Join (cost=808.01..832.34 rows=1 width=19) (actual time=20.190..20.190 rows=0 loops=1)

  • Join Filter: (od.operation_data_id = am.operation_data_id)
20. 0.012 20.180 ↓ 0.0 0 1

Nested Loop (cost=808.01..819.24 rows=1 width=19) (actual time=20.180..20.180 rows=0 loops=1)

  • Join Filter: (od.acquisition_time = cq_operation_data_history.acquisition_time)
21. 0.011 20.168 ↓ 0.0 0 1

HashAggregate (cost=807.45..807.46 rows=1 width=32) (actual time=20.168..20.168 rows=0 loops=1)

  • Group Key: main.machine_id, main.acquisition_time
22. 0.011 20.157 ↓ 0.0 0 1

Nested Loop (cost=1.69..807.44 rows=1 width=32) (actual time=20.157..20.157 rows=0 loops=1)

23. 0.011 20.146 ↓ 0.0 0 1

Nested Loop (cost=1.12..798.85 rows=1 width=24) (actual time=20.146..20.146 rows=0 loops=1)

24. 20.135 20.135 ↓ 0.0 0 1

Index Scan using cq_operation_data_history_index04 on cq_operation_data_history cq_operation_data_history_1 (cost=0.56..794.20 rows=1 width=16) (actual time=20.135..20.135 rows=0 loops=1)

  • Index Cond: ((operation_data_id = $1) AND (machine_id = 22))
  • Filter: ((COALESCE(string_data, ''::character varying))::text = '34315 x106T 01 CWA01'::text)
25. 0.000 0.000 ↓ 0.0 0

Index Only Scan using cq_operation_data_history_index02 on cq_operation_data_history main (cost=0.56..4.64 rows=1 width=16) (never executed)

  • Index 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 Fetches: 0
26. 0.000 0.000 ↓ 0.0 0

Index Scan using cq_operation_data_history_index02 on cq_operation_data_history (cost=0.56..8.58 rows=1 width=16) (never executed)

  • Index Cond: ((machine_id = 22) AND (acquisition_time = main.acquisition_time) AND (operation_data_id = $0))
  • Filter: (COALESCE(number_data, 0::numeric) = 37.0)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using cq_operation_data_history_index02 on cq_operation_data_history od (cost=0.56..11.77 rows=1 width=27) (never executed)

  • 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))
28. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..13.06 rows=1 width=8) (never executed)

  • 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.06 rows=1 width=8) (never executed)

  • Filter: (operation_data_id = 4507)
Planning time : 715.321 ms
Execution time : 47.756 ms