Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 5B68AB5DD93 for ; Wed, 2 Nov 2011 18:59:38 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 82964-06 for ; Wed, 2 Nov 2011 21:59:31 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-yw0-f46.google.com (mail-yw0-f46.google.com [209.85.213.46]) by mail.postgresql.org (Postfix) with ESMTP id 41B17B5DDA0 for ; Wed, 2 Nov 2011 18:59:31 -0300 (ADT) Received: by ywf7 with SMTP id 7so600887ywf.19 for ; Wed, 02 Nov 2011 14:59:31 -0700 (PDT) Received: by 10.68.26.193 with SMTP id n1mr7048430pbg.125.1320270812057; Wed, 02 Nov 2011 14:53:32 -0700 (PDT) Received: from issac.consistentstate.com (c-67-165-192-103.hsd1.co.comcast.net. [67.165.192.103]) by mx.google.com with ESMTPS id lt8sm7923684pbb.0.2011.11.02.14.53.29 (version=SSLv3 cipher=OTHER); Wed, 02 Nov 2011 14:53:31 -0700 (PDT) Message-ID: <4EB1BBD8.3040307@consistentstate.com> Date: Wed, 02 Nov 2011 15:53:28 -0600 From: CS DBA 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: Scott Marlowe CC: "pgsql-performance@postgresql.org" Subject: Re: Poor performance on a simple join References: <4EB1A63E.80101@consistentstate.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201111/32 X-Sequence-Number: 45543 On 11/02/2011 02:45 PM, Scott Marlowe wrote: > On Wed, Nov 2, 2011 at 2:21 PM, CS DBA 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 -- --------------------------------------------- Kevin Kempter - Constent State A PostgreSQL Professional Services Company www.consistentstate.com ---------------------------------------------