QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------- Sort (cost=3979787.07..3980005.96 rows=87555 width=891) (actual time=96252.680..96252.681 rows=3 loops=1) Sort Key: l.line Sort Method: quicksort Memory: 25kB -> Hash Right Join (cost=2061843.15..3952442.08 rows=87555 width=891) (actual time=96251.416..96252.652 rows=3 loops=1) Hash Cond: ((il.c_orderline_id)::text = (l.c_orderline_id)::text) -> GroupAggregate (cost=2061754.19..3827441.55 rows=5836983 width=190) (actual time=46446.330..95298.559 rows=5492026 loops=1) Group Key: il.m_inoutline_id, il.c_orderline_id, il.movementqty, il.quantityorder, il.m_product_uom_id Filter: ((il.movementqty - sum(COALESCE(mi.qty, '0'::numeric))) <> '0'::numeric) Rows Removed by Filter: 341827 -> Sort (cost=2061754.19..2076346.65 rows=5836983 width=190) (actual time=46446.216..61119.425 rows=5835586 loops=1) Sort Key: il.m_inoutline_id, il.c_orderline_id, il.movementqty, il.quantityorder, il.m_product_uom_id Sort Method: external merge Disk: 465160kB -> Hash Left Join (cost=24233.12..1090265.69 rows=5836983 width=190) (actual time=303.079..15344.169 rows=5835586 loops=1) Hash Cond: ((il.m_inoutline_id)::text = (mi.m_inoutline_id)::text) -> Merge Join (cost=8.39..799947.39 rows=5836983 width=185) (actual time=0.016..12328.700 rows=5833853 loops=1) Merge Cond: ((io.m_inout_id)::text = (il.m_inout_id)::text) -> Index Scan using m_inout_key on m_inout io (cost=0.43..162901.99 rows=1462950 width=33) (actual time=0.008..2434.400 rows=1413578 loops=1) Filter: (processed = 'Y'::bpchar) Rows Removed by Filter: 1207 -> Index Scan using m_inoutline_inout on m_inoutline il (cost=0.56..560416.13 rows=5841851 width=218) (actual time=0.006..6924.197 rows=5837430 loops=1) Filter: (c_orderline_id IS NOT NULL) Rows Removed by Filter: 582 -> Hash (cost=19933.77..19933.77 rows=343277 width=38) (actual time=301.142..301.142 rows=345976 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 27814kB -> Seq Scan on m_matchinv mi (cost=0.00..19933.77 rows=343277 width=38) (actual time=0.013..194.470 rows=345976 loops=1) -> Hash (cost=88.92..88.92 rows=3 width=712) (actual time=0.896..0.896 rows=3 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Nested Loop (cost=30.66..88.92 rows=3 width=712) (actual time=0.654..0.891 rows=3 loops=1) -> Hash Join (cost=30.24..83.19 rows=3 width=712) (actual time=0.605..0.792 rows=3 loops=1) Hash Cond: ((l.c_uom_id)::text = (uom.c_uom_id)::text) -> Nested Loop (cost=28.28..81.18 rows=3 width=704) (actual time=0.547..0.733 rows=3 loops=1) -> HashAggregate (cost=28.13..76.23 rows=3 width=1091) (actual time=0.531..0.709 rows=3 loops=1) Group Key: l.ad_org_id, l.c_currency_id, l.c_uom_id, l.m_product_id, l.line, l.description, l.c_orderline_id, l.qtyordered, l.quantityorder, l.m_product_uom_id, l.m_attributesetinstance_id, l.taxbaseamt, COALESCE(l.a_asset_id, o.a_asset_id), COALESCE(l.c_project_id, o.c_project_id), COALESCE(l.c_costcenter_id, o.c_costcenter_id), COALESCE(l.user1_id, o.user1_id), COALESCE(l.user2_id, o.user2_id), l.explode -> Nested Loop Left Join (cost=1.54..27.98 rows=3 width=1091) (actual time=0.119..0.238 rows=3 loops=1) -> Nested Loop Left Join (cost=0.99..19.64 rows=3 width=714) (actual time=0.077..0.182 rows=3 loops=1) -> Index Scan using c_orderline_key on c_orderline l (cost=0.56..8.33 rows=3 width=709) (actual time=0.039..0.091 rows=3 loops=1) Index Cond: ((c_orderline_id)::text = ANY ('{632B1284EB114531A8FEAEACE488C0F3,C63CC19F80564EFFAE6901A556906DB5,E48F5E01236B4C52892E05E8EDE7B01B}'::text[])) -> Index Scan using em_cdms_matchpo_orderline on m_matchpo mp (cost=0.42..3.76 rows=1 width=38) (actual time=0.027..0.027 rows=0 loops=3) Index Cond: ((l.c_orderline_id)::text = (c_orderline_id)::text) Filter: (c_invoiceline_id IS NOT NULL) -> Index Scan using c_order_key on c_order o (cost=0.55..2.77 rows=1 width=443) (actual time=0.016..0.016 rows=1 loops=3) Index Cond: ((l.c_order_id)::text = (c_order_id)::text) SubPlan 1 -> HashAggregate (cost=7.87..7.88 rows=1 width=42) (actual time=0.067..0.067 rows=1 loops=3) Group Key: ci.c_orderline_id, co.qtyordered -> Nested Loop (cost=4.52..7.86 rows=1 width=42) (actual time=0.063..0.064 rows=1 loops=3) -> Index Scan using c_orderline_key on c_orderline co (cost=0.56..2.78 rows=1 width=37) (actual time=0.008..0.008 rows=1 loops=3) Index Cond: ((c_orderline_id)::text = (l.c_orderline_id)::text) -> Bitmap Heap Scan on c_invoiceline ci (cost=3.96..5.07 rows=1 width=38) (actual time=0.052..0.053 rows=1 loops=3) Recheck Cond: (((c_orderline_id)::text = (l.c_orderline_id)::text) AND ((c_invoice_id)::text = '1660DF7FB4B94F818C45C3FC6CEE81B5'::text)) Heap Blocks: exact=3 -> BitmapAnd (cost=3.96..3.96 rows=1 width=0) (actual time=0.047..0.047 rows=0 loops=3) -> Bitmap Index Scan on c_invoiceline_orderline (cost=0.00..1.71 rows=7 width=0) (actual time=0.028..0.028 rows=1 loops=3) Index Cond: ((c_orderline_id)::text = (l.c_orderline_id)::text) -> Bitmap Index Scan on c_invoiceline_invoice (cost=0.00..2.00 rows=45 width=0) (actual time=0.018..0.018 rows=3 loops=3) Index Cond: ((c_invoice_id)::text = '1660DF7FB4B94F818C45C3FC6CEE81B5'::text) SubPlan 2 -> HashAggregate (cost=7.87..7.88 rows=1 width=42) (actual time=0.027..0.027 rows=1 loops=3) Group Key: ci_1.c_orderline_id, co_1.qtyordered -> Nested Loop (cost=4.52..7.86 rows=1 width=42) (actual time=0.024..0.025 rows=1 loops=3) -> Index Scan using c_orderline_key on c_orderline co_1 (cost=0.56..2.78 rows=1 width=37) (actual time=0.006..0.006 rows=1 loops=3) Index Cond: ((c_orderline_id)::text = (l.c_orderline_id)::text) -> Bitmap Heap Scan on c_invoiceline ci_1 (cost=3.96..5.07 rows=1 width=38) (actual time=0.015..0.015 rows=1 loops=3) Recheck Cond: (((c_orderline_id)::text = (l.c_orderline_id)::text) AND ((c_invoice_id)::text = '1660DF7FB4B94F818C45C3FC6CEE81B5'::text)) Heap Blocks: exact=3 -> BitmapAnd (cost=3.96..3.96 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=3) -> Bitmap Index Scan on c_invoiceline_orderline (cost=0.00..1.71 rows=7 width=0) (actual time=0.006..0.006 rows=1 loops=3) Index Cond: ((c_orderline_id)::text = (l.c_orderline_id)::text) -> Bitmap Index Scan on c_invoiceline_invoice (cost=0.00..2.00 rows=45 width=0) (actual time=0.007..0.007 rows=3 loops=3) Index Cond: ((c_invoice_id)::text = '1660DF7FB4B94F818C45C3FC6CEE81B5'::text) -> Index Scan using c_currency_key on c_currency cur (cost=0.14..1.63 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=3) Index Cond: ((c_currency_id)::text = (l.c_currency_id)::text) -> Hash (cost=1.43..1.43 rows=43 width=34) (actual time=0.039..0.039 rows=43 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 11kB -> Seq Scan on c_uom uom (cost=0.00..1.43 rows=43 width=34) (actual time=0.009..0.020 rows=43 loops=1) -> Index Only Scan using m_product_key on m_product p (cost=0.42..1.90 rows=1 width=33) (actual time=0.030..0.031 rows=1 loops=3) Index Cond: (m_product_id = (l.m_product_id)::text) Heap Fetches: 0 Planning time: 12.625 ms Execution time: 96308.536 ms (80 rows) (END)