slow down on UPDATE using IN statements

In versions 7.3 and before, IN was a dog compared to the equivalent EXISTS
statement. But given your timings, how long has it been since you did a
VACUUM ANALYZE FULL on your database?

Or REINDEX on any of the indexes?

Hope this helps,

On Mon, Nov 03, 2003 at 10:52:44AM -0300, MaRcElO PeReIrA wrote:

Hi there,

I was in troubles with a UPDATE+IN statement:

The following command use to take about 5 minutes to
be done:

UPDATE requisicao SET conclusao='3' WHERE reg IN
(SELECT reg FROM requisicao WHERE now()-data>'15
days');

The table 'requisicao' has only about 400 lines (!!).

If I change it to:

UPDATE requisicao SET conclusao='3' WHERE reg IN (12,
45, 87, 98, 129, 350, 389);

I have detected that the major problem isn't in the
amount of lines changed, but in the subselect.

How can I solve/optimize it? I would like to use the
IN, but in the last case I would make a software
change.

Thanks in advance and
Best regards,

Marcelo Pereira
Brazil

Yahoo! Mail - o melhor webmail do Brasil
http://mail.yahoo.com.br

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Mon, 3 Nov 2003, [iso-8859-1] MaRcElO PeReIrA wrote:

Hi there,

I was in troubles with a UPDATE+IN statement:

The following command use to take about 5 minutes to
be done:

UPDATE requisicao SET conclusao='3' WHERE reg IN
(SELECT reg FROM requisicao WHERE now()-data>'15
days');

The table 'requisicao' has only about 400 lines (!!).

If I change it to:

UPDATE requisicao SET conclusao='3' WHERE reg IN (12,
45, 87, 98, 129, 350, 389);

I have detected that the major problem isn't in the
amount of lines changed, but in the subselect.

How can I solve/optimize it? I would like to use the
IN, but in the last case I would make a software
change.

The easiest is wait for 7.4 where IN optimizes better than it has in the
past and see if that resolves the problem, otherwise, try
changing the query into an exists form.

Is the query below any different from what you are trying to accomplish.

UPDATE requisicao SET conclusao='3' WHERE now()-data>'15 days' ;

if you want to use the exact query more efficiently you may rewrite
using EXISTS as

UPDATE requisicao AS a SET conclusao='3' WHERE
EXISTS (select * from requisicao where reg=a.reg and
now()-data>'15 days' ) ;

Best Regards
Mallah.

Hi there,

I was in troubles with a UPDATE+IN statement:

The following command use to take about 5 minutes to
be done:

UPDATE requisicao SET conclusao='3' WHERE reg IN
(SELECT reg FROM requisicao WHERE now()-data>'15
days');

The table 'requisicao' has only about 400 lines (!!).

If I change it to:

UPDATE requisicao SET conclusao='3' WHERE reg IN (12,
45, 87, 98, 129, 350, 389);

I have detected that the major problem isn't in the
amount of lines changed, but in the subselect.

How can I solve/optimize it? I would like to use the
IN, but in the last case I would make a software
change.

Thanks in advance and
Best regards,

Marcelo Pereira
Brazil

Yahoo! Mail - o melhor webmail do Brasil
http://mail.yahoo.com.br

---------------------------(end of broadcast)--------------------------- TIP 5: Have you
checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

-----------------------------------------
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages.
http://www.trade-india.com/dyn/gdh/eyp/

Oops sorry AS is not allowed.

UPDATE requisicao AS a SET conclusao='3' WHERE
EXISTS (select * from requisicao where reg=a.reg and
now()-data>'15 days' ) ;

probably below will work.

UPDATE requisicao SET conclusao='3' WHERE
EXISTS (select * from requisicao as a where reg=requisicao.reg and
now()-data>'15 days' ) ;

Regds
Mallah.

Hi,

Is this right?? Can I use AS on UPDATE???

Regards,

Marcelo

--- mallah@trade-india.com escreveu: >

Is the query below any different from what you are
trying to accomplish.

UPDATE requisicao SET conclusao='3' WHERE
now()-data>'15 days' ;

if you want to use the exact query more efficiently
you may rewrite
using EXISTS as

UPDATE requisicao AS a SET conclusao='3' WHERE
EXISTS (select * from requisicao where reg=a.reg and
now()-data>'15 days' ) ;

Best Regards
Mallah.

Hi there,

I was in troubles with a UPDATE+IN statement:

The following command use to take about 5 minutes

to

be done:

UPDATE requisicao SET conclusao='3' WHERE reg IN
(SELECT reg FROM requisicao WHERE now()-data>'15
days');

The table 'requisicao' has only about 400 lines

(!!).

If I change it to:

UPDATE requisicao SET conclusao='3' WHERE reg IN

(12,

45, 87, 98, 129, 350, 389);

I have detected that the major problem isn't in

the

amount of lines changed, but in the subselect.

How can I solve/optimize it? I would like to use

the

IN, but in the last case I would make a software
change.

Thanks in advance and
Best regards,

Marcelo Pereira
Brazil

Yahoo! Mail - o melhor webmail do Brasil
http://mail.yahoo.com.br

---------------------------(end of

broadcast)--------------------------- TIP 5: Have
you

checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

-----------------------------------------
Over 1,00,000 exporters are waiting for your order!
Click below to get
in touch with leading Indian exporters listed in the
premier
trade directory Exporters Yellow Pages.
http://www.trade-india.com/dyn/gdh/eyp/

Yahoo! Mail - o melhor webmail do Brasil
http://mail.yahoo.com.br

-----------------------------------------
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages.
http://www.trade-india.com/dyn/gdh/eyp/