PostgreSQL Query Speed Issues - Mailing list pgsql-novice

From Joseph Pravato
Subject PostgreSQL Query Speed Issues
Date
Msg-id 9678018.67.1361479743893.JavaMail.Joey@Goku
Whole thread Raw
Responses Re: PostgreSQL Query Speed Issues
List pgsql-novice
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.


pgsql-novice by date:

Previous
From: Matt Musgrove
Date:
Subject: Re: minimum hardware requirements for small postgres db
Next
From: Douglas J Hunley
Date:
Subject: Re: PostgreSQL Query Speed Issues