pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: CS DBA <cs_dba@consistentstate.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Poor performance on a simple join
Date: Wed, 02 Nov 2011 14:21:18 -0600
Message-ID: <4EB1A63E.80101@consistentstate.com> (raw )
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
---------------------------------------------
view thread (5+ messages) latest in thread
Message-ID: <4EB1A63E.80101@consistentstate.com>
Permalink: ../4EB1A63E.80101@consistentstate.com/
Also on: postgresql.org/message-id/4EB1A63E.80101@consistentstate.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cs_dba@consistentstate.com
Subject: Re: Poor performance on a simple join
In-Reply-To: <4EB1A63E.80101@consistentstate.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox