Re: Query becoming slower on adding a primary key

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(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
>>
>>
>>
>