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 DC6C9D6D8C for ; Mon, 14 Nov 2005 21:08:05 -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 12476-01 for ; Tue, 15 Nov 2005 01:08:02 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from calvin.slamb.org (calvin.slamb.org [216.136.66.56]) by svr1.postgresql.org (Postfix) with ESMTP id 535C9DB77F for ; Mon, 14 Nov 2005 21:08:03 -0400 (AST) Received: by calvin.slamb.org (Postfix, from userid 103) id 616AB6FD21; Mon, 14 Nov 2005 19:08:07 -0600 (CST) Received: from [IPv6:::1] (localhost.slamb.org [127.0.0.1]) by calvin.slamb.org (Postfix) with ESMTP id C4AC16FCD7; Mon, 14 Nov 2005 19:08:05 -0600 (CST) In-Reply-To: <43792355.1020501@noao.edu> References: <43790A99.9050603@noao.edu> <5E90D296-FA4A-4E48-92B4-396AADE35EF2@slamb.org> <43792355.1020501@noao.edu> Mime-Version: 1.0 (Apple Message framework v746.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Cc: Postgres-performance Content-Transfer-Encoding: 7bit From: Scott Lamb Subject: Re: Help speeding up delete Date: Mon, 14 Nov 2005 17:08:03 -0800 To: Steve Wampler X-Mailer: Apple Mail (2.746.2) 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/237 X-Sequence-Number: 15494 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. > It appears (to me) to be the Seq Scan on tmp_table2 > that is the killer here. If we put an index on, would it help? On...tmp_table2.id? If it is a primary key, there already is one. If not, yeah, I expect it would help. -- Scott Lamb