hi al, On Jul 25, 2012, at 10:40 AM, AI Rumman <rummandba@xxxxxxxxx> wrote: > 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) did you have analyze'd your tables? try if indexing column deleted on table1_leads gives you some more speed. regards, jan -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
