Re: Query becoming slower on adding a primary key - Mailing list pgsql-sql

<br /> Tom Lane wrote: <blockquote cite="mid16609.1086125809@sss.pgh.pa.us" type="cite"><pre wrap=""><a
class="moz-txt-link-abbreviated"href="mailto:mallah@trade-india.com">mallah@trade-india.com</a> writes:
</pre><blockquotetype="cite"><pre wrap="">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  ;   </pre></blockquote><pre wrap=""> </pre><blockquote
type="cite"><prewrap="">Runs for Ever.   </pre></blockquote><pre wrap="">
 
So what does plain explain say about it? </pre></blockquote> Oops sorry that was a valuable info i left. (sorry for
delaytoo)<br /><small><br /><tt>tradein_clients=# explain  select  email_id ,email ,contact from t_a a join
email_sourcef using(email_id) join email_subscriptions h <br /> using(email_id) where 1=1 and f.source_id =1 and
h.sub_id= 3  ;<br /> +-----------------------------------------------------------------------------------------+<br />
|                                      QUERY PLAN                                        |<br />
+-----------------------------------------------------------------------------------------+<br/> | Hash Join 
(cost=133741.48..224746.39rows=328814 width=40)                             |<br /> |   Hash Cond: ("outer".email_id =
"inner".email_id)                                     |<br /> |   ->  Seq Scan on email_subscriptions h 
(cost=0.00..70329.54rows=749735 width=4)      |<br /> |         Filter: (sub_id =
3)                                                           |<br /> |   ->  Hash  (cost=130230.99..130230.99
rows=324994width=44)                            |<br /> |         ->  Hash Join  (cost=26878.00..130230.99
rows=324994width=44)                  |<br /> |               Hash Cond: ("outer".email_id =
"inner".email_id)                         |<br /> |               ->  Seq Scan on email_source f 
(cost=0.00..26159.21rows=324994 width=4) |<br /> |                     Filter: (source_id =
1)                                            |<br /> |               ->  Hash  (cost=18626.80..18626.80 rows=800080
width=40)                 |<br /> |                     ->  Seq Scan on t_a a  (cost=0.00..18626.80 rows=800080
width=40)  |<br /> +-----------------------------------------------------------------------------------------+<br />
(11rows)<br /><br /> Time: 452.417 ms<br /> tradein_clients=# ALTER TABLE t_a add primary key(email_id);<br /> NOTICE: 
ALTERTABLE / ADD PRIMARY KEY will create implicit index "t_a_pkey" for table "t_a"<br /> ALTER TABLE<br /> Time:
7923.230ms<br /> tradein_clients=# explain  select  email_id ,email ,contact from t_a a join email_source f
using(email_id)join email_subscriptions <br /> h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3  ;<br />
+-------------------------------------------------------------------------------------------------------------------+<br
/>|                                                    QUERY PLAN                                                    
|<br/>
+-------------------------------------------------------------------------------------------------------------------+<br
/>| Hash Join  (cost=106819.76..197824.68 rows=328814 width=40)                                                      
|<br/> |   Hash Cond: ("outer".email_id =
"inner".email_id)                                                               |<br /> |   ->  Seq Scan on
email_subscriptionsh  (cost=0.00..70329.54 rows=749735 width=4)                                |<br /> |        
Filter:(sub_id = 3)                                                                                      |<br /> |  
-> Hash  (cost=103309.28..103309.28 rows=324994 width=44)                                                      |<br
/>|         ->  Merge Join  (cost=0.00..103309.28 rows=324994
width=44)                                              |<br /> |               Merge Cond: ("outer".email_id =
"inner".email_id)                                                  |<br /> |               ->  Index Scan using
t_a_pkeyon t_a a  (cost=0.00..44689.59 rows=800080 width=40)                  |<br /> |               ->  Index Scan
usingemail_source_pkey on email_source f  (cost=0.00..52602.59 rows=324994 width=4) |<br /> |                    
Filter:(source_id = 1)                                                                       |<br />
+-------------------------------------------------------------------------------------------------------------------+<br
/>(10 rows)<br /><br /> Time: 2436.551 ms<br /> tradein_clients=#</tt></small><br /><br /><br /><br /> Regds<br />
Mallah.<br/><br /><br /><blockquote cite="mid16609.1086125809@sss.pgh.pa.us" type="cite"><pre wrap="">        regards,
tomlane
 
 </pre></blockquote><br />