explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5s76

Settings
# exclusive inclusive rows x rows loops node
1. 89.577 3,014.546 ↓ 0.0 0 1

Insert on tmp_src_data (cost=209,300.71..220,613.32 rows=752,990 width=24) (actual time=3,014.546..3,014.546 rows=0 loops=1)

2. 31.827 2,924.969 ↑ 8.9 85,076 1

Merge Join (cost=209,300.71..220,613.32 rows=752,990 width=24) (actual time=2,858.696..2,924.969 rows=85,076 loops=1)

  • Merge Cond: (d.device_id = device_data_cache_0.device_id)
3. 3.607 4.401 ↑ 1.1 3,110 1

Sort (cost=260.36..269.29 rows=3,570 width=4) (actual time=3.529..4.401 rows=3,110 loops=1)

  • Sort Key: d.device_id
  • Sort Method: quicksort Memory: 242kB
4. 0.794 0.794 ↑ 1.1 3,110 1

Seq Scan on tmp_device_list d (cost=0.00..49.70 rows=3,570 width=4) (actual time=0.046..0.794 rows=3,110 loops=1)

5. 179.591 2,888.741 ↓ 2.0 85,076 1

Sort (cost=209,040.35..209,145.81 rows=42,184 width=24) (actual time=2,855.155..2,888.741 rows=85,076 loops=1)

  • Sort Key: device_data_cache_0.device_id
  • Sort Method: external sort Disk: 3496kB
6. 157.670 2,709.150 ↓ 2.0 85,076 1

Merge Join (cost=196,333.68..205,799.68 rows=42,184 width=24) (actual time=2,621.561..2,709.150 rows=85,076 loops=1)

  • Merge Cond: ((device_data_cache_0.type_id = tk.type_id) AND (device_data_cache_0.nr = tk.nr))
7. 1,925.372 2,545.326 ↑ 1.8 645,559 1

Sort (cost=196,233.04..199,142.30 rows=1,163,705 width=32) (actual time=2,239.044..2,545.326 rows=645,559 loops=1)

  • Sort Key: device_data_cache_0.type_id, device_data_cache_0.nr
  • Sort Method: external merge Disk: 53544kB
8. 143.354 619.954 ↓ 1.0 1,191,780 1

Append (cost=0.00..23,296.05 rows=1,163,705 width=32) (actual time=0.062..619.954 rows=1,191,780 loops=1)

9. 46.566 46.566 ↓ 1.1 77,152 1

Seq Scan on device_data_cache_0 (cost=0.00..1,883.00 rows=71,900 width=32) (actual time=0.061..46.566 rows=77,152 loops=1)

10. 429.980 429.980 ↓ 1.0 1,114,628 1

Seq Scan on device_data_cache_1 (cost=0.00..21,365.85 rows=1,089,085 width=32) (actual time=0.041..429.980 rows=1,114,628 loops=1)

11. 0.038 0.038 ↓ 0.0 0 1

Seq Scan on device_data_cache_2 (cost=0.00..23.60 rows=1,360 width=32) (actual time=0.038..0.038 rows=0 loops=1)

12. 0.016 0.016 ↓ 0.0 0 1

Seq Scan on device_data_cache (cost=0.00..23.60 rows=1,360 width=32) (actual time=0.016..0.016 rows=0 loops=1)

13. 6.121 6.154 ↑ 1,450.0 1 1

Sort (cost=100.64..104.26 rows=1,450 width=8) (actual time=0.056..6.154 rows=1 loops=1)

  • Sort Key: tk.type_id, tk.nr
  • Sort Method: quicksort Memory: 25kB
14. 0.033 0.033 ↑ 1,450.0 1 1

Seq Scan on tmp_task_row tk (cost=0.00..24.50 rows=1,450 width=8) (actual time=0.032..0.033 rows=1 loops=1)

Planning time : 2.260 ms
Execution time : 3,034.384 ms