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@mascari.com

On Mon, Jul 07, 2003 at 05:58:00PM -0400, Mike Mascari wrote:

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;

Very un-portable. PGSQL is the only database I know of that will
construct a FROM clause for you on it's own. You should at least add

FROM lap_mpdetail_view

to the update. Even that isn't highly portable, though at least some
databases support that syntax. (Sybase and MS SQL come to mind).

--
Jim C. Nasby, Database Consultant jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"