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-24 11:46:28
Message-ID: 4B0544A0-9DE5-4BF5-9FCB-45AB5738F390@me.com (view raw or flat)
Thread:
Lists: pgsql-performance
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: Laszlo NagyDate: 2012-07-24 12:51:07
Subject: ZFS vs. UFS
Previous:From: AI RummanDate: 2012-07-24 11:35:54
Subject: Re: Why do I need more time with partition table?

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