pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedupdate and IN vs. EXISTS
4+ messages / 3 participants
[nested] [flat]
* update and IN vs. EXISTS
@ 2003-02-01 11:40 pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 4+ messages in thread
From: pginfo @ 2003-02-01 11:40 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have 2 tables Table1 and Table2.
The PK for Table1 is declared as name.
Table 2 have only 1 field and it is also name ( it is indexed).
I will to update all Table1.filedForUpdate for all rows that exists in
Table2.
In Table1 I have ~ 120 000 rows and in Table2 I have ~ 100 000.
If I execute:
update Table1 set fieldForUpdate = 1;
it takes ~ 28 sec. I test it only to know how much time will I need for
all rows.
If I try to execute:
update Table1 set fieldForUpdate = 1 where ID IN (select T2.ID from
Table2);
it is running very slow.
I do not nkow how many time, but I waited ~ 30 min without to get
result.
I tested anoder query:
update Table1 set fieldForUpdate = 1 where ID IN ( select T1.ID from
Table1 T1 where exists (select * select T2.ID from Table2 where
T1.IDS=T2.IDS ));
and it was running > 30 min ( I do not know how many).
And the last query:
update Table1 set fieldForUpdate = 1 from Tablet T1 where EXISTS
(select * select T2.ID from Table2 where T1.IDS=T2.IDS );
and it was also > 30 min.
How can I speed up this update?
I have executed vacuum and vacuum full analyze.
redards,
ivan.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: update and IN vs. EXISTS
@ 2003-02-01 13:49 pginfo <pginfo@t1.unisoftbg.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 4+ messages in thread
From: pginfo @ 2003-02-01 13:49 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
Bruno Wolff III wrote:
> On Sat, Feb 01, 2003 at 12:40:00 +0100,
> pginfo <pginfo@t1.unisoftbg.com> wrote:
> >
> > If I try to execute:
> > update Table1 set fieldForUpdate = 1 where ID IN (select T2.ID from
> > Table2);
> > it is running very slow.
>
> You might try:
> update Table1 set fieldForUpdate = 1 from Table2 where Table1.id = Table2.id;
>
It is great.It takes 122 sec.
With IN it takes 8000 sec.
> This uses a nonstandard postgres extension and may not be portable, if that
> is a concern.
>
How to resolve the problem with the standart?regards,
iavn.
> INs are being speeded up in 7.4, so the original form might work a lot better
> in the next release.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: update and IN vs. EXISTS
@ 2003-02-01 14:54 Bruno Wolff III <bruno@wolff.to>
parent: pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 4+ messages in thread
From: Bruno Wolff III @ 2003-02-01 14:54 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: pgsql-sql
On Sat, Feb 01, 2003 at 12:40:00 +0100,
pginfo <pginfo@t1.unisoftbg.com> wrote:
>
> If I try to execute:
> update Table1 set fieldForUpdate = 1 where ID IN (select T2.ID from
> Table2);
> it is running very slow.
You might try:
update Table1 set fieldForUpdate = 1 from Table2 where Table1.id = Table2.id;
This uses a nonstandard postgres extension and may not be portable, if that
is a concern.
INs are being speeded up in 7.4, so the original form might work a lot better
in the next release.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: update and IN vs. EXISTS
@ 2003-02-03 08:37 Tambet Matiisen <t.matiisen@aprote.ee>
0 siblings, 0 replies; 4+ messages in thread
From: Tambet Matiisen @ 2003-02-03 08:37 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
> -----Original Message-----
> From: pginfo [mailto:pginfo@t1.unisoftbg.com]
> Sent: Saturday, February 01, 2003 3:50 PM
> To: Bruno Wolff III
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] update and IN vs. EXISTS
>
>
>
>
> Bruno Wolff III wrote:
>
> > On Sat, Feb 01, 2003 at 12:40:00 +0100,
> > pginfo <pginfo@t1.unisoftbg.com> wrote:
> > >
> > > If I try to execute:
> > > update Table1 set fieldForUpdate = 1 where ID IN
> (select T2.ID from
> > > Table2);
> > > it is running very slow.
> >
> > You might try:
> > update Table1 set fieldForUpdate = 1 from Table2 where
> Table1.id = Table2.id;
> >
>
> It is great.It takes 122 sec.
> With IN it takes 8000 sec.
>
> > This uses a nonstandard postgres extension and may not be
> portable, if that
> > is a concern.
> >
>
> How to resolve the problem with the standart?regards,
> iavn.
>
This should work as well:
update Table1 set fieldForUpdate = 1 where EXISTS
(select 1 from Table2 where Table1.IDS=Table2.IDS );
Tambet
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-02-03 08:37 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-01 11:40 update and IN vs. EXISTS pginfo <pginfo@t1.unisoftbg.com>
2003-02-01 14:54 ` Bruno Wolff III <bruno@wolff.to>
2003-02-01 13:49 ` pginfo <pginfo@t1.unisoftbg.com>
2003-02-03 08:37 Re: update and IN vs. EXISTS Tambet Matiisen <t.matiisen@aprote.ee>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox