explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rGRp

Settings
# exclusive inclusive rows x rows loops node
1. 0.210 10,600.843 ↑ 144.0 1 1

Sort (cost=2,520,141.9..2,520,142.26 rows=144 width=319) (actual time=10,600.842..10,600.843 rows=1 loops=1)

  • Sort Key: (upper((ym_sscc_label.name)::text))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6,087,689, temp read=1,651 written=1,651
2. 0.030 10,600.633 ↑ 144.0 1 1

Nested Loop (cost=2,518,888.46..2,520,136.73 rows=144 width=319) (actual time=10,600.629..10,600.633 rows=1 loops=1)

  • Filter: ((a.c_location_id IS NULL) OR (NOT (hashed SubPlan 1)))
  • Buffers: shared hit=6,087,689, temp read=1,651 written=1,651
3. 10.639 10,600.581 ↑ 289.0 1 1

Subquery Scan on ym_sscc_label (cost=2,518,881.02..2,519,893.35 rows=289 width=185) (actual time=10,600.578..10,600.581 rows=1 loops=1)

  • Filter: (ym_sscc_label.m_logunit_id = '1057186'::numeric)
  • Buffers: shared hit=6,087,685, temp read=1,651 written=1,651
4. 13.831 10,589.942 ↑ 1.0 57,186 1

Unique (cost=2,518,881.02..2,519,170.26 rows=57,847 width=355) (actual time=10,543.949..10,589.942 rows=57,186 loops=1)

  • Buffers: shared hit=6,087,685, temp read=1,651 written=1,651
5. 776.089 10,576.111 ↓ 1.1 65,091 1

Sort (cost=2,518,881.02..2,519,025.64 rows=57,847 width=355) (actual time=10,543.948..10,576.111 rows=65,091 loops=1)

  • Sort Key: lu.sscc, asi.guaranteedate, asi.lot
  • Sort Method: external merge Disk: 13,200kB
  • Buffers: shared hit=6,087,685, temp read=1,651 written=1,651
6. 293.047 9,800.022 ↓ 1.1 65,091 1

Nested Loop (cost=21,004.03..2,508,745.74 rows=57,847 width=355) (actual time=295.886..9,800.022 rows=65,091 loops=1)

  • Buffers: shared hit=6,087,685
7. 64.506 9,311.702 ↓ 1.1 65,091 1

Nested Loop (cost=21,003.75..2,487,096.86 rows=57,847 width=159) (actual time=295.839..9,311.702 rows=65,091 loops=1)

  • Buffers: shared hit=5,898,070
8. 40.703 934.857 ↓ 1.0 59,801 1

Hash Join (cost=20,962.03..71,493.78 rows=57,847 width=133) (actual time=295.687..934.857 rows=59,801 loops=1)

  • Buffers: shared hit=257,313
9. 46.754 892.100 ↓ 1.0 59,801 1

Hash Join (cost=20,724.72..70,461.07 rows=57,847 width=120) (actual time=293.595..892.1 rows=59,801 loops=1)

  • Buffers: shared hit=257,157
10. 46.980 759.998 ↓ 1.0 59,801 1

Hash Join (cost=11,473.41..60,416.32 rows=57,847 width=102) (actual time=207.625..759.998 rows=59,801 loops=1)

  • Buffers: shared hit=249,765
11. 46.452 651.567 ↓ 1.0 59,801 1

Hash Join (cost=5,398.18..53,545.69 rows=57,847 width=85) (actual time=145.578..651.567 rows=59,801 loops=1)

  • Buffers: shared hit=245,762
12. 46.621 603.547 ↓ 1.0 59,801 1

Nested Loop (cost=5,236.9..52,589.09 rows=57,847 width=51) (actual time=143.981..603.547 rows=59,801 loops=1)

  • Buffers: shared hit=245,665
13. 54.886 317.722 ↓ 1.0 59,801 1

Hash Join (cost=5,236.48..24,594.56 rows=57,847 width=43) (actual time=143.96..317.722 rows=59,801 loops=1)

  • Buffers: shared hit=13,202
14. 113.672 231.895 ↓ 1.0 59,801 1

Hash Join (cost=2,699.56..21,655.05 rows=57,847 width=38) (actual time=112.709..231.895 rows=59,801 loops=1)

  • Buffers: shared hit=12,012
15. 75.385 75.385 ↑ 1.1 498,940 1

Seq Scan on m_packageline pl (cost=0..16,307.98 rows=569,398 width=20) (actual time=0.009..75.385 rows=498,940 loops=1)

  • Buffers: shared hit=10,614
16. 26.511 42.838 ↑ 1.0 57,186 1

Hash (cost=1,976.47..1,976.47 rows=57,847 width=25) (actual time=42.838..42.838 rows=57,186 loops=1)

  • Buffers: shared hit=1,398
17. 16.327 16.327 ↑ 1.0 57,186 1

Seq Scan on m_logunit lu (cost=0..1,976.47 rows=57,847 width=25) (actual time=0.021..16.327 rows=57,186 loops=1)

  • Buffers: shared hit=1,398
18. 15.835 30.941 ↑ 1.0 58,881 1

Hash (cost=1,788.63..1,788.63 rows=59,863 width=18) (actual time=30.941..30.941 rows=58,881 loops=1)

  • Buffers: shared hit=1,190
19. 15.106 15.106 ↑ 1.0 58,881 1

Seq Scan on m_attributesetinstance asi (cost=0..1,788.63 rows=59,863 width=18) (actual time=0.011..15.106 rows=58,881 loops=1)

  • Buffers: shared hit=1,190
20. 239.204 239.204 ↑ 1.0 1 59,801

