Re: [SQL] Query becoming slower on adding a primary key

[Rajesh Kumar Mallah]

Tom Lane wrote:

[EMAIL PROTECTED] 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