deletions on master table takes for ever... - Mailing list pgsql-sql

<tt>Hi folks ,</tt><br /><tt></tt> <tt></tt><p><tt>can anyone explain why the deletions below take so much
time.</tt><br/><tt>the problem is none of the queries below is faast ?</tt><tt></tt><p><tt><b>1.</b> explain delete
from email_bank where exists (select domain from t_bad_domains where domain=
email_bank.domain);</tt><tt></tt><p><tt><fontcolor="#3333FF">NOTICE:  QUERY PLAN:</font></tt><br /><tt><font
color="#3333FF">SeqScan on email_bank  (cost=0.00..724279.53 rows=1009007 width=6)</font></tt><br /><tt><font
color="#3333FF"> SubPlan</font></tt><br /><tt><font color="#3333FF">    ->  Index Scan using t_bad_domains_domain on
t_bad_domains (cost=0.00..296.39 rows=424 width=12)</font></tt><br /><tt><font
color="#3333FF">EXPLAIN</font></tt><tt><fontcolor="#3333FF"></font></tt><p><tt><font color="#000000"><b>2.</b> explain
deletefrom  email_bank where email_bank.domain= t_bad_domains.domain ;</font></tt><br /><tt><font
color="#000000">NOTICE: QUERY PLAN:</font></tt><tt><font color="#000000"></font></tt><p><tt><font
color="#3333FF">NestedLoop  (cost=0.00..304423734.99 rows=427899689 width=30)</font></tt><br /><tt><font
color="#3333FF"> ->  Seq Scan on email_bank  (cost=0.00..19092.07 rows=1009007 width=18)</font></tt><br /><tt><font
color="#3333FF"> ->  Index Scan using t_bad_domains_domain on t_bad_domains  (cost=0.00..296.39 rows=424
width=12)</font></tt><tt><fontcolor="#3333FF"></font></tt><p><tt><font color="#3333FF">EXPLAIN</font></tt><br
/><tt><fontcolor="#000000"></font></tt> <tt><font color="#000000"></font></tt><p><tt><font color="#000000">also find
thetable  descriptions below.</font></tt><tt><font color="#000000"></font></tt><p><tt><font color="#000000">Also i
wouldlike to tell that the are 8 tables linked to the master table</font></tt><br /><tt><font
color="#000000">email_bankso every deletetion on email_bank is eight deletions.</font></tt><tt><font
color="#000000"></font></tt><p><tt><fontcolor="#000000">do i need to create indexes on the slave tables also
?</font></tt><tt><fontcolor="#000000"></font></tt><p><tt><font color="#000000">and the m/c is Dual PIII 800 and SCSI
and1 GB</font></tt><tt><font color="#000000"></font></tt><p><tt><font color="#000000">regds</font></tt><br /><tt><font
color="#000000">mallah.</font></tt><br/><tt><font color="#000000"></font></tt> <tt><font
color="#000000"></font></tt><p><tt><fontcolor="#000000"><font
size="-1">=============================================================================</font></font></tt><tt><font
color="#000000"></font></tt><p><tt><fontcolor="#000000"><font size="-1">tradein_clients=> \d
email_bank</font></font></tt><br/><tt><font color="#000000"><font size="-1">                                   Table
"email_bank"</font></font></tt><br/><tt><font color="#000000"><font size="-1">  Column  |          Type         
|                    Modifiers</font></font></tt><br /><tt><font color="#000000"><font
size="-1">----------+------------------------+----------------------------------------------------</font></font></tt><br
/><tt><fontcolor="#000000"><font size="-1"> email_id | integer                | default
nextval('"email_bank_email_id_seq"'::text)</font></font></tt><br/><tt><font color="#000000"><font size="-1"> userid   |
integer               | not null default 0</font></font></tt><br /><tt><font color="#000000"><font size="-1"> email   
|character varying(100) | not null</font></font></tt><br /><tt><font color="#000000"><font size="-1"> country  |
charactervarying(100) |</font></font></tt><br /><tt><font color="#000000"><font size="-1"> domain   | character
varying(100)| default ''</font></font></tt><br /><tt><font color="#000000"><font size="-1"> tld      | character
varying(100)| default ''</font></font></tt><br /><tt><font color="#000000"><font size="-1">Indexes:
email_bank_email_key</font></font></tt><br/><tt><font color="#000000"><font size="-1">Unique keys:
email_bank_email_id_key,</font></font></tt><br/><tt><font color="#000000"><font size="-1">            
uniq_email_insencase</font></font></tt><br/><tt><font color="#000000"><font size="-1">Triggers:
RI_ConstraintTrigger_63438161,</font></font></tt><br/><tt><font color="#000000"><font size="-1">         
RI_ConstraintTrigger_63438159,</font></font></tt><br/><tt><font color="#000000"><font size="-1">         
RI_ConstraintTrigger_63438157,</font></font></tt><br/><tt><font color="#000000"><font size="-1">         
RI_ConstraintTrigger_63438155,</font></font></tt><br/><tt><font color="#000000"><font size="-1">         
RI_ConstraintTrigger_63438153,</font></font></tt><br/><tt><font color="#000000"><font size="-1">         
RI_ConstraintTrigger_63438151,</font></font></tt><br/><tt><font color="#000000"><font size="-1">         
RI_ConstraintTrigger_63438149,</font></font></tt><br/><tt><font color="#000000"><font size="-1">         
RI_ConstraintTrigger_63438147</font></font></tt><tt><fontcolor="#000000"><font
size="-1"></font></font></tt><p><tt><fontcolor="#000000"><font
size="-1">tradein_clients=></font></font></tt><tt><fontcolor="#000000"><font
size="-1"></font></font></tt><p><tt><fontcolor="#000000"><font size="-1">tradein_clients=> \d
t_bad_domains</font></font></tt><br/><tt><font color="#000000"><font size="-1">   Table
"t_bad_domains"</font></font></tt><br/><tt><font color="#000000"><font size="-1"> Column | Type |
Modifiers</font></font></tt><br/><tt><font color="#000000"><font
size="-1">--------+------+-----------</font></font></tt><br/><tt><font color="#000000"><font size="-1"> domain | text
|</font></font></tt><br/><tt><font color="#000000"><font size="-1">Indexes:
t_bad_domains_domain</font></font></tt><tt><fontcolor="#000000"><font size="-1"></font></font></tt><p><tt><font
color="#000000"><fontsize="-1">tradein_clients=></font></font></tt><br /><tt><font color="#000000"><font
size="-1">==============================================================================</font></font></tt>