X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BC9A3DB7D1 for ; Tue, 15 Nov 2005 00:04:41 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 51677-04 for ; Tue, 15 Nov 2005 04:04:01 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noao.edu (noao.edu [140.252.1.54]) by svr1.postgresql.org (Postfix) with ESMTP id E8BF4DB77B for ; Tue, 15 Nov 2005 00:04:00 -0400 (AST) X-TFF-CGPSA-Version: 1.4f1 X-TFF-CGPSA-Filter: Scanned Received: from weaver.tuc.noao.edu ([140.252.14.8] verified) by noao.edu (CommuniGate Pro SMTP 5.0.1) with ESMTPS id 21639674; Mon, 14 Nov 2005 21:03:58 -0700 Received: from [127.0.0.1] (localhost.localdomain [127.0.0.1]) by weaver.tuc.noao.edu (8.13.1/8.13.1) with ESMTP id jAF43uGO005497; Mon, 14 Nov 2005 21:03:57 -0700 Message-ID: <43795E2C.4040502@noao.edu> Date: Mon, 14 Nov 2005 21:03:56 -0700 From: Steve Wampler User-Agent: Mozilla Thunderbird 1.0.7 (X11/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Scott Lamb CC: Postgres-performance Subject: Re: Help speeding up delete References: <43790A99.9050603@noao.edu> <5E90D296-FA4A-4E48-92B4-396AADE35EF2@slamb.org> <43792355.1020501@noao.edu> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/238 X-Sequence-Number: 15495 Scott Lamb wrote: > On Nov 14, 2005, at 3:52 PM, Steve Wampler wrote: > >> Scott Lamb wrote: >> >>> On Nov 14, 2005, at 2:07 PM, Steve Wampler wrote: >>> >>>> # SELECT at.id FROM "tmp_table2" at, "tmp_tabl2e" a >>>> # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505'; >>> >>> >>> >>> Isn't this equivalent? >>> >>> select id from tmp_table2 where name = 'obsid' and value = 'oid080505'; >> >> >> Probably, the user based the above on a query designed to find >> all rows with the same id as those rows that have a.name='obsid' and >> a.value='oid080505'. > > > Well, this indirection is only significant if those two sets can > differ. If (A) you meant "tmp_table2" when you wrote "tmp_tabl2e", so > this is a self-join, and (B) there is a primary key on "id", I don't > think that can ever happen. I wasn't clear. The original query was: SELECT at.* FROM "tmp_table2" at, "tmp_table2" a WHERE at.id=a.id and a.name='obsid' and a.value='oid080505'; which is significantly different than: SELECT * FROM "tmp_table2" WHERE name='obsid' and value='oid080505'; The user had adapted that query for her needs, but it would have been better to just use the query that you suggested (as the subselect in the DELETE FROM...). Unfortunately, that only improves performance slightly - it is still way too slow on deletes. -- Steve Wampler -- swampler@noao.edu The gods that smiled on your birth are now laughing out loud.