explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8X5s

Settings
# exclusive inclusive rows x rows loops node
1. 157.951 2,448.776 ↓ 0.0 0 1

Insert on tmp_src_data (cost=922,518.78..1,141,892.65 rows=882,009 width=24) (actual time=2,448.776..2,448.776 rows=0 loops=1)

2. 293.491 2,290.825 ↑ 9.0 97,864 1

Merge Join (cost=922,518.78..1,133,072.56 rows=882,009 width=24) (actual time=66.369..2,290.825 rows=97,864 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.800 2.784 ↑ 1,664.5 3,110 1

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

5. 0.984 0.984 ↑ 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.086..0.984 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.082 0.082 ↑ 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.081..0.082 rows=1 loops=1)

8. 210.306 1,976.409 ↑ 1.1 1,296,053 1

Merge Append (cost=1.19..126,805.40 rows=1,363,097 width=32) (actual time=0.205..1,976.409 rows=1,296,053 loops=1)

  • Sort Key: device_data_cache_0.device_id, device_data_cache_0.type_id, device_data_cache_0.nr
9. 110.613 110.613 ↑ 1.1 77,152 1

Index Scan using ix_device_data_cache_p0 on device_data_cache_0 (cost=0.42..7,088.16 rows=82,126 width=32) (actual time=0.115..110.613 rows=77,152 loops=1)

10. 1,655.463 1,655.463 ↑ 1.0 1,218,901 1

Index Scan using ix_device_data_cache_p1 on device_data_cache_1 (cost=0.43..84,646.42 rows=1,278,251 width=32) (actual time=0.060..1,655.463 rows=1,218,901 loops=1)

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

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

13. 13.279 20.925 ↑ 52.9 97,842 1

Materialize (cost=857,733.52..883,616.02 rows=5,176,500 width=12) (actual time=5.514..20.925 rows=97,842 loops=1)

14. 3.543 7.646 ↑ 1,664.5 3,110 1

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

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

Planning time : 2.120 ms
Execution time : 2,448.943 ms