Re: Poor performance on a simple join

On Wed, Nov 2, 2011 at 3:53 PM, CS DBA <cs_dba@consistentstate.com> wrote:
> On 11/02/2011 02:45 PM, Scott Marlowe wrote:
>>
>> On Wed, Nov 2, 2011 at 2:21 PM, CS DBA<cs_dba@consistentstate.com>  wrote:
>>>
>>> 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
>>
>> How long should it take?  300 milliseconds is fairly fast for mushing
>> 129k rows up against 26k rows and getting 12k rows back.  That's 40
>> rows / millisecond, which isn't too bad really.
>>
>>
>> What pg version are you running?  What evidence do you have that this
>> is slow? i.e. other machines you've run it on where it's faster?  What
>> hardware (CPU, RAM, IO subsystem, OS) Are you running on?
>>
>>>
>>> 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
>>> ---------------------------------------------
>>>
>>>
>>> --
>>> Sent via pgsql-performance mailing list
>>> (pgsql-performance@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-performance
>>
> Agreed. but it's not fast enough for the client.  I think we're going to
> look at creating an aggregate table or maybe partitioning

Take a look here:
http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views