Pedro Alves wrote:
> Hi.
>
>
> I have a doubt:
>
> If I make:
>
> update stockline set status=3 where id IN (select id from lap_mpdetail_view where lap=3976)
>
> postgres makes an Seq Scan on stockline.
>
> when stockline is big, it is better to make an select id from
> lap_mpdetail_view where lap=3976) and programaticaly build the query of the
> type update stockline set status=3 where id=X or id=Y or...
>
>
> There must be a better way... EXISTS also make a seq scan
>
> update stockline set status=3 where id = (select id from
> lap_mpdetail_view where lap=3976); returns more than one tuple
1. I assume you have an index on stockline.id:
CREATE INDEX i_stockline1 ON stockline(id);
2. I've found the following syntax to perform better, although I'm not
sure of its portability:
UPDATE stockline SET status = 3
WHERE stockline.id = lap_mpdetail_view.id AND
lap_mpdetail_view.lap = 3976;
3. I assume you've executed VACUUM ANALYZE.
4. I assume that stockline is not a small table where the query
optimizer determined a sequential scan would be faster.
Hope that helps,
Mike Mascari
mascarm@mascari.com
