Обсуждение: deletions on master table takes for ever...

Поиск
Список
Период
Сортировка

deletions on master table takes for ever...

От
Rajesh Kumar Mallah
Дата:
<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>

Re: deletions on master table takes for ever...

От
Stephan Szabo
Дата:
On Sat, 13 Apr 2002, Rajesh Kumar Mallah wrote:

> Also i would like to tell that the are 8 tables linked to the master
> table
> email_bank so every deletetion on email_bank is eight deletions.
>
> do i need to create indexes on the slave tables also ?

You probably should since otherwise it's going to need to do sequential
scans of all those tables for each row being deleted.  That may or may not
be the bottleneck here, but it's worth doing.