Sorry Igor - yes wrong plan.
Here's the new one ...
(running a wee bit slower this morning - still 20x faster that before however)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=70661.35..70661.36 rows=1 width=24) (actual time=1305.098..1326.956 rows=52624 loops=1)
Buffers: shared hit=232615 read=3871 dirtied=387
-> Nested Loop (cost=1.29..70661.34 rows=1 width=24) (actual time=6.307..1242.567 rows=53725 loops=1)
Buffers: shared hit=232615 read=3871 dirtied=387
-> Index Scan using branch_po_state_idx on branch_purchase_order o (cost=0.42..822.22 rows=1768 width=17) (actual time=0.042..6.001 rows=1861 loops=1)
Index Cond: ((po_state)::text = 'PLACED'::text)
Filter: ((supplier)::text = 'XX'::text)
Rows Removed by Filter: 3016
Buffers: shared hit=2218
-> Nested Loop (cost=0.87..39.49 rows=1 width=36) (actual time=0.151..0.651 rows=29 loops=1861)
Buffers: shared hit=230397 read=3871 dirtied=387
-> Index Scan using ssales_ib_replace_order_no on stocksales_ib ss (cost=0.44..33.59 rows=1 width=31) (actual time=0.093..0.401 rows=29 loops=1861)
Index Cond: (replace((order_no)::text, ' '::text, ''::text) = ((o.branch_code)::text || (o.po_number)::text))
Filter: ((o.supplier)::bpchar = branch_code)
Buffers: shared hit=13225 read=2994
-> Index Only Scan using branch_purchase_order_products_po_id_product_code_idx on branch_purchase_order_products p (cost=0.43..5.90 rows=1 width=12) (actual time=0.006..0.007 rows=1 loops=54396)
Index Cond: ((po_id = o.po_id) AND (product_code = (ss.product_code)::text))
Heap Fetches: 54475
Buffers: shared hit=217172 read=877 dirtied=387
Total runtime: 1336.253 ms
(20 rows)
