On 11/02/2011 02:45 PM, Scott Marlowe wrote:
On Wed, Nov 2, 2011 at 2:21 PM, CS DBA<cs_dba@xxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
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
---------------------------------------------
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
