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