explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tkT9

Settings
# exclusive inclusive rows x rows loops node
1. 24,137.702 24,812.387 ↓ 3.8 5,820 1

WindowAgg (cost=494.71..340,257.29 rows=1,548 width=1,791) (actual time=20.452..24,812.387 rows=5,820 loops=1)

2. 23.674 34.485 ↓ 3.8 5,820 1

HashAggregate (cost=494.71..517.93 rows=1,548 width=117) (actual time=18.228..34.485 rows=5,820 loops=1)

  • Group Key: m_locator.m_locator_id, m_storage.m_product_id, m_storage.m_attributesetinstance_id, m_storage.ad_client_id, m_storage.ad_org_id
  • Filter: (sum(m_storage.qtyonhand) <> '0'::numeric)
  • Rows Removed by Filter: 5,365
3. 5.473 10.811 ↓ 7.2 11,185 1

Hash Join (cost=131.51..467.62 rows=1,548 width=117) (actual time=3.809..10.811 rows=11,185 loops=1)

  • Hash Cond: (m_storage.m_locator_id = m_locator.m_locator_id)
4. 1.554 1.554 ↓ 1.8 12,161 1

Seq Scan on m_storage (cost=0.00..295.10 rows=6,810 width=112) (actual time=0.012..1.554 rows=12,161 loops=1)

5. 1.054 3.784 ↓ 4.4 3,981 1

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

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 239kB
6. 2.137 2.730 ↓ 4.4 3,981 1

Hash Join (cost=1.34..120.10 rows=913 width=21) (actual time=0.053..2.730 rows=3,981 loops=1)

  • Hash Cond: (m_locator.m_warehouse_id = m_warehouse.m_warehouse_id)
7. 0.561 0.561 ↑ 1.0 3,999 1

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

8. 0.008 0.032 ↑ 1.0 5 1

Hash (cost=1.27..1.27 rows=5 width=7) (actual time=0.032..0.032 rows=5 loops=1)

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

Seq Scan on m_warehouse (cost=0.00..1.27 rows=5 width=7) (actual time=0.011..0.024 rows=5 loops=1)

  • Filter: (isviewapp = 'Y'::bpchar)
  • Rows Removed by Filter: 18
10.          

SubPlan (for WindowAgg)

11. 29.100 29.100 ↑ 1.0 1 5,820

Seq Scan on m_warehouse m_warehouse_1 (cost=0.00..1.27 rows=1 width=16) (actual time=0.003..0.005 rows=1 loops=5,820)

  • Filter: (m_warehouse_id = m_locator.m_warehouse_id)
  • Rows Removed by Filter: 22
12. 34.920 34.920 ↑ 1.0 1 5,820

Index Scan using m_product_id_idx on m_product (cost=0.29..8.30 rows=1 width=45) (actual time=0.006..0.006 rows=1 loops=5,820)

  • Index Cond: (m_product_id = m_storage.m_product_id)
13. 11.640 58.200 ↑ 1.0 1 5,820

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

14. 23.280 23.280 ↑ 1.0 1 5,820

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

  • Index Cond: (m_attributesetinstance_id = m_storage.m_attributesetinstance_id)
15. 23.280 23.280 ↑ 1.0 1 5,820

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,820)

  • Index Cond: (m_lot_id = m_attributesetinstance.m_lot_id)
16. 11.640 11.640 ↑ 1.0 1 5,820

Index Scan using m_product_id_idx on m_product m_product_1 (cost=0.29..8.30 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=5,820)

  • Index Cond: (m_product_id = m_storage.m_product_id)
17. 11.640 11.640 ↑ 1.0 1 5,820

Index Scan using m_product_id_idx on m_product m_product_2 (cost=0.29..8.30 rows=1 width=31) (actual time=0.002..0.002 rows=1 loops=5,820)

  • Index Cond: (m_product_id = m_storage.m_product_id)
18. 23.280 34.920 ↑ 1.0 1 5,820

Seq Scan on m_product_category (cost=8.30..9.39 rows=1 width=32) (actual time=0.005..0.006 rows=1 loops=5,820)

  • Filter: (m_product_category_id = $7)
  • Rows Removed by Filter: 6
19.          

Initplan (for Seq Scan)

20. 11.640 11.640 ↑ 1.0 1 5,820

Index Scan using m_product_id_idx on m_product m_product_3 (cost=0.29..8.30 rows=1 width=7) (actual time=0.002..0.002 rows=1 loops=5,820)

  • Index Cond: (m_product_id = m_storage.m_product_id)
21. 0.000 133.860 ↑ 20.0 1 5,820

