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