Re: delete operation with "where XXX in"

"Peter Alberer" <h9351252(at)obelix(dot)wu-wien(dot)ac(dot)at> writes:
> The following query takes quite long:
> delete from lr_object_usage where lr_object_usage_id in (
> select lr_object_usage_id from lr_locked_objects where timeout_time
> < now() and context is not null
> );

> to get the rows I want to delete into a select query I can simply use

> select * from lr_object_usage lrou inner join lr_locked_objects llo
> on llo.lr_object_usage_id = lrou.lr_object_usage_id
> where llo.timeout_time < now() ;

> But how can i rephrase the delete operation to get a fast delete
> operation?

If you don't mind a nonstandard query you can write

delete from lr_object_usage where
lr_locked_objects.lr_object_usage_id = lr_object_usage_id
and lr_locked_objects.timeout_time < now() ;

A difficulty with this is you can't use any alias names, but
except for cases involving self-joins you don't really need 'em.

regards, tom lane