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_subscriptions h (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.92 rows=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=800080 width=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 null
source_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 null
userid | 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 null
sub_id | integer | not null
Indexes:
"email_subscriptions_pkey" primary key, btree (email_id, sub_id)
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
