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)
