explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TMfM

Settings
# exclusive inclusive rows x rows loops node
1. 2,814.002 183,224.614 ↓ 191.7 34,323 1

GroupAggregate (cost=4,270.66..4,281.85 rows=179 width=811) (actual time=169,927.200..183,224.614 rows=34,323 loops=1)

  • Filter: (sum(t.stock) <> 0::numeric)
  • Rows Removed by Filter: 23516
2. 134,087.936 180,410.612 ↓ 15,816.5 2,831,157 1

Sort (cost=4,270.66..4,271.11 rows=179 width=811) (actual time=169,925.541..180,410.612 rows=2,831,157 loops=1)

  • Sort Key: w.name, pc.name, p.searchkey, t.m_product_id, t.m_warehouse_id, t.c_uom_id, t.c_currency_id, t.ad_org_id, p.name, uom.name, cr.cost
  • Sort Method: external merge Disk: 622912kB
3. 2,061.296 46,322.676 ↓ 15,816.5 2,831,157 1

Nested Loop Left Join (cost=7.89..4,263.96 rows=179 width=811) (actual time=0.346..46,322.676 rows=2,831,157 loops=1)

4. 1,225.463 27,274.438 ↓ 15,816.5 2,831,157 1

Nested Loop (cost=7.48..4,058.88 rows=179 width=805) (actual time=0.326..27,274.438 rows=2,831,157 loops=1)

5. 2,434.234 17,555.504 ↓ 15,816.5 2,831,157 1

Nested Loop (cost=7.20..4,003.83 rows=179 width=824) (actual time=0.316..17,555.504 rows=2,831,157 loops=1)

6. 1,439.469 3,796.642 ↓ 15,816.5 2,831,157 1

Hash Join (cost=6.79..3,847.00 rows=179 width=750) (actual time=0.300..3,796.642 rows=2,831,157 loops=1)

  • Hash Cond: ((t.c_uom_id)::text = (uom.r_uom_id)::text)
7. 873.610 2,357.146 ↓ 3,080.7 2,831,157 1

Nested Loop (cost=4.91..3,839.88 rows=919 width=612) (actual time=0.265..2,357.146 rows=2,831,157 loops=1)

8. 0.021 0.021 ↑ 1.0 33 1

Seq Scan on r_warehouse w (cost=0.00..1.33 rows=33 width=220) (actual time=0.002..0.021 rows=33 loops=1)

9. 913.011 1,483.515 ↓ 3,064.0 85,793 33

Bitmap Heap Scan on m_trx_per_warehouse t (cost=4.91..116.04 rows=28 width=474) (actual time=18.099..44.955 rows=85,793 loops=33)

  • Recheck Cond: (((ad_org_id)::text = 'E1B3C6BCD75341C7BD474AFFEB442302'::text) AND ((m_warehouse_id)::text = (w.r_warehouse_id)::text) AND (movementdate <= to_date('05-06-2019'::text, 'DD-MM-YYYY'::text)))
10. 570.504 570.504 ↓ 3,064.0 85,793 33

Bitmap Index Scan on m_trx_per_warehouse_orgwhsdate (cost=0.00..4.91 rows=28 width=0) (actual time=17.288..17.288 rows=85,793 loops=33)

  • Index Cond: (((ad_org_id)::text = 'E1B3C6BCD75341C7BD474AFFEB442302'::text) AND ((m_warehouse_id)::text = (w.r_warehouse_id)::text) AND (movementdate <= to_date('05-06-2019'::text, 'DD-MM-YYYY'::text)))
11. 0.010 0.027 ↑ 1.0 39 1

Hash (cost=1.39..1.39 rows=39 width=220) (actual time=0.027..0.027 rows=39 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
12. 0.017 0.017 ↑ 1.0 39 1

Seq Scan on r_uom uom (cost=0.00..1.39 rows=39 width=220) (actual time=0.004..0.017 rows=39 loops=1)

13. 11,324.628 11,324.628 ↑ 1.0 1 2,831,157

Index Scan using r_product_pk on r_product p (cost=0.41..0.87 rows=1 width=107) (actual time=0.004..0.004 rows=1 loops=2,831,157)

  • Index Cond: ((r_product_id)::text = (t.m_product_id)::text)
14. 8,493.471 8,493.471 ↑ 1.0 1 2,831,157

Index Scan using r_productcategory_pk on r_productcategory pc (cost=0.28..0.30 rows=1 width=47) (actual time=0.003..0.003 rows=1 loops=2,831,157)

  • Index Cond: ((r_productcategory_id)::text = (p.productcategory_id)::text)
15. 16,986.942 16,986.942 ↑ 1.0 1 2,831,157

Index Scan using m_costing_last_prodorg on m_costing_last cr (cost=0.41..1.14 rows=1 width=107) (actual time=0.006..0.006 rows=1 loops=2,831,157)

  • Index Cond: (((t.m_product_id)::text = (m_product_id)::text) AND ((t.ad_org_id)::text = (ad_org_id)::text) AND ((ad_org_id)::text = 'E1B3C6BCD75341C7BD474AFFEB442302'::text))
  • Filter: (((costtype)::text = ANY ('{AVA,AV}'::text[])) AND ((((warehouse_dimension)::text = 'N'::text) AND (m_warehouse_id IS NULL)) OR (((warehouse_dimension)::text = 'Y'::text) AND ((m_warehouse_id)::text = (t.m_warehouse_id)::text))))
  • Rows Removed by Filter: 0