Обсуждение: 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?"