Re: making multiple updates use indexes: howto?

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(at)mascari(dot)com