explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yaa

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2.280 46,548.364 ↑ 2.8 28 1

Nested Loop (cost=1,001.00..918,843.05 rows=79 width=240) (actual time=33,848.330..46,548.364 rows=28 loops=1)

  • Join Filter: (ml.to_stock_type = st_to.stock_type_id)
  • Rows Removed by Join Filter: 140
  • Buffers: shared hit=4,181,589 read=150,527 dirtied=83
2.          

CTE reason

3. 0.006 0.017 ↑ 1.0 14 1

Append (cost=0.00..0.28 rows=14 width=36) (actual time=0.003..0.017 rows=14 loops=1)

4. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=1)

5. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=1)

6. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=1)

7. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.001 rows=1 loops=1)

8. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.001 rows=1 loops=1)

9. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.000 rows=1 loops=1)

10. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=1)

11. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.000 rows=1 loops=1)

12. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.001 rows=1 loops=1)

13. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.001 rows=1 loops=1)

14. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.001 rows=1 loops=1)

15. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.000 rows=1 loops=1)

16. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.000 rows=1 loops=1)

17. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.001 rows=1 loops=1)

18.          

CTE stock_type

19. 0.002 0.008 ↑ 1.0 6 1

Append (cost=0.00..0.12 rows=6 width=36) (actual time=0.001..0.008 rows=6 loops=1)

20. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.001 rows=1 loops=1)

21. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=1)

22. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.001 rows=1 loops=1)

23. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=1)

24. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=1)

25. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=1)

26. 0.050 46,546.028 ↑ 3.6 28 1

Nested Loop (cost=1,000.60..918,828.56 rows=101 width=176) (actual time=33,846.142..46,546.028 rows=28 loops=1)

  • Join Filter: (ml.from_stock_type = st_from.stock_type_id)
  • Rows Removed by Join Filter: 188
  • Buffers: shared hit=4,181,589 read=150,527 dirtied=83
27. 0.000 46,545.942 ↑ 3.6 36 1

Nested Loop (cost=1,000.60..918,810.81 rows=131 width=144) (actual time=33,846.136..46,545.942 rows=36 loops=1)

  • Join Filter: (ml.reason = reason.reason)
  • Rows Removed by Join Filter: 482
  • Buffers: shared hit=4,181,589 read=150,527 dirtied=83
28. 1,465.006 46,591.423 ↑ 7.8 37 1

Gather Merge (cost=1,000.60..918,719.32 rows=290 width=112) (actual time=33,846.114..46,591.423 rows=37 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=13,675,981 read=495,836 dirtied=219
29. 45,126.417 45,126.417 ↑ 10.1 12 3 / 3

Parallel Index Scan using movement_log_at_idx on movement_log ml (cost=0.57..917,685.82 rows=121 width=112) (actual time=22,945.410..45,126.417 rows=12 loops=3)

  • Index Cond: (at >= (LOCALTIMESTAMP - '1 mon'::interval month))
  • Filter: ((user_id IS NOT NULL) AND (reason <> 35) AND (warehouse_id = '15431806189000'::bigint) AND (item_id = 155,456,566))
  • Rows Removed by Filter: 8,385,966
  • Buffers: shared hit=13,675,981 read=495,836 dirtied=219
30. 0.111 0.111 ↑ 1.0 14 37

CTE Scan on reason (cost=0.00..0.28 rows=14 width=36) (actual time=0.000..0.003 rows=14 loops=37)

31. 0.036 0.036 ↑ 1.0 6 36

CTE Scan on stock_type st_from (cost=0.00..0.12 rows=6 width=36) (actual time=0.000..0.001 rows=6 loops=36)

32. 0.056 0.056 ↑ 1.0 6 28

CTE Scan on stock_type st_to (cost=0.00..0.12 rows=6 width=36) (actual time=0.000..0.002 rows=6 loops=28)

Planning time : 0.575 ms
Execution time : 46,594.242 ms