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

Hi ,

After adding a primary key in one of the participant tables
the query never finishes. The live table has a primary key
so it cannot be removed. I made a copy of the live table
using create table t_a as select * from tab. the query works
fine . when i ad the pkey like i have in the live table it does
not work. Can anyone please help me with this problem?
below are the details.
thanks in advance.

Regds
Mallah.



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  ;                                                               QUERY
PLAN

-------------------------------------------------------------------------------------------------------------------------------------------Hash
Join (cost=134818.15..221449.12 rows=306921 width=40) (actual
 
time=9457.000..17700.227 rows=283763 loops=1)  Hash Cond: ("outer".email_id = "inner".email_id)  ->  Seq Scan on
email_subscriptionsh  (cost=0.00..70323.77 rows=746257
 
width=4) (actual time=0.054..3434.639 rows=746721 loops=1)        Filter: (sub_id = 3)  ->  Hash
(cost=131485.92..131485.92rows=308491 width=44) (actual
 
time=9456.757..9456.757 rows=0 loops=1)        ->  Hash Join  (cost=26878.00..131485.92 rows=308491 width=44)
(actual time=2293.378..8978.407 rows=299873 loops=1)              Hash Cond: ("outer".email_id = "inner".email_id)
       ->  Seq Scan on email_source f  (cost=0.00..26119.84
 
rows=308491 width=4) (actual time=0.123..1094.661
rows=317504 loops=1)                    Filter: (source_id = 1)              ->  Hash  (cost=18626.80..18626.80
rows=800080width=40)
 
(actual time=2275.979..2275.979 rows=0 loops=1)                    ->  Seq Scan on t_a a  (cost=0.00..18626.80
rows=800080 width=40) (actual time=0.009..1297.728
rows=800080 loops=1)Total runtime: 17856.763 ms
(12 rows)

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: 6322.116 ms
tradein_clients=# VACUUM analyze t_a;
VACUUM
Time: 809.840 ms



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.





ROW COUNTS:

t_a : 8,00,080
email_source: 15,45,056
email_subscriptions: 40,41,133


Structures:


tradein_clients=# \d email_source  Table "public.email_source" Column   |  Type   | Modifiers
-----------+---------+-----------email_id  | integer | not nullsource_id | integer | not null
Indexes:   "email_source_pkey" primary key, btree (email_id, source_id)   "email_source_sid" btree (source_id)
Foreign-key constraints:   "$1" FOREIGN KEY (source_id) REFERENCES sources(source_id) ON UPDATE
CASCADE ON DELETE CASCADE



\d t_a               Table "public.t_a" Column   |           Type           | Modifiers
-----------+--------------------------+-----------email_id  | integer                  | not nulluserid    | integer
             |email     | character varying(100)   |country   | character varying(100)   |city      | character
varying(50)   |contact   | character varying(100)   |last_sent | timestamp with time zone |pref      | character
varying(1)    |website   | character varying(255)   |address   | text                     |
 

\d email_subscriptions
Table "public.email_subscriptions" Column  |  Type   | Modifiers
----------+---------+-----------email_id | integer | not nullsub_id   | integer | not null
Indexes:   "email_subscriptions_pkey" primary key, btree (email_id, sub_id)