Re: Speeding up subselect ?

Peter Alberer wrote:
> Hi there,
>
> i have the following query in a pl/pgsql procedure:
>
> update lr_object_usage
> set status = (case status
> when ''OPEN_SUCC'' then ''CLOSED_SUCC''
> when ''OPEN_FAIL'' then ''CLOSED_FAIL'' end)
> where lr_object_usage_id in (select lr_object_usage_id from
> lr_locked_objects where context = p_exam_usage_id);
>
> the problem is the result of the subselect is obviously calculated for
> every row of lr_object_usage (30K rows) -> the update takes very long.
> Here is the explain output with p_exam_usage_id being 38191. Two simple
> selects with the result of the first one being used in the second one
> vs. the subselect. Divided into two selects
>
> Can I somehow tell the planer not to requery the subselect for every row

Try to recast the subselect as a FROM clause subselect. E.g. will this work?

update lr_object_usage
set status = (case status
when ''OPEN_SUCC'' then ''CLOSED_SUCC''
when ''OPEN_FAIL'' then ''CLOSED_FAIL'' end)
from (select lr_object_usage_id
from lr_locked_objects
where context = p_exam_usage_id) as t1
where lr_object_usage.lr_object_usage_id = t1.lr_object_usage_id;

HTH,

Joe