Hi!
We are running into an interesting issue with PostgreSQL 9.2. Some of the queries are returning in 5-30 minutes where
othersnever 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
querythat we are running into this issue with, hopefully some insight on this issue will help us solve a similar
problemwith 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.708rows=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.348rows=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
specificto PostgreSQL where as other databases seem to do it automatically. Is it possible to change a configuration
optionon the database or does PostgreSQL require hand optimization to all queries to run optimally.
Any help would be greatly appreciated.