Sorry Igor - yes wrong plan. Here's the new one ... (running a wee bit slower this morning - still 20x faster that before however)
http://explain.depesz.com/s/64YM 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)
