explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qgbD

Settings
# exclusive inclusive rows x rows loops node
1. 4,973.720 51,692.987 ↓ 191.7 34,323 1

HashAggregate (cost=4,271.57..4,274.70 rows=179 width=811) (actual time=51,650.263..51,692.987 rows=34,323 loops=1)

  • Filter: (sum(t.stock) <> 0::numeric)
  • Rows Removed by Filter: 23516
2. 1,783.360 46,719.267 ↓ 15,816.5 2,831,157 1

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

3. 1,561.399 27,948.965 ↓ 15,816.5 2,831,157 1

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

4. 2,444.376 17,894.095 ↓ 15,816.5 2,831,157 1

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

5. 1,123.022 4,125.091 ↓ 15,816.5 2,831,157 1

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

  • Hash Cond: ((t.c_uom_id)::text = (uom.r_uom_id)::text)
6. 731.033 3,002.042 ↓ 3,080.7 2,831,157 1

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

7. 0.015 0.015 ↑ 1.0 33 1

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

8. 1,617.000 2,270.994 ↓ 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=20.572..68.818 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)))
9. 653.994 653.994 ↓ 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=19.818..19.818 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)))
10. 0.017 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
11. 0.010 0.010 ↑ 1.0 39 1

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

12. 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)
13. 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)
14. 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