Re: PostgreSQL Query Speed Issues

On Thu, Feb 21, 2013 at 3:56 PM, Joseph Pravato
<joseph.pravato@nomagic.com> wrote:
> NOTE: All database tests were done without changing or updating any settings after install.

The defaults are sub-optimal. You really do need to tune them to the
server in question.

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

After loading this data, did you run an ANALYZE?

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

Table definitions would probably help. You might be missing indexes.

>
> 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

disk sorts imply work_mem isn't big enough.

>               ->  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) 

This should probably be using an index

--
Douglas J Hunley (doug.hunley@gmail.com)
Twitter: @hunleyd                                               Web:
douglasjhunley.com
G+: http://goo.gl/sajR3