Re: Help speeding up delete

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 <http://www.slamb.org/>