pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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=>
==============================================================================

view thread (2+ messages)  latest in thread

Message-ID: <3CB8037E.B73F7F6D@trade-india.com>
Permalink:  ../3CB8037E.B73F7F6D@trade-india.com/
Also on:    postgresql.org/message-id/3CB8037E.B73F7F6D@trade-india.com

 · 

reply

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