just by removing the order by co_name reduces the query time dramatically from ~ 9 sec to 63 ms. Can anyone please help.
The 63 ms query result is probably useless since it returns a limit of 25 rows from an unordered result. It is not surprising that this is fast.
The pain is here:
Index Scan using profile_master_co_name on profile_master b (cost=0.00..1125295.59 rows=6968 width=25) (actual time=0.097..9193.154 rows=2212 loops=1) Filter: ((co_name IS NOT NULL) AND ((co_name_vec)::tsvector @@ to_tsquery('manufacturer'::text)))
It looks like seq_scans are disabled, since the index scan has only a filter expression but not an index cond.
seq_scans is NOT explicitly disabled. The two queries just differed in the order by clause.