explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9XOC : Optimization for: plan #Yaa (warehouse_id, at, item_id)

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.232 2,981.749 ↑ 2.8 28 1

Nested Loop (cost=0.97..385,811.65 rows=79 width=240) (actual time=1,580.966..2,981.749 rows=28 loops=1)

  • Join Filter: (ml.to_stock_type = st_to.stock_type_id)
  • Rows Removed by Join Filter: 140
  • Buffers: shared hit=10 read=111487
2.          

CTE reason

3. 0.003 0.017 ↑ 1.0 14 1

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

4. 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)

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.000..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.001..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.001..0.001 rows=1 loops=1)

9. 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)

10. 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)

11. 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)

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.001..0.001 rows=1 loops=1)

15. 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)

16. 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)

17. 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)

18.          

CTE stock_type

19. 0.001 0.007 ↑ 1.0 6 1

Append (cost=0.00..0.12 rows=6 width=36) (actual time=0.001..0.007 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.001..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.001..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.000..0.001 rows=1 loops=1)

26. 0.048 2,981.489 ↑ 3.6 28 1

Nested Loop (cost=0.57..385,797.16 rows=101 width=176) (actual time=1,580.946..2,981.489 rows=28 loops=1)

  • Join Filter: (ml.from_stock_type = st_from.stock_type_id)
  • Rows Removed by Join Filter: 188
  • Buffers: shared hit=10 read=111487
27. 0.136 2,981.405 ↑ 3.6 36 1

Nested Loop (cost=0.57..385,779.41 rows=131 width=144) (actual time=1,580.941..2,981.405 rows=36 loops=1)

  • Join Filter: (ml.reason = reason.reason)
  • Rows Removed by Join Filter: 482
  • Buffers: shared hit=10 read=111487
28. 2,981.158 2,981.158 ↑ 7.8 37 1

Index Scan using movement_log_at_item_id_idx on movement_log ml (cost=0.57..385,687.92 rows=290 width=112) (actual time=1,580.924..2,981.158 rows=37 loops=1)

  • Index Cond: ((warehouse_id = '15431806189000'::bigint) AND (at >= (LOCALTIMESTAMP - '1 mon'::interval month)) AND (item_id = 155456566))
  • Filter: ((user_id IS NOT NULL) AND (reason <> 35))
  • Rows Removed by Filter: 4
  • Buffers: shared hit=10 read=111487
29. 0.111 0.111 ↑ 1.0 14 37

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

30. 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)

31. 0.028 0.028 ↑ 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.001 rows=6 loops=28)

Planning time : 0.659 ms
Execution time : 2,981.877 ms