Wrong Query Plan

Below if the query plan that postgres is generating. The troubling part is the
sequential scan on fund_data table. This table has close to million records. It
started doing this from yesterday.

We have added lot of data in allocation_data & transfer_data tables.

If I have just sub query 1 or sub query 2 then it is doing a index scan on
fund_data table but as soon as I add the union it is doing a sequential scan.



EXPLAIN SELECT fund_data.fund_id FROM fund_data WHERE fund_data.fund_id IN
((SELECT allocation_data.fund_id  FROM allocation_data, allocation_lists WHERE
allocation_lists.allocation_id = allocation_data.allocation_id  AND
allocation_lists.account_id=23338) UNION (SELECT transfer_data.target_fund_id as
fund_id FROM transfer_data WHERE transfer_data.account_id=23338));
                                                                           QUERY
PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=97.73..157055.63 rows=138696 width=4)
   Hash Cond: ("outer".fund_id = "inner".fund_id)
   ->  Seq Scan on fund_data  (cost=0.00..123670.96 rows=6379996 width=4)
   ->  Hash  (cost=97.49..97.49 rows=98 width=4)
         ->  HashAggregate  (cost=97.49..97.49 rows=98 width=4)
               ->  Subquery Scan "IN_subquery"  (cost=95.77..97.24 rows=98 width=4)
                     ->  Unique  (cost=95.77..96.26 rows=98 width=4)
                           ->  Sort  (cost=95.77..96.02 rows=98 width=4)
                                 Sort Key: fund_id
                                 ->  Append  (cost=0.00..92.53 rows=98 width=4)
                                       ->  Subquery Scan "*SELECT* 1"
(cost=0.00..45.69 rows=27 width=4)
                                             ->  Nested Loop  (cost=0.00..45.42
rows=27 width=4)
                                                   ->  Index Scan using
m_all_lists_account_id_idx on allocation_lists  (cost=0.00..11.01 rows=7 width=4)
                                                         Index Cond: (account_id
= 23338)
                                                   ->  Index Scan using
m_all_data_all_list_id_idx on allocation_data  (cost=0.00..4.84 rows=6 width=8)
                                                         Index Cond:
("outer".allocation_id = allocation_data.allocation_id)
                                       ->  Subquery Scan "*SELECT* 2"
(cost=0.00..46.84 rows=71 width=4)
                                             ->  Index Scan using
m_trans_data_account_id_idx on transfer_data  (cost=0.00..46.13 rows=71 width=4)
                                                   Index Cond: (account_id = 23338)
(19 rows)

Thanks,
-Prasanth.