explain.depesz.com

PostgreSQL's explain analyze made readable

Result: L2np

Settings
# exclusive inclusive rows x rows loops node
1. 415.920 7,472.216 ↓ 1,906.8 34,323 1

Sort (cost=18,450.37..18,450.42 rows=18 width=483) (actual time=7,433.047..7,472.216 rows=34,323 loops=1)

  • Sort Key: w.name, pc.name, p.searchkey
  • Sort Method: external merge Disk: 4704kB
2. 41.295 7,056.296 ↓ 1,906.8 34,323 1

Nested Loop Left Join (cost=18,257.91..18,450.00 rows=18 width=483) (actual time=6,477.651..7,056.296 rows=34,323 loops=1)

3. 1.323 6,809.063 ↓ 1,906.8 34,323 1

Nested Loop (cost=18,257.50..18,352.60 rows=18 width=723) (actual time=6,477.629..6,809.063 rows=34,323 loops=1)

4. 31.170 6,704.771 ↓ 1,906.8 34,323 1

Nested Loop (cost=18,257.23..18,347.06 rows=18 width=742) (actual time=6,477.620..6,704.771 rows=34,323 loops=1)

5. 11.582 6,536.309 ↓ 1,906.8 34,323 1

Hash Join (cost=18,256.81..18,272.91 rows=18 width=668) (actual time=6,477.603..6,536.309 rows=34,323 loops=1)

  • Hash Cond: ((t.c_uom_id)::text = (uom.r_uom_id)::text)
6. 13.476 6,524.703 ↓ 373.1 34,323 1

Hash Join (cost=18,254.94..18,270.50 rows=92 width=612) (actual time=6,477.574..6,524.703 rows=34,323 loops=1)

  • Hash Cond: ((t.m_warehouse_id)::text = (w.r_warehouse_id)::text)
7. 2,621.104 6,511.216 ↓ 61.5 34,323 1

HashAggregate (cost=18,253.19..18,260.17 rows=558 width=474) (actual time=6,477.557..6,511.216 rows=34,323 loops=1)

  • Filter: (sum(t.stock) <> 0::numeric)
  • Rows Removed by Filter: 23516
8. 2,881.347 3,890.112 ↓ 508.2 2,831,157 1

Bitmap Heap Scan on m_trx_per_warehouse t (cost=245.66..18,141.77 rows=5,571 width=474) (actual time=1,019.064..3,890.112 rows=2,831,157 loops=1)

  • Recheck Cond: (((ad_org_id)::text = 'E1B3C6BCD75341C7BD474AFFEB442302'::text) AND (movementdate <= to_date('05-06-2019'::text, 'DD-MM-YYYY'::text)))
9. 1,008.765 1,008.765 ↓ 508.2 2,831,157 1

Bitmap Index Scan on m_trx_per_warehouse_orgdate (cost=0.00..244.27 rows=5,571 width=0) (actual time=1,008.765..1,008.765 rows=2,831,157 loops=1)

  • Index Cond: (((ad_org_id)::text = 'E1B3C6BCD75341C7BD474AFFEB442302'::text) AND (movementdate <= to_date('05-06-2019'::text, 'DD-MM-YYYY'::text)))
10. 0.006 0.011 ↑ 1.0 33 1

Hash (cost=1.33..1.33 rows=33 width=220) (actual time=0.011..0.011 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
11. 0.005 0.005 ↑ 1.0 33 1

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

12. 0.012 0.024 ↑ 1.0 39 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
13. 0.012 0.012 ↑ 1.0 39 1

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

14. 137.292 137.292 ↑ 1.0 1 34,323

Index Scan using r_product_pk on r_product p (cost=0.41..4.11 rows=1 width=107) (actual time=0.004..0.004 rows=1 loops=34,323)

  • Index Cond: ((r_product_id)::text = (t.m_product_id)::text)
15. 102.969 102.969 ↑ 1.0 1 34,323

Index Scan using r_productcategory_pk on r_productcategory pc (cost=0.28..0.30 rows=1 width=47) (actual time=0.002..0.003 rows=1 loops=34,323)

  • Index Cond: ((r_productcategory_id)::text = (p.productcategory_id)::text)
16. 205.938 205.938 ↑ 1.0 1 34,323

Index Scan using m_costing_last_prodorg on m_costing_last cr (cost=0.41..5.40 rows=1 width=107) (actual time=0.006..0.006 rows=1 loops=34,323)

  • Index Cond: (((t.m_product_id)::text = (m_product_id)::text) AND ((t.ad_org_id)::text = (ad_org_id)::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
Total runtime : 7,474.354 ms