Re: Poor performance on a simple join

On 11/02/2011 02:45 PM, Scott Marlowe wrote:
> On Wed, Nov 2, 2011 at 2:21 PM, CS DBA<cs_dba(at)consistentstate(dot)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(at)postgresql(dot)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

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