Unique (cost=81.08..81.18 rows=20 width=32) (actual time=0.021..0.023 rows=1 loops=5,820)

22.          

Initplan (for Unique)

23. 17.460 17.460 ↑ 1.0 1 5,820

Index Scan using m_product_id_idx on m_product m_product_4 (cost=0.29..8.30 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=5,820)

  • Index Cond: (m_product_id = m_storage.m_product_id)
24. 40.740 122.220 ↑ 2.2 9 5,820

Sort (cost=72.77..72.82 rows=20 width=32) (actual time=0.020..0.021 rows=9 loops=5,820)

  • Sort Key: ((((ad_ref_list.value)::text || ' - '::text) || (ad_ref_list.name)::text))
  • Sort Method: quicksort Memory: 25kB
25. 46.560 81.480 ↑ 2.2 9 5,820

Bitmap Heap Scan on ad_ref_list (cost=4.49..72.34 rows=20 width=32) (actual time=0.012..0.014 rows=9 loops=5,820)

  • Recheck Cond: (((value)::text = ($9)::text) AND (ad_reference_id = '1000188'::numeric))
  • Heap Blocks: exact=8,102
26. 34.920 34.920 ↑ 2.2 9 5,820

Bitmap Index Scan on ad_ref_list_value (cost=0.00..4.49 rows=20 width=0) (actual time=0.006..0.006 rows=9 loops=5,820)

  • Index Cond: (((value)::text = ($9)::text) AND (ad_reference_id = '1000188'::numeric))
27. 0.000 52.380 ↑ 1.0 1 5,820

Unique (cost=16.62..16.63 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=5,820)

28.          

Initplan (for Unique)

29. 11.640 11.640 ↑ 1.0 1 5,820

Index Scan using m_product_id_idx on m_product m_product_5 (cost=0.29..8.30 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=5,820)

  • Index Cond: (m_product_id = m_storage.m_product_id)
30. 23.280 46.560 ↑ 1.0 1 5,820

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

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

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,820)

  • Index Cond: (((value)::text = ($11)::text) AND (ad_reference_id = '1000190'::numeric))
32. 0.000 58.200 ↑ 1.0 1 5,820

Unique (cost=16.62..16.63 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=5,820)

33.          

Initplan (for Unique)

34. 11.640 11.640 ↑ 1.0 1 5,820

Index Scan using m_product_id_idx on m_product m_product_6 (cost=0.29..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=5,820)

  • Index Cond: (m_product_id = m_storage.m_product_id)
35. 23.280 52.380 ↑ 1.0 1 5,820

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

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

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,820)

  • Index Cond: (((value)::text = ($13)::text) AND (ad_reference_id = '1000189'::numeric))
37. 34.920 64.020 ↑ 1.0 1 5,820

Hash Join (cost=8.31..9.49 rows=1 width=3) (actual time=0.010..0.011 rows=1 loops=5,820)

  • Hash Cond: (c_uom.c_uom_id = m_product_7.c_uom_id)
38. 11.640 11.640 ↑ 1.0 13 5,820

Seq Scan on c_uom (cost=0.00..1.13 rows=13 width=10) (actual time=0.001..0.002 rows=13 loops=5,820)

39. 5.820 17.460 ↑ 1.0 1 5,820

Hash (cost=8.30..8.30 rows=1 width=7) (actual time=0.003..0.003 rows=1 loops=5,820)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
40. 11.640 11.640 ↑ 1.0 1 5,820

Index Scan using m_product_id_idx on m_product m_product_7 (cost=0.29..8.30 rows=1 width=7) (actual time=0.002..0.002 rows=1 loops=5,820)

  • Index Cond: (m_product_id = m_storage.m_product_id)
41. 17.460 151.320 ↑ 1.0 1 5,820

Aggregate (cost=43.09..43.10 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=5,820)

42. 87.300 133.860 ↓ 4.0 4 5,820

Bitmap Heap Scan on m_transaction tr (cost=4.37..43.08 rows=1 width=8) (actual time=0.016..0.023 rows=4 loops=5,820)

  • Recheck Cond: (m_product_id = m_storage.m_product_id)
  • Filter: (m_locator_id = m_locator.m_locator_id)
  • Rows Removed by Filter: 27
  • Heap Blocks: exact=46,873
43. 46.560 46.560 ↓ 3.2 35 5,820

Bitmap Index Scan on m_transsaction_product (cost=0.00..4.37 rows=11 width=0) (actual time=0.008..0.008 rows=35 loops=5,820)

  • Index Cond: (m_product_id = m_storage.m_product_id)
Planning time : 4.450 ms
Execution time : 24,815.243 ms