Message-ID: <4EB1A63E.80101@consistentstate.com>
Date: Wed, 02 Nov 2011 14:21:18 -0600
From: CS DBA <cs_dba@consistentstate.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.23) Gecko/20110928 Fedora/3.1.15-1.fc14 Lightning/1.0b2
	Thunderbird/3.1.15
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Poor performance on a simple join
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi All;

The below contab2 table conmtains ~400,000 rows. This query should not 
take this long.  We've tweaked work_mem up to 50MB, ensured that the 
appropriate indexes are in place, etc...

Thoughts?

Thanks in advance


Explain analyze:
SELECT contab2.contacts_tab
FROM contab2
INNER JOIN sctab
     ON sctab.id = contab2.to_service_id
         AND sctab.type IN ('FService', 'FqService', 'LService', 
'TService')
WHERE contab2.from_contact_id=402513;
                                                                         
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------- 

  Hash Join  (cost=16904.28..25004.54 rows=26852 width=4) (actual 
time=302.621..371.599 rows=12384 loops=1)
    Hash Cond: (contab2.to_service_id = sctab.id)
    ->  Bitmap Heap Scan on contab2  (cost=1036.49..8566.14 rows=26852 
width=20) (actual time=5.191..32.701 rows=26963 loops=1)
          Recheck Cond: (from_contact_id = 402513)
          ->  Bitmap Index Scan on index_contab2_on_from_user_id  
(cost=0.00..1029.78 rows=26852 width=0) (actual time=4.779..4.779 
rows=26963 loops=1)
                Index Cond: (from_contact_id = 402513)
    ->  Hash  (cost=14445.19..14445.19 rows=113808 width=16) (actual 
time=297.332..297.332 rows=129945 loops=1)
          Buckets: 16384  Batches: 1  Memory Usage: 6092kB
          ->  Bitmap Heap Scan on sctab  (cost=2447.07..14445.19 
rows=113808 width=16) (actual time=29.480..187.166 rows=129945 loops=1)
                Recheck Cond: ((type)::text = ANY 
('{FService,FqService,LService,TService}'::text[]))
                ->  Bitmap Index Scan on index_sctab_on_type  
(cost=0.00..2418.62 rows=113808 width=0) (actual time=27.713..27.713 
rows=130376 loops=1)
                      Index Cond: ((type)::text = ANY 
('{FService,FqService,LService,TService}'::text[]))
  Total runtime: 382.514 ms
(13 rows)

-- 
---------------------------------------------
Kevin Kempter       -       Constent State
A PostgreSQL Professional Services Company
           www.consistentstate.com
---------------------------------------------