pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Bad performing DELETE
2+ messages / 2 participants
[nested] [flat]

* Bad performing DELETE
@ 2000-11-27 11:39  Hans-Jürgen Schönig <hs@cybertec.at>
  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 15:44  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Hans-Jürgen Schönig <hs@cybertec.at>
  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