Обсуждение: making multiple updates use indexes: howto?
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 Thanks -- Pedro Miguel G. Alves pmalves@think.pt THINK - Tecnologias de Informação www.think.pt Tel: +351 21 413 46 00 Av. José Gomes Ferreira Fax: +351 21 413 46 09 nº 13 1495-139 ALGÉS
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?"