Index Scan using m_inoutline_pkey on m_inoutline iol (cost=0.42..0.47 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=59,801)

  • Index Cond: (pl.m_inoutline_id = iol.m_inoutline_id)
  • Buffers: shared hit=232,463
21. 0.765 1.568 ↓ 1.0 2,871 1

Hash (cost=125.57..125.57 rows=2,857 width=47) (actual time=1.567..1.568 rows=2,871 loops=1)

  • Buffers: shared hit=97
22. 0.803 0.803 ↓ 1.0 2,871 1

Seq Scan on m_product p (cost=0..125.57 rows=2,857 width=47) (actual time=0.009..0.803 rows=2,871 loops=1)

  • Buffers: shared hit=97
23. 27.608 61.451 ↑ 1.0 88,035 1

Hash (cost=4,923.99..4,923.99 rows=92,099 width=24) (actual time=61.451..61.451 rows=88,035 loops=1)

  • Buffers: shared hit=4,003
24. 33.843 33.843 ↑ 1.0 88,035 1

Seq Scan on m_inout io (cost=0..4,923.99 rows=92,099 width=24) (actual time=0.01..33.843 rows=88,035 loops=1)

  • Buffers: shared hit=4,003
25. 27.710 85.348 ↓ 1.0 82,689 1

Hash (cost=8,218.36..8,218.36 rows=82,636 width=30) (actual time=85.347..85.348 rows=82,689 loops=1)

  • Buffers: shared hit=7,392
26. 57.638 57.638 ↓ 1.0 82,689 1

Seq Scan on c_order o (cost=0..8,218.36 rows=82,636 width=30) (actual time=0.012..57.638 rows=82,689 loops=1)

  • Buffers: shared hit=7,392
27. 0.929 2.054 ↑ 1.0 3,614 1

Hash (cost=192.14..192.14 rows=3,614 width=25) (actual time=2.054..2.054 rows=3,614 loops=1)

  • Buffers: shared hit=156
28. 1.125 1.125 ↑ 1.0 3,614 1

Seq Scan on c_bpartner bp (cost=0..192.14 rows=3,614 width=25) (actual time=0.009..1.125 rows=3,614 loops=1)

  • Buffers: shared hit=156
29. 119.602 8,312.339 ↑ 1.0 1 59,801

Subquery Scan on pallets (cost=41.72..41.75 rows=1 width=51) (actual time=0.13..0.139 rows=1 loops=59,801)

  • Filter: ((pallets.sscc)::text = (lu.sscc)::text)
  • Buffers: shared hit=5,640,757
30. 956.816 8,192.737 ↓ 15.0 15 59,801

WindowAgg (cost=41.72..41.74 rows=1 width=145) (actual time=0.121..0.137 rows=15 loops=59,801)

  • Buffers: shared hit=5,640,757
31. 1,734.229 7,235.921 ↓ 15.0 15 59,801

Sort (cost=41.72..41.72 rows=1 width=23) (actual time=0.119..0.121 rows=15 loops=59,801)

  • Sort Key: lu2.sscc
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=5,640,757
32. 806.197 5,501.692 ↓ 15.0 15 59,801

Nested Loop (cost=1.14..41.71 rows=1 width=23) (actual time=0.012..0.092 rows=15 loops=59,801)

  • Buffers: shared hit=5,640,757
33. 526.374 2,930.249 ↓ 1.2 15 59,801

Nested Loop (cost=0.85..37.86 rows=12 width=11) (actual time=0.009..0.049 rows=15 loops=59,801)

  • Buffers: shared hit=2,988,230
34. 418.607 418.607 ↑ 1.0 11 59,801

Index Scan using m_inoutline_inout on m_inoutline iol2 (cost=0.42..8.68 rows=11 width=6) (actual time=0.004..0.007 rows=11 loops=59,801)

  • Index Cond: (iol2.m_inout_id = io.m_inout_id)
  • Buffers: shared hit=300,791
35. 1,985.268 1,985.268 ↑ 1.0 1 661,756

Index Scan using fki_minoutline_mpackageline on m_packageline pl2 (cost=0.42..2.64 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=661,756)

  • Index Cond: (pl2.m_inoutline_id = iol2.m_inoutline_id)
  • Buffers: shared hit=2,687,439
36. 1,765.246 1,765.246 ↑ 1.0 1 882,623

Index Scan using m_logunit_key on m_logunit lu2 (cost=0.29..0.31 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=882,623)

  • Index Cond: (lu2.m_logunit_id = pl2.m_logunit_id)
  • Buffers: shared hit=2,652,527
37. 195.273 195.273 ↑ 1.0 1 65,091

Index Scan using c_bpartner_location_pkey on c_bpartner_location bpl (cost=0.28..0.3 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=65,091)

  • Index Cond: (bpl.c_bpartner_location_id = io.c_bpartner_location_id)
  • Buffers: shared hit=189,615
38. 0.018 0.018 ↑ 1.0 1 1

Index Scan using c_location_pkey on c_location a (cost=0.28..0.8 rows=1 width=16) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: (ym_sscc_label.c_location_id = a.c_location_id)
  • Buffers: shared hit=3
39.          

SubPlan (for Nested Loop)

40. 0.004 0.004 ↓ 0.0 0 1

Index Scan using ad_private_access_pkey on ad_private_access ad_private_access (cost=0.15..7.15 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (ad_private_access.ad_table_id = '54578'::numeric)
  • Filter: ((ad_private_access.ad_user_id <> '100'::numeric) AND (ad_private_access.isactive = 'Y'::bpchar))
  • Buffers: shared hit=1
Planning time : 4.232 ms
Execution time : 10,606.015 ms