explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k5Fz

Settings
# exclusive inclusive rows x rows loops node
1. 25,351.517 25,873.261 ↓ 3.4 5,925 1

WindowAgg (cost=2,267.00..284,564.69 rows=1,739 width=656) (actual time=53.257..25,873.261 rows=5,925 loops=1)

2. 36.774 71.444 ↓ 3.4 5,925 1

HashAggregate (cost=2,267.00..2,293.09 rows=1,739 width=250) (actual time=50.919..71.444 rows=5,925 loops=1)

  • Group Key: lo.m_locator_id, st.m_product_id, st.m_attributesetinstance_id, st.ad_client_id, st.ad_org_id, wh.name, pr.description, pr.value, pr.name, pr.m_product_category_id, pr.c_uom_id, pr.marchio, pr.linea, pr.gamma
  • Filter: (sum(st.qtyonhand) <> '0'::numeric)
  • Rows Removed by Filter: 6,252
3. 5.210 34.670 ↓ 7.0 12,177 1

Hash Join (cost=1,815.31..2,197.44 rows=1,739 width=250) (actual time=22.229..34.670 rows=12,177 loops=1)

  • Hash Cond: (st.m_product_id = pr.m_product_id)
4. 5.697 14.223 ↓ 7.0 12,177 1

Hash Join (cost=131.51..509.08 rows=1,739 width=133) (actual time=6.951..14.223 rows=12,177 loops=1)

  • Hash Cond: (st.m_locator_id = lo.m_locator_id)
5. 1.600 1.600 ↓ 1.7 13,219 1

Seq Scan on m_storage st (cost=0.00..331.50 rows=7,650 width=112) (actual time=0.013..1.600 rows=13,219 loops=1)

6. 1.975 6.926 ↓ 4.4 3,981 1

Hash (cost=120.10..120.10 rows=913 width=37) (actual time=6.926..6.926 rows=3,981 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 250kB
7. 3.982 4.951 ↓ 4.4 3,981 1

Hash Join (cost=1.34..120.10 rows=913 width=37) (actual time=0.056..4.951 rows=3,981 loops=1)

  • Hash Cond: (lo.m_warehouse_id = wh.m_warehouse_id)
8. 0.934 0.934 ↑ 1.0 3,999 1

Seq Scan on m_locator lo (cost=0.00..106.18 rows=4,018 width=21) (actual time=0.009..0.934 rows=3,999 loops=1)

9. 0.009 0.035 ↑ 1.0 5 1

Hash (cost=1.27..1.27 rows=5 width=23) (actual time=0.035..0.035 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
10. 0.026 0.026 ↑ 1.0 5 1

Seq Scan on m_warehouse wh (cost=0.00..1.27 rows=5 width=23) (actual time=0.012..0.026 rows=5 loops=1)

  • Filter: (isviewapp = 'Y'::bpchar)
  • Rows Removed by Filter: 18
11. 5.308 15.237 ↑ 1.0 14,791 1

Hash (cost=1,498.91..1,498.91 rows=14,791 width=123) (actual time=15.237..15.237 rows=14,791 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 2,429kB
12. 9.929 9.929 ↑ 1.0 14,791 1

Seq Scan on m_product pr (cost=0.00..1,498.91 rows=14,791 width=123) (actual time=0.006..9.929 rows=14,791 loops=1)

13.          

SubPlan (for WindowAgg)

14. 11.850 71.100 ↑ 1.0 1 5,925

Nested Loop (cost=0.56..16.60 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=5,925)

15. 35.550 35.550 ↑ 1.0 1 5,925

Index Scan using m_attributesetinstance_m_attributesetinstance_id_idx on m_attributesetinstance (cost=0.28..8.30 rows=1 width=6) (actual time=0.006..0.006 rows=1 loops=5,925)

  • Index Cond: (m_attributesetinstance_id = st.m_attributesetinstance_id)
16. 23.700 23.700 ↑ 1.0 1 5,925

Index Scan using m_lot_pkey on m_lot (cost=0.28..8.30 rows=1 width=14) (actual time=0.004..0.004 rows=1 loops=5,925)

  • Index Cond: (m_lot_id = m_attributesetinstance.m_lot_id)
17. 17.775 17.775 ↑ 1.0 1 5,925

Seq Scan on m_product_category (cost=0.00..1.09 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=5,925)

  • Filter: (m_product_category_id = pr.m_product_category_id)
  • Rows Removed by Filter: 6
18. 11.850 124.425 ↑ 22.0 1 5,925

Unique (cost=79.75..79.86 rows=22 width=32) (actual time=0.020..0.021 rows=1 loops=5,925)

19. 41.475 112.575 ↑ 1.7 13 5,925

Sort (cost=79.75..79.80 rows=22 width=32) (actual time=0.019..0.019 rows=13 loops=5,925)

  • Sort Key: ((((ad_ref_list.value)::text || ' - '::text) || (ad_ref_list.name)::text))
  • Sort Method: quicksort Memory: 25kB
20. 35.550 71.100 ↑ 1.7 13 5,925

Bitmap Heap Scan on ad_ref_list (cost=4.51..79.26 rows=22 width=32) (actual time=0.009..0.012 rows=13 loops=5,925)

  • Recheck Cond: (((value)::text = (pr.marchio)::text) AND (ad_reference_id = '1000188'::numeric))
  • Heap Blocks: exact=9,355
21. 35.550 35.550 ↑ 1.7 13 5,925

Bitmap Index Scan on ad_ref_list_value (cost=0.00..4.51 rows=22 width=0) (actual time=0.006..0.006 rows=13 loops=5,925)

  • Index Cond: (((value)::text = (pr.marchio)::text) AND (ad_reference_id = '1000188'::numeric))
22. 5.925 35.550 ↑ 1.0 1 5,925

Unique (cost=8.32..8.33 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=5,925)

23. 5.925 29.625 ↑ 1.0 1 5,925

Sort (cost=8.32..8.33 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=5,925)

  • Sort Key: ((((ad_ref_list_1.value)::text || ' - '::text) || (ad_ref_list_1.name)::text))
  • Sort Method: quicksort Memory: 25kB
24. 23.700 23.700 ↑ 1.0 1 5,925

Index Scan using ad_ref_list_value on ad_ref_list ad_ref_list_1 (cost=0.29..8.31 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=5,925)

  • Index Cond: (((value)::text = (pr.gamma)::text) AND (ad_reference_id = '1000190'::numeric))
25. 5.925 41.475 ↑ 1.0 1 5,925

Unique (cost=8.32..8.33 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=5,925)

26. 5.925 35.550 ↑ 1.0 1 5,925

Sort (cost=8.32..8.33 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=5,925)

  • Sort Key: ((((ad_ref_list_2.value)::text || ' - '::text) || (ad_ref_list_2.name)::text))
  • Sort Method: quicksort Memory: 25kB
27. 29.625 29.625 ↑ 1.0 1 5,925

Index Scan using ad_ref_list_value on ad_ref_list ad_ref_list_2 (cost=0.29..8.31 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=5,925)

  • Index Cond: (((value)::text = (pr.linea)::text) AND (ad_reference_id = '1000189'::numeric))
28. 17.775 17.775 ↑ 1.0 1 5,925

Seq Scan on c_uom (cost=0.00..1.16 rows=1 width=3) (actual time=0.002..0.003 rows=1 loops=5,925)

  • Filter: (c_uom_id = pr.c_uom_id)
  • Rows Removed by Filter: 12
29. 17.775 142.200 ↑ 1.0 1 5,925

Aggregate (cost=46.67..46.68 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=5,925)

30. 82.950 124.425 ↓ 5.0 5 5,925

Bitmap Heap Scan on m_transaction tr (cost=4.38..46.67 rows=1 width=8) (actual time=0.014..0.021 rows=5 loops=5,925)

  • Recheck Cond: (m_product_id = st.m_product_id)
  • Filter: (m_locator_id = lo.m_locator_id)
  • Rows Removed by Filter: 28
  • Heap Blocks: exact=48,960
31. 41.475 41.475 ↓ 3.1 37 5,925

Bitmap Index Scan on m_transsaction_product (cost=0.00..4.38 rows=12 width=0) (actual time=0.007..0.007 rows=37 loops=5,925)

  • Index Cond: (m_product_id = st.m_product_id)
Planning time : 3.888 ms
Execution time : 25,876.122 ms