explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OU1o

Settings
# exclusive inclusive rows x rows loops node
1. 1,637.243 3,995.333 ↓ 666.5 11,208,302 1

Hash Join (cost=47,004.45..57,983.80 rows=16,816 width=6) (actual time=455.490..3,995.333 rows=11,208,302 loops=1)

  • Hash Cond: (io.m_warehouse_id = whouse.m_warehouse_id)
2. 1,829.259 2,358.083 ↓ 866.3 11,208,302 1

Hash Join (cost=47,003.22..57,934.22 rows=12,938 width=20) (actual time=455.474..2,358.083 rows=11,208,302 loops=1)

  • Hash Cond: (("*SELECT* 1".m_product_id = p.m_product_id) AND (loc.m_warehouse_id = io.m_warehouse_id))
3. 18.366 73.597 ↓ 15.4 100,744 1

Hash Join (cost=1.29..10,345.74 rows=6,531 width=23) (actual time=0.012..73.597 rows=100,744 loops=1)

  • Hash Cond: ("*SELECT* 1".m_locator_id = loc.m_locator_id)
4. 5.288 55.226 ↓ 1.0 100,744 1

Append (cost=0.00..9,070.39 rows=100,473 width=338) (actual time=0.005..55.226 rows=100,744 loops=1)

5. 12.230 48.447 ↑ 1.0 99,525 1

Result (cost=0.00..8,183.96 rows=99,606 width=338) (actual time=0.005..48.447 rows=99,525 loops=1)

6. 4.667 36.217 ↑ 1.0 99,525 1

Append (cost=0.00..6,938.88 rows=99,606 width=310) (actual time=0.004..36.217 rows=99,525 loops=1)

7. 12.001 30.152 ↓ 1.0 98,525 1

Subquery Scan on *SELECT* 1 (cost=0.00..6,049.44 rows=98,153 width=148) (actual time=0.004..30.152 rows=98,525 loops=1)

8. 18.151 18.151 ↓ 1.0 98,525 1

Seq Scan on m_storageonhand s (cost=0.00..4,822.53 rows=98,153 width=120) (actual time=0.003..18.151 rows=98,525 loops=1)

9. 0.140 1.398 ↑ 1.5 1,000 1

Subquery Scan on *SELECT* 2 (cost=1.23..391.41 rows=1,453 width=151) (actual time=0.033..1.398 rows=1,000 loops=1)

10. 0.264 1.258 ↑ 1.5 1,000 1

Hash Join (cost=1.23..373.25 rows=1,453 width=123) (actual time=0.032..1.258 rows=1,000 loops=1)

  • Hash Cond: (sr.m_warehouse_id = w.m_warehouse_id)
11. 0.981 0.981 ↑ 1.5 1,000 1

Seq Scan on m_storagereservation sr (cost=0.00..366.60 rows=1,453 width=108) (actual time=0.012..0.981 rows=1,000 loops=1)

  • Filter: ((qty <> '0'::numeric) AND (issotrx = 'Y'::bpchar))
  • Rows Removed by Filter: 4677
12. 0.008 0.013 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=13) (actual time=0.013..0.013 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.005 0.005 ↑ 1.0 10 1

Seq Scan on m_warehouse w (cost=0.00..1.10 rows=10 width=13) (actual time=0.003..0.005 rows=10 loops=1)

14. 0.198 1.491 ↓ 1.4 1,219 1

Subquery Scan on *SELECT* 3 (cost=1.23..384.07 rows=867 width=179) (actual time=0.043..1.491 rows=1,219 loops=1)

15. 0.304 1.293 ↓ 1.4 1,219 1

Hash Join (cost=1.23..371.06 rows=867 width=123) (actual time=0.042..1.293 rows=1,219 loops=1)

  • Hash Cond: (so.m_warehouse_id = w_1.m_warehouse_id)
16. 0.972 0.972 ↓ 1.4 1,219 1

Seq Scan on m_storagereservation so (cost=0.00..366.60 rows=867 width=108) (actual time=0.018..0.972 rows=1,219 loops=1)

  • Filter: ((qty <> '0'::numeric) AND (issotrx = 'N'::bpchar))
  • Rows Removed by Filter: 4458
17. 0.012 0.017 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=13) (actual time=0.017..0.017 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.005 0.005 ↑ 1.0 10 1

Seq Scan on m_warehouse w_1 (cost=0.00..1.10 rows=10 width=13) (actual time=0.003..0.005 rows=10 loops=1)

19. 0.002 0.005 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=13) (actual time=0.005..0.005 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.003 0.003 ↑ 1.0 13 1

Seq Scan on m_locator loc (cost=0.00..1.13 rows=13 width=13) (actual time=0.002..0.003 rows=13 loops=1)

21. 82.142 455.227 ↑ 1.0 562,570 1

Hash (cost=38,490.40..38,490.40 rows=567,435 width=26) (actual time=455.227..455.227 rows=562,570 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 41155kB
22. 100.862 373.085 ↑ 1.0 562,570 1

Hash Join (cost=11,726.39..38,490.40 rows=567,435 width=26) (actual time=72.061..373.085 rows=562,570 loops=1)

  • Hash Cond: (iol.m_product_id = p.m_product_id)
23. 163.795 262.716 ↑ 1.0 562,570 1

Hash Join (cost=10,032.56..35,306.77 rows=567,435 width=20) (actual time=62.543..262.716 rows=562,570 loops=1)

  • Hash Cond: (iol.m_inout_id = io.m_inout_id)
24. 36.443 36.443 ↓ 1.0 598,320 1

Seq Scan on m_inoutline iol (cost=0.00..23,709.73 rows=595,973 width=25) (actual time=0.003..36.443 rows=598,320 loops=1)

25. 17.163 62.478 ↓ 1.0 135,156 1

Hash (cost=8,346.67..8,346.67 rows=134,871 width=39) (actual time=62.478..62.478 rows=135,156 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 11590kB
26. 45.315 45.315 ↓ 1.0 135,156 1

Seq Scan on m_inout io (cost=0.00..8,346.67 rows=134,871 width=39) (actual time=0.005..45.315 rows=135,156 loops=1)

  • Filter: (docstatus <> ALL ('{RE,VO}'::bpchar[]))
  • Rows Removed by Filter: 7013
27. 2.267 9.507 ↓ 1.0 22,041 1

Hash (cost=1,418.37..1,418.37 rows=22,037 width=13) (actual time=9.507..9.507 rows=22,041 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1161kB
28. 7.240 7.240 ↓ 1.0 22,041 1

Seq Scan on m_product p (cost=0.00..1,418.37 rows=22,037 width=13) (actual time=0.004..7.240 rows=22,041 loops=1)

29. 0.002 0.007 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=7) (actual time=0.007..0.007 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.005 0.005 ↑ 1.0 10 1

Seq Scan on m_warehouse whouse (cost=0.00..1.10 rows=10 width=7) (actual time=0.003..0.005 rows=10 loops=1)

Planning time : 1.092 ms
Execution time : 4,223.909 ms