Skip site navigation (1) Skip section navigation (2)

Re: Why do I need more time with partition table?

From: AI Rumman <rummandba(at)gmail(dot)com>
To: Jan Otto <asche(at)me(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Why do I need more time with partition table?
Date: 2012-07-25 08:40:33
Message-ID: CAGoODpcuTHdms590BhxJdqiWQ5PPk5i3vg4BYaQoCiAW2HkFOw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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 <asche(at)me(dot)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
>
>

In response to

Responses

pgsql-performance by date

Next:From: Jan OttoDate: 2012-07-25 14:42:24
Subject: Re: Why do I need more time with partition table?
Previous:From: Thomas KellererDate: 2012-07-25 08:10:13
Subject: Re: Using ctid column changes plan drastically

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group