pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Query becoming slower on adding a primary key
Date: Wed, 09 Jun 2004 00:11:54 +0530
Message-ID: <40C60872.60209@trade-india.com> (raw)
In-Reply-To: <40BD94BC.5030903@trade-india.com>
References: <33303.192.168.0.100.1086117670.squirrel@system67.trade-india-local.com>
	<16609.1086125809@sss.pgh.pa.us>
	<40BD94BC.5030903@trade-india.com>


Hi,

Is there any solution to this issue ? I am facing it every week.

Warm Regds
Mallah.

Rajesh Kumar Mallah wrote:

>
> Tom Lane wrote:
>
>>mallah@trade-india.com writes:
>>  
>>
>>>tradein_clients=# explain analyze select  email_id ,email ,contact from
>>>t_a a join email_source f using(email_id) join email_subscriptions h
>>>using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3  ;
>>>    
>>>
>>
>>  
>>
>>>Runs for Ever.
>>>    
>>>
>>
>>So what does plain explain say about it?
>>  
>>
> Oops sorry that was a valuable info i left. (sorry for delay too)
>
> tradein_clients=# explain  select  email_id ,email ,contact from t_a a 
> join email_source f using(email_id) join email_subscriptions h
> using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3  ;
> +-----------------------------------------------------------------------------------------+
> |                                       QUERY 
> PLAN                                        |
> +-----------------------------------------------------------------------------------------+
> | Hash Join  (cost=133741.48..224746.39 rows=328814 
> width=40)                             |
> |   Hash Cond: ("outer".email_id = 
> "inner".email_id)                                      |
> |   ->  Seq Scan on email_subscriptions h  (cost=0.00..70329.54 
> rows=749735 width=4)      |
> |         Filter: (sub_id = 
> 3)                                                            |
> |   ->  Hash  (cost=130230.99..130230.99 rows=324994 
> width=44)                            |
> |         ->  Hash Join  (cost=26878.00..130230.99 rows=324994 
> width=44)                  |
> |               Hash Cond: ("outer".email_id = 
> "inner".email_id)                          |
> |               ->  Seq Scan on email_source f  (cost=0.00..26159.21 
> rows=324994 width=4) |
> |                     Filter: (source_id = 
> 1)                                             |
> |               ->  Hash  (cost=18626.80..18626.80 rows=800080 
> width=40)                  |
> |                     ->  Seq Scan on t_a a  (cost=0.00..18626.80 
> rows=800080 width=40)   |
> +-----------------------------------------------------------------------------------------+
> (11 rows)
>
> Time: 452.417 ms
> tradein_clients=# ALTER TABLE t_a add primary key(email_id);
> NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index 
> "t_a_pkey" for table "t_a"
> ALTER TABLE
> Time: 7923.230 ms
> tradein_clients=# explain  select  email_id ,email ,contact from t_a a 
> join email_source f using(email_id) join email_subscriptions
> h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3  ;
> +-------------------------------------------------------------------------------------------------------------------+
> |                                                    QUERY 
> PLAN                                                     |
> +-------------------------------------------------------------------------------------------------------------------+
> | Hash Join  (cost=106819.76..197824.68 rows=328814 
> width=40)                                                       |
> |   Hash Cond: ("outer".email_id = 
> "inner".email_id)                                                                
> |
> |   ->  Seq Scan on email_subscriptions h  (cost=0.00..70329.54 
> rows=749735 width=4)                                |
> |         Filter: (sub_id = 
> 3)                                                                                      
> |
> |   ->  Hash  (cost=103309.28..103309.28 rows=324994 
> width=44)                                                      |
> |         ->  Merge Join  (cost=0.00..103309.28 rows=324994 
> width=44)                                               |
> |               Merge Cond: ("outer".email_id = 
> "inner".email_id)                                                   |
> |               ->  Index Scan using t_a_pkey on t_a a  
> (cost=0.00..44689.59 rows=800080 width=40)                  |
> |               ->  Index Scan using email_source_pkey on email_source 
> f  (cost=0.00..52602.59 rows=324994 width=4) |
> |                     Filter: (source_id = 
> 1)                                                                       |
> +-------------------------------------------------------------------------------------------------------------------+
> (10 rows)
>
> Time: 2436.551 ms
> tradein_clients=#
>
>
>
> Regds
> Mallah.
>
>
>>			regards, tom lane
>>
>>  
>>
>

view thread (6+ messages)  latest in thread

Message-ID: <40C60872.60209@trade-india.com>
Permalink:  ../40C60872.60209@trade-india.com/
Also on:    postgresql.org/message-id/40C60872.60209@trade-india.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: mallah@trade-india.com, tgl@sss.pgh.pa.us
  Subject: Re: Query becoming slower on adding a primary key
  In-Reply-To: <40C60872.60209@trade-india.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox