pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
deletions on master table takes for ever...
2+ messages / 2 participants
[nested] [flat]

* deletions on master table takes for ever...
@ 2002-04-13 10:07 Rajesh Kumar Mallah <mallah@trade-india.com>
  2002-04-13 14:57 ` Re: deletions on master table takes for ever... Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Rajesh Kumar Mallah @ 2002-04-13 10:07 UTC (permalink / raw)
  To: pgsql-sql

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=>
==============================================================================

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: deletions on master table takes for ever...
  2002-04-13 10:07 deletions on master table takes for ever... Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2002-04-13 14:57 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Stephan Szabo @ 2002-04-13 14:57 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-sql


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.





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2002-04-13 14:57 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-13 10:07 deletions on master table takes for ever... Rajesh Kumar Mallah <mallah@trade-india.com>
2002-04-13 14:57 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

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