Re: Poor performance on a simple join

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
>



--
To understand recursion, one must first understand recursion.