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

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

From: Jan Otto <asche(at)me(dot)com>
To: AI Rumman <rummandba(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Why do I need more time with partition table?
Date: 2012-07-25 14:42:24
Message-ID: 98747C10-E954-4D50-B881-B33FA2D8A718@me.com (view raw or flat)
Thread:
Lists: pgsql-performance
hi al,

On Jul 25, 2012, at 10:40 AM, AI Rumman <rummandba(at)gmail(dot)com> 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

In response to

pgsql-performance by date

Next:From: Jeff JanesDate: 2012-07-25 14:55:55
Subject: Re: Why do I need more time with partition table?
Previous:From: AI RummanDate: 2012-07-25 08:40:33
Subject: Re: Why do I need more time with partition table?

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