Re: Query becoming slower on adding a primary key

Tom Lane wrote:

>mallah(at)trade-india(dot)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
>
>
>