Обсуждение: slow down on UPDATE using IN statements
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
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/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato
Вложения
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/