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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster