What happens if you do this:
SET cpu_tuple_cost TO '0.5';
SET cpu_index_tuple_cost TO '0.5';
EXPLAIN ANALYZE 8.3 query....
Next try this:
SET cpu_tuple_cost TO '0.5';
SET cpu_index_tuple_cost TO '0.5';
SET seq_page_cost TO '4.0';
SET random_page_cost TO '1.0';
EXPLAIN ANALYZE 8.3 query....
And then this:
SET cpu_tuple_cost TO '0.5';
SET cpu_index_tuple_cost TO '0.5';
SET seq_page_cost TO '4.0';
SET random_page_cost TO '1.0';
SET effective_cache_size TO '3000MB';
EXPLAIN ANALYZE 8.3 query....
These three are pretty much the same in terms of performance. I stayed with the first one (cpu_tuple_cost = 0.5 and cpu_index_tuple_cost = 0.5). As shown earlier, it gives a result similar or slightly better than 8.2.12 in terms of performance and response time. The explain analyze shows that the query no longer causes postgreSQL to uses hashes, but indexes instead which boosted the performance of the query from ~1200 ms to ~600 ms.
Thank you everyone for all the help and feedback on this issue.
