Db slow down after table partition

From: AI Rumman <rummandba(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Db slow down after table partition
Date: 2010-10-10 09:52:06
Message-ID: AANLkTimEAs7gs0LYFcirMx8CDF9WuswbNhKVTxLQafPn@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I have more than 1300000 records in crm table and I partioned the table with
deleted = 0 key.
It is working fine except that after partioion query is taking more time
than the previous one.
I already set constraint_exclusion = on; My DB version is Postgresql 8.1

I added the explain anayze for both the states.
Any idea please why the delay is being occured.

explain analyze
select *
from crm as c
inner join activity as a on c.crmid = a.activityid
inner join seactivityrel as s on c.crmid= s.crmid
where c.deleted = 0;

Before partiion:

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=116378.17..358658.14 rows=8306416340 width=549) (actual
time=18273.373..18276.638 rows=1 loops=1)
Hash Cond: ("outer".crmid = "inner".activityid)
-> Append (cost=0.00..37825.51 rows=949942 width=329) (actual
time=0.051..5753.293 rows=949941 loops=1)
-> Seq Scan on crm c (cost=0.00..13.25 rows=1 width=280) (actual
time=0.002..0.002 rows=0 loops=1)
Filter: (deleted = 0)
-> Seq Scan on crm_active c (cost=0.00..37812.26 rows=949941 width=329)
(actual time=0.046..3914.645 rows=949941 loops=1)
Filter: (deleted = 0)
-> Hash (cost=72725.10..72725.10 rows=1748826 width=153) (actual
time=8716.413..8716.413 rows=1 loops=1)
-> Merge Join (cost=0.00..72725.10 rows=1748826 width=153) (actual
time=7122.474..8716.314 rows=1 loops=1)
Merge Cond: ("outer".activityid = "inner".crmid)
-> Index Scan using activity_activityid_subject_idx on activity a
(cost=0.00..11489.23 rows=343003 width=145) (actual time=0.430..1075.108
rows=343001 loops=1)
-> Index Scan using seactivityrel_crmid_idx on seactivityrel s
(cost=0.00..38518.04 rows=1748826 width=8) (actual time=76.291..5410.545
rows=1748826 loops=1)
Total runtime: 18276.780 ms
(13 rows)

After partition:

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=115857.19..357283.03 rows=8306416340 width=548) (actual
time=85871.145..85874.584 rows=1 loops=1)
Hash Cond: ("outer".crmid = "inner".activityid)
-> Append (cost=0.00..37825.51 rows=949942 width=329) (actual
time=0.167..72430.097 rows=949941 loops=1)
-> Seq Scan on crm c (cost=0.00..13.25 rows=1 width=280) (actual
time=0.001..0.001 rows=0 loops=1)
Filter: (deleted = 0)
-> Seq Scan on crm_active c (cost=0.00..37812.26 rows=949941 width=329)
(actual time=0.162..70604.116 rows=949941 loops=1)
Filter: (deleted = 0)
-> Hash (cost=73058.13..73058.13 rows=1748826 width=152) (actual
time=9603.453..9603.453 rows=1 loops=1)
-> Merge Join (cost=0.00..73058.13 rows=1748826 width=152) (actual
time=7959.707..9603.101 rows=1 loops=1)
Merge Cond: ("outer".activityid = "inner".crmid)
-> Index Scan using activity_pkey on activity a (cost=0.00..11822.25
rows=343004 width=144) (actual time=88.467..1167.556 rows=343001 loops=1)
-> Index Scan using seactivityrel_crmid_idx on seactivityrel s
(cost=0.00..38518.04 rows=1748826 width=8) (actual time=0.459..6148.843
rows=1748826 loops=1)
Total runtime: 85875.591 ms
(13 rows)

Browse pgsql-performance by date

  From Date Subject
Next Message Neil Whelchel 2010-10-10 10:29:42 Re: Slow count(*) again...
Previous Message Craig Ringer 2010-10-10 07:20:55 Re: large dataset with write vs read clients