explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lBNm

Settings
# exclusive inclusive rows x rows loops node
1. 135.935 2,291.746 ↓ 0.0 0 1

Insert on tmp_src_data (cost=922,518.78..1,138,352.33 rows=841,931 width=24) (actual time=2,291.746..2,291.746 rows=0 loops=1)

2. 260.798 2,155.811 ↑ 8.6 97,550 1

Merge Join (cost=922,518.78..1,129,933.02 rows=841,931 width=24) (actual time=122.396..2,155.811 rows=97,550 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. 2.812 4.295 ↑ 1,664.5 3,110 1

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

5. 1.483 1.483 ↑ 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.140..1.483 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.089 0.089 ↑ 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.088..0.089 rows=1 loops=1)

8. 204.018 1,872.995 ↓ 1.0 1,327,089 1

Merge Append (cost=1.19..124,531.17 rows=1,301,159 width=32) (actual time=0.308..1,872.995 rows=1,327,089 loops=1)

  • Sort Key: device_data_cache_0.device_id, device_data_cache_0.type_id, device_data_cache_0.nr
9. 112.962 112.962 ↑ 1.0 77,152 1

Index Scan using ix_device_data_cache_p0 on device_data_cache_0 (cost=0.42..7,013.55 rows=77,152 width=32) (actual time=0.220..112.962 rows=77,152 loops=1)

10. 1,555.969 1,555.969 ↓ 1.0 1,249,937 1

Index Scan using ix_device_data_cache_p1 on device_data_cache_1 (cost=0.43..83,840.40 rows=1,221,287 width=32) (actual time=0.039..1,555.969 rows=1,249,937 loops=1)

11. 0.004 0.004 ↓ 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.004 rows=0 loops=1)

12. 0.042 0.042 ↓ 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.042..0.042 rows=0 loops=1)

13. 11.542 22.018 ↑ 53.1 97,528 1

Materialize (cost=857,733.52..883,616.02 rows=5,176,500 width=12) (actual time=8.814..22.018 rows=97,528 loops=1)

14. 3.999 10.476 ↑ 1,664.5 3,110 1

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

  • Sort Key: d.device_id, d.type_id, d.nr
  • Sort Method: quicksort Memory: 242kB
15. 6.477 6.477 ↑ 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.242..6.477 rows=3,110 loops=1)

Planning time : 2.341 ms
Execution time : 2,292.038 ms