pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBad performing DELETE
2+ messages / 2 participants
[nested] [flat]
* Bad performing DELETE
@ 2000-11-27 11:39 Hans-Jürgen Schönig <hs@cybertec.at>
2000-11-27 15:44 ` Re: Bad performing DELETE Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Hans-Jürgen Schönig @ 2000-11-27 11:39 UTC (permalink / raw)
To: pgsql-sql
>
I have two tables: t_haus is about 1400 row and t_host has 364000 entries.
Both tables are indexed on edvnr. I did a vacuum on my db and all indices
are rebuild.
I want to delete all Entries in t_haus where a row can be found in t_host.
When using "delete from t_haus where t_haus.edvnr=t_host.edvnr; " the
database performs extremely bad.
explain delete from t_haus where t_haus.edvnr=t_host.edvnr;
NOTICE: QUERY PLAN:
Merge Join (cost=52178.53..56754.63 rows=6299767 width=14)
-> Sort (cost=52038.25..52038.25 rows=364359 width=4)
-> Seq Scan on t_host (cost=0.00..11700.59 rows=364359 width=4)
-> Sort (cost=140.27..140.27 rows=1729 width=10)
-> Seq Scan on t_haus (cost=0.00..47.29 rows=1729 width=10)
EXPLAIN
What can I do to speed it up?
I've also had troubles with DELETE before (when doing joins in the
WHERE clause).
Hans
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Bad performing DELETE
2000-11-27 11:39 Bad performing DELETE Hans-Jürgen Schönig <hs@cybertec.at>
@ 2000-11-27 15:44 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2000-11-27 15:44 UTC (permalink / raw)
To: Hans-Jürgen Schönig <hs@cybertec.at>; +Cc: pgsql-sql
=?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= <hs@cybertec.at> writes:
> I have two tables: t_haus is about 1400 row and t_host has 364000 entries.
> Both tables are indexed on edvnr. I did a vacuum on my db and all indices
> are rebuild.
> I want to delete all Entries in t_haus where a row can be found in t_host.
> When using "delete from t_haus where t_haus.edvnr=t_host.edvnr; " the
> database performs extremely bad.
> explain delete from t_haus where t_haus.edvnr=t_host.edvnr;
> NOTICE: QUERY PLAN:
> Merge Join (cost=52178.53..56754.63 rows=6299767 width=14)
> -> Sort (cost=52038.25..52038.25 rows=364359 width=4)
> -> Seq Scan on t_host (cost=0.00..11700.59 rows=364359 width=4)
> -> Sort (cost=140.27..140.27 rows=1729 width=10)
> -> Seq Scan on t_haus (cost=0.00..47.29 rows=1729 width=10)
I wonder if a hash join would be faster. What does EXPLAIN show if
you first do "set enable_mergejoin to off"? What's the actual
performance in both cases?
Also, it's possible that the performance problem isn't the fault of the
plan at all. Are there multiple rows in t_host matching the deletable
rows of t_haus? I'm wondering if there's some speed penalty associated
with trying to delete the same row multiple times in one command...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-11-27 15:44 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-27 11:39 Bad performing DELETE Hans-Jürgen Schönig <hs@cybertec.at>
2000-11-27 15:44 ` Tom Lane <tgl@sss.pgh.pa.us>
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