Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eARFiUt50031 for ; Mon, 27 Nov 2000 10:44:31 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id eARFiWw10917; Mon, 27 Nov 2000 10:44:32 -0500 (EST) To: =?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= cc: pgsql-sql@postgresql.org Subject: Re: Bad performing DELETE In-reply-to: <3A2247F4.85C70143@cybertec.at> References: <20001127124423.A26525@erm1.u-strasbg.fr> <3A2247F4.85C70143@cybertec.at> Comments: In-reply-to =?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= message dated "Mon, 27 Nov 2000 12:39:32 +0100" Date: Mon, 27 Nov 2000 10:44:32 -0500 Message-ID: <10914.975339872@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200011/240 X-Sequence-Number: 271 =?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= 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