PostgreSQL Query Speed Issues

Hi!

We are running into an interesting issue with PostgreSQL 9.2. Some of the queries are returning in 5-30 minutes where others never return at all.

The same queries return in several seconds in other databases we have tested on with the same data. Here is a sample query that we are running into this issue with, hopefully some insight on this issue will help us solve a similar problem with the rest of the queries.

NOTE: All database tests were done without changing or updating any settings after install.

# rows in contact: 574939
# rows in contact_address_map: 574924

Original: NEVER RETURNS (tested to over an hour)

select * from contact where id not in (select contact_id from contact_address_map)

Result from an 'explain analyze':

Since the original query never returned, this was not run.

Optimized for Postgres: RETURNS

select c.*
from contact c
left join CONTACT_ADDRESS_MAP cam on cam.CONTACT_ID = c.id
where cam.ADDRESS_ID is null

Result from an 'explain analyze':

QUERY PLAN
Merge Left Join (cost=83512.87..158059.12 rows=1 width=952) (actual time=5224.171..5224.270 rows=15 loops=1)
Merge Cond: (c.id = cam.contact_id)
Filter: (cam.address_id IS NULL)
Rows Removed by Filter: 574924
-> Index Scan using contact_pkey on contact c (cost=0.00..63048.48 rows=574917 width=952) (actual time=0.009..852.708 rows=574939 loops=1)
-> Materialize (cost=83512.59..86387.17 rows=574917 width=16) (actual time=1407.387..3107.246 rows=574924 loops=1)
-> Sort (cost=83512.59..84949.88 rows=574917 width=16) (actual time=1407.379..2027.946 rows=574924 loops=1)
Sort Key: cam.contact_id
Sort Method: external sort Disk: 14616kB
-> Seq Scan on contact_address_map cam (cost=0.00..8857.17 rows=574917 width=16) (actual time=0.018..578.348 rows=574924 loops=1)
Total runtime: 5228.459 ms

We have done some investigation online and it looks like many of these speed issues require hand optimizing each query specific to PostgreSQL where as other databases seem to do it automatically. Is it possible to change a configuration option on the database or does PostgreSQL require hand optimization to all queries to run optimally.

Any help would be greatly appreciated.