explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 39cS

Settings
# exclusive inclusive rows x rows loops node
1. 140.264 2,407.645 ↓ 0.0 0 1

Insert on tmp_src_data (cost=922,518.78..1,156,965.74 rows=826,670 width=24) (actual time=2,407.644..2,407.645 rows=0 loops=1)

2. 260.851 2,267.381 ↑ 8.7 94,497 1

Merge Join (cost=922,518.78..1,148,699.04 rows=826,670 width=24) (actual time=56.910..2,267.381 rows=94,497 loops=1)

  • Merge Cond: ((device_data_cache_0.device_id = d.device_id) AND (device_data_cache_0.type_id = d.type_id) AND (device_data_cache_0.nr = d.nr))
3.          

CTE d

4. 1.764 2.912 ↑ 1,664.5 3,110 1

Nested Loop (cost=0.00..64,784.07 rows=5,176,500 width=12) (actual time=0.156..2.912 rows=3,110 loops=1)

5. 1.148 1.148 ↑ 1.1 3,110 1

Seq Scan on tmp_device_list l (cost=0.00..49.70 rows=3,570 width=4) (actual time=0.100..1.148 rows=3,110 loops=1)

6. 0.000 0.000 ↑ 1,450.0 1 3,110

Materialize (cost=0.00..31.75 rows=1,450 width=8) (actual time=0.000..0.000 rows=1 loops=3,110)

7. 0.051 0.051 ↑ 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.051..0.051 rows=1 loops=1)

8. 194.825 1,987.612 ↓ 1.0 1,283,863 1

Merge Append (cost=1.19..143,626.69 rows=1,277,573 width=32) (actual time=0.224..1,987.612 rows=1,283,863 loops=1)

  • Sort Key: device_data_cache_0.device_id, device_data_cache_0.type_id, device_data_cache_0.nr
9. 125.507 125.507 ↓ 1.1 77,152 1

Index Scan using ix_device_data_cache_p0 on device_data_cache_0 (cost=0.42..9,766.77 rows=71,900 width=32) (actual time=0.119..125.507 rows=77,152 loops=1)

10. 1,667.255 1,667.255 ↓ 1.0 1,206,711 1

Index Scan using ix_device_data_cache_p1 on device_data_cache_1 (cost=0.43..100,713.39 rows=1,202,953 width=32) (actual time=0.077..1,667.255 rows=1,206,711 loops=1)

11. 0.003 0.003 ↓ 0.0 0 1

Index Scan using ix_device_data_cache_p2 on device_data_cache_2 (cost=0.15..64.55 rows=1,360 width=32) (actual time=0.003..0.003 rows=0 loops=1)

12. 0.022 0.022 ↓ 0.0 0 1

Index Scan using ix_device_data_cache on device_data_cache (cost=0.15..4,336.55 rows=1,360 width=32) (actual time=0.022..0.022 rows=0 loops=1)

13. 11.217 18.918 ↑ 54.8 94,476 1

Materialize (cost=857,733.52..883,616.02 rows=5,176,500 width=12) (actual time=5.778..18.918 rows=94,476 loops=1)

14. 3.423 7.701 ↑ 1,664.5 3,110 1

Sort (cost=857,733.52..870,674.77 rows=5,176,500 width=12) (actual time=5.752..7.701 rows=3,110 loops=1)

  • Sort Key: d.device_id, d.type_id, d.nr
  • Sort Method: quicksort Memory: 242kB
15. 4.278 4.278 ↑ 1,664.5 3,110 1

CTE Scan on d (cost=0.00..103,530.00 rows=5,176,500 width=12) (actual time=0.158..4.278 rows=3,110 loops=1)

Planning time : 1.145 ms
Execution time : 2,407.797 ms