Received: from mail-relay.trade-india.com (unknown [203.196.129.235]) by postgresql.org (Postfix) with SMTP id EEDBA475893 for ; Sat, 13 Apr 2002 06:04:29 -0400 (EDT) Received: (qmail 3764 invoked from network); 13 Apr 2002 10:10:49 -0000 Received: from system11.trade-india-local.com (HELO trade-india.com) (192.168.0.11) by ptil-236-129-del.primus-india.net with SMTP; 13 Apr 2002 10:10:49 -0000 Message-ID: <3CB8037E.B73F7F6D@trade-india.com> Date: Sat, 13 Apr 2002 15:37:58 +0530 From: Rajesh Kumar Mallah X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.4.2-2 i686) X-Accept-Language: en MIME-Version: 1.0 To: PostgResql SQL Mailing List Subject: deletions on master table takes for ever... Content-Type: multipart/alternative; boundary="------------531E03FE39DF160D7003F1C6" X-Archive-Number: 200204/167 X-Sequence-Number: 7431 --------------531E03FE39DF160D7003F1C6 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi folks , can anyone explain why the deletions below take so much time. the problem is none of the queries below is faast ? 1. explain delete from email_bank where exists (select domain from t_bad_domains where domain= email_bank.domain); NOTICE: QUERY PLAN: Seq Scan on email_bank (cost=0.00..724279.53 rows=1009007 width=6) SubPlan -> Index Scan using t_bad_domains_domain on t_bad_domains (cost=0.00..296.39 rows=424 width=12) EXPLAIN 2. explain delete from email_bank where email_bank.domain= t_bad_domains.domain ; NOTICE: QUERY PLAN: Nested Loop (cost=0.00..304423734.99 rows=427899689 width=30) -> Seq Scan on email_bank (cost=0.00..19092.07 rows=1009007 width=18) -> Index Scan using t_bad_domains_domain on t_bad_domains (cost=0.00..296.39 rows=424 width=12) EXPLAIN also find the table descriptions below. 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 ? and the m/c is Dual PIII 800 and SCSI and 1 GB regds mallah. ============================================================================= tradein_clients=> \d email_bank Table "email_bank" Column | Type | Modifiers ----------+------------------------+---------------------------------------------------- email_id | integer | default nextval('"email_bank_email_id_seq"'::text) userid | integer | not null default 0 email | character varying(100) | not null country | character varying(100) | domain | character varying(100) | default '' tld | character varying(100) | default '' Indexes: email_bank_email_key Unique keys: email_bank_email_id_key, uniq_email_insencase Triggers: RI_ConstraintTrigger_63438161, RI_ConstraintTrigger_63438159, RI_ConstraintTrigger_63438157, RI_ConstraintTrigger_63438155, RI_ConstraintTrigger_63438153, RI_ConstraintTrigger_63438151, RI_ConstraintTrigger_63438149, RI_ConstraintTrigger_63438147 tradein_clients=> tradein_clients=> \d t_bad_domains Table "t_bad_domains" Column | Type | Modifiers --------+------+----------- domain | text | Indexes: t_bad_domains_domain tradein_clients=> ============================================================================== --------------531E03FE39DF160D7003F1C6 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hi folks ,
 

can anyone explain why the deletions below take so much time.
the problem is none of the queries below is faast ?

1. explain delete from  email_bank where exists (select domain from t_bad_domains where domain= email_bank.domain);

NOTICE:  QUERY PLAN:
Seq Scan on email_bank  (cost=0.00..724279.53 rows=1009007 width=6)
  SubPlan
    ->  Index Scan using t_bad_domains_domain on t_bad_domains  (cost=0.00..296.39 rows=424 width=12)
EXPLAIN

2. explain delete from  email_bank where email_bank.domain= t_bad_domains.domain ;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..304423734.99 rows=427899689 width=30)
  ->  Seq Scan on email_bank  (cost=0.00..19092.07 rows=1009007 width=18)
  ->  Index Scan using t_bad_domains_domain on t_bad_domains  (cost=0.00..296.39 rows=424 width=12)

EXPLAIN
 

also find the table  descriptions below.

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 ?

and the m/c is Dual PIII 800 and SCSI and 1 GB

regds
mallah.
 

=============================================================================

tradein_clients=> \d email_bank
                                   Table "email_bank"
  Column  |          Type          |                     Modifiers
----------+------------------------+----------------------------------------------------
 email_id | integer                | default nextval('"email_bank_email_id_seq"'::text)
 userid   | integer                | not null default 0
 email    | character varying(100) | not null
 country  | character varying(100) |
 domain   | character varying(100) | default ''
 tld      | character varying(100) | default ''
Indexes: email_bank_email_key
Unique keys: email_bank_email_id_key,
             uniq_email_insencase
Triggers: RI_ConstraintTrigger_63438161,
          RI_ConstraintTrigger_63438159,
          RI_ConstraintTrigger_63438157,
          RI_ConstraintTrigger_63438155,
          RI_ConstraintTrigger_63438153,
          RI_ConstraintTrigger_63438151,
          RI_ConstraintTrigger_63438149,
          RI_ConstraintTrigger_63438147

tradein_clients=>

tradein_clients=> \d t_bad_domains
   Table "t_bad_domains"
 Column | Type | Modifiers
--------+------+-----------
 domain | text |
Indexes: t_bad_domains_domain

tradein_clients=>
============================================================================== --------------531E03FE39DF160D7003F1C6--