From mallah@trade-india.com Sat Apr 13 10:04:29 2002 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-- From sszabo@megazone23.bigpanda.com Sat Apr 13 14:57:05 2002 Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 345F2474E71 for ; Sat, 13 Apr 2002 10:57:05 -0400 (EDT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id C9C71D5FA; Sat, 13 Apr 2002 07:57:08 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id BF7975C01; Sat, 13 Apr 2002 07:57:08 -0700 (PDT) Date: Sat, 13 Apr 2002 07:57:08 -0700 (PDT) From: Stephan Szabo To: Rajesh Kumar Mallah Cc: PostgResql SQL Mailing List Subject: Re: deletions on master table takes for ever... In-Reply-To: <3CB8037E.B73F7F6D@trade-india.com> Message-ID: <20020413075506.W84263-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200204/168 X-Sequence-Number: 7432 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.