From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: PostgResql SQL Mailing List <pgsql-sql@postgresql.org>
Subject: deletions on master table takes for ever...
Date: Sat, 13 Apr 2002 15:37:58 +0530
Message-ID: <3CB8037E.B73F7F6D@trade-india.com> (raw)
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=>
==============================================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mallah@trade-india.com
Subject: Re: deletions on master table takes for ever...
In-Reply-To: <3CB8037E.B73F7F6D@trade-india.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox