Thanks. I missed to add the trigger. Now I added it, but still without partition taking less time compared to with partition query.
*With partition :- * explain analyze > select * > from table1 as c > inner join table2 as a on c.crmid = a.activityid and deleted = 0 > where module ='Leads' > ; > > > QUERY PLAN > > > ------------------------------------------------------------------------------------------------------------------------------------------------------ > Hash Join (cost=25669.79..86440.88 rows=288058 width=367) (actual > time=4411.734..4411.734 rows=0 loops=1) > Hash Cond: (a.activityid = c.crmid) > -> Seq Scan on table2 a (cost=0.00..18337.34 rows=681434 width=139) > (actual time=0.264..1336.555 rows=681434 loops=1) > -> Hash (cost=13207.07..13207.07 rows=288058 width=228) (actual > time=1457.495..1457.495 rows=287365 loops=1) > Buckets: 1024 Batches: 128 Memory Usage: 226kB > -> Append (cost=0.00..13207.07 rows=288058 width=228) (actual > time=0.014..1000.182 rows=287365 loops=1) > -> Seq Scan on table1 c (cost=0.00..0.00 rows=1 > width=367) (actual time=0.001..0.001 rows=0 loops=1) > Filter: ((deleted = 0) AND ((module)::text = > 'Leads'::text)) > -> Seq Scan on table1_leads c (cost=0.00..13207.07 > rows=288057 width=228) (actual time=0.010..490.169 rows=287365 loops=1) > Filter: ((deleted = 0) AND ((module)::text = > 'Leads'::text)) > Total runtime: 4412.534 ms > (11 rows) *Without partition :- * explain analyze > select * > from table1_old as c > inner join table2 as a on c.crmid = a.activityid and deleted = 0 > where module ='Leads' > ; > > QUERY PLAN > > > ---------------------------------------------------------------------------------------------------------------------------------------------------------- > Hash Join (cost=92095.07..157111.03 rows=107445 width=502) (actual > time=3795.273..3795.273 rows=0 loops=1) > Hash Cond: (a.activityid = c.crmid) > -> Seq Scan on table2 a (cost=0.00..18337.34 rows=681434 width=139) > (actual time=0.030..812.925 rows=681434 loops=1) > -> Hash (cost=73246.44..73246.44 rows=314850 width=363) (actual > time=1377.624..1377.624 rows=287365 loops=1) > Buckets: 1024 Batches: 128 Memory Usage: 226kB > -> Bitmap Heap Scan on table1_old c (cost=9228.69..73246.44 > rows=314850 width=363) (actual time=83.189..926.542 rows=287365 loops=1) > Recheck Cond: (((module)::text = 'Leads'::text) AND > (deleted = 0)) > -> Bitmap Index Scan on crmentity_module_idx > (cost=0.00..9149.98 rows=314850 width=0) (actual time=79.357..79.357 > rows=287365 loops=1) > Index Cond: ((module)::text = 'Leads'::text) > Total runtime: 3795.721 ms > (10 rows) On Tue, Jul 24, 2012 at 5:46 PM, Jan Otto <as...@me.com> wrote: > hi al, > > > With Parition :- > > > > > > explain analyze > > select * > > from table1 as c > > inner join table2 as a on c.crmid = a.table2id and deleted = 0 > > where module ='Leads'; > > > QUERY PLAN > > > -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > Hash Join (cost=108101.50..175252.57 rows=313256 width=506) (actual > time=8430.588..8430.588 rows=0 loops=1) > > Hash Cond: (a.table2id = c.crmid) > > -> Seq Scan on table2 a (cost=0.00..18337.34 rows=681434 width=139) > (actual time=0.054..870.554 rows=681434 loops=1) > > -> Hash (cost=89195.80..89195.80 rows=313256 width=367) (actual > time=2751.950..2751.950 rows=287365 loops=1) > > Buckets: 1024 Batches: 128 Memory Usage: 226kB > > -> Append (cost=0.00..89195.80 rows=313256 width=367) (actual > time=0.034..2304.191 rows=287365 loops=1) > > -> Seq Scan on table1 c (cost=0.00..89187.53 > rows=313255 width=367) (actual time=0.032..1783.075 rows=287365 loops=1) > > Filter: ((deleted = 0) AND ((module)::text = > 'Leads'::text)) > > -> Index Scan using table1_leads_deleted_idx on > table1_leads c (cost=0.00..8.27 rows=1 width=280) (actual > time=0.010..0.010 rows=0 loops=1) > > Index Cond: (deleted = 0) > > Filter: ((module)::text = 'Leads'::text) > > Total runtime: 8432.024 ms > > (12 rows) > > > > I set constraint_exclusion to partition. > > > > Why do I need more time with parition? > > it looks like you don't moved your data from base-table to your partitions. > > regards, jan > >
