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=287365loops=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)
(actualtime=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