explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hQko

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.615 3,969.716 ↓ 7.4 287 1

Hash Join (cost=190.98..1,834.34 rows=39 width=3,002) (actual time=181.442..3,969.716 rows=287 loops=1)

  • Hash Cond: (t.ad_org_id = org.ad_org_id)
2. 183.330 3,968.864 ↓ 7.4 287 1

Hash Join (cost=189.33..1,832.15 rows=39 width=3,009) (actual time=181.163..3,968.864 rows=287 loops=1)

  • Hash Cond: (p.m_product_id = pp.m_product_id)
  • Join Filter: ((t.movementdate >= plv.validfrom) AND (t.movementdate < COALESCE((((SubPlan 1))::timestamp without time zone)::timestamp with time zone, now())))
  • Rows Removed by Join Filter: 115698
3. 16.878 2,103.932 ↓ 3.4 19,228 1

Nested Loop (cost=159.79..1,772.55 rows=5,733 width=3,035) (actual time=4.957..2,103.932 rows=19,228 loops=1)

4. 0.048 4.198 ↑ 4.1 8 1

Nested Loop (cost=159.36..510.08 rows=33 width=3,014) (actual time=3.228..4.198 rows=8 loops=1)

5. 0.179 2.950 ↑ 4.1 8 1

Bitmap Heap Scan on c_bpartner_product bp (cost=159.08..263.69 rows=33 width=12) (actual time=2.935..2.950 rows=8 loops=1)

  • Recheck Cond: (c_bpartner_id = '1001834'::numeric)
  • Filter: (isexclusiveconsignment ~~ 'Y'::text)
  • Heap Blocks: exact=2
6. 0.004 2.771 ↓ 0.0 0 1

BitmapAnd (cost=159.08..159.08 rows=33 width=0) (actual time=2.771..2.771 rows=0 loops=1)

7. 0.360 0.360 ↑ 14.8 9 1

Bitmap Index Scan on c_bpartner_product_isexclusiveconsignment_idx (cost=0.00..5.28 rows=133 width=0) (actual time=0.360..0.360 rows=9 loops=1)

  • Index Cond: (isexclusiveconsignment = 'Y'::bpchar)
8. 2.407 2.407 ↓ 1.0 6,567 1

Bitmap Index Scan on c_bpartner_product_pkey (cost=0.00..153.53 rows=6,565 width=0) (actual time=2.407..2.407 rows=6,567 loops=1)

  • Index Cond: (c_bpartner_id = '1001834'::numeric)
9. 1.200 1.200 ↑ 1.0 1 8

Index Scan using m_product_pkey on m_product p (cost=0.29..7.46 rows=1 width=3,002) (actual time=0.150..0.150 rows=1 loops=8)

  • Index Cond: (m_product_id = bp.m_product_id)
10. 2,082.856 2,082.856 ↓ 3.7 2,404 8

Index Scan using m_transaction_product_m_product_id_idx on m_transaction t (cost=0.43..31.81 rows=645 width=21) (actual time=0.339..260.357 rows=2,404 loops=8)

  • Index Cond: (m_product_id = p.m_product_id)
  • Filter: (movementtype ~~ 'C-'::text)
  • Rows Removed by Filter: 151
11. 11.300 160.986 ↓ 9,761.5 19,523 1

Hash (cost=29.51..29.51 rows=2 width=26) (actual time=160.986..160.986 rows=19,523 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1458kB
12. 4.834 149.686 ↓ 9,761.5 19,523 1

Nested Loop (cost=5.13..29.51 rows=2 width=26) (actual time=0.889..149.686 rows=19,523 loops=1)

13. 0.056 1.167 ↓ 45.0 45 1

Nested Loop (cost=4.71..18.92 rows=1 width=26) (actual time=0.698..1.167 rows=45 loops=1)

14. 0.657 0.657 ↑ 1.0 1 1

Index Scan using c_bpartner_pkey on c_bpartner cb (cost=0.42..8.44 rows=1 width=11) (actual time=0.655..0.657 rows=1 loops=1)

  • Index Cond: (c_bpartner_id = '1001834'::numeric)
15. 0.427 0.454 ↓ 22.5 45 1

Bitmap Heap Scan on m_pricelist_version plv (cost=4.29..10.46 rows=2 width=20) (actual time=0.037..0.454 rows=45 loops=1)

  • Recheck Cond: (m_pricelist_id = cb.po_pricelist_id)
  • Heap Blocks: exact=16
16. 0.027 0.027 ↓ 22.5 45 1

Bitmap Index Scan on m_pricelist_version_validfrom (cost=0.00..4.29 rows=2 width=0) (actual time=0.027..0.027 rows=45 loops=1)

  • Index Cond: (m_pricelist_id = cb.po_pricelist_id)
17. 143.685 143.685 ↓ 5.5 434 45

Index Only Scan using m_productprice_unique_product_id on m_productprice pp (cost=0.42..9.80 rows=79 width=12) (actual time=0.180..3.193 rows=434 loops=45)

  • Index Cond: (m_pricelist_version_id = plv.m_pricelist_version_id)
  • Heap Fetches: 19523
18.          

SubPlan (for Hash Join)

19. 89.448 1,520.616 ↑ 1.0 1 89,448

Limit (cost=0.28..8.55 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=89,448)

20. 1,431.168 1,431.168 ↑ 1.0 1 89,448

Index Only Scan using m_pricelist_version_validfrom on m_pricelist_version (cost=0.28..8.55 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=89,448)

  • Index Cond: ((m_pricelist_id = plv.m_pricelist_id) AND (validfrom > plv.validfrom))
  • Heap Fetches: 89448
21. 0.016 0.237 ↑ 1.0 29 1

Hash (cost=1.29..1.29 rows=29 width=16) (actual time=0.237..0.237 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
22. 0.221 0.221 ↑ 1.0 29 1

Seq Scan on ad_org org (cost=0.00..1.29 rows=29 width=16) (actual time=0.219..0.221 rows=29 loops=1)

Planning time : 60.097 ms
Execution time : 3,970.161 ms