Postgres Pro
Downloads
Home   >   mailing lists

Thread: update and IN vs. EXISTS

update and IN vs. EXISTS

From
pginfo
Date:
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.

vacuum and serial primary keys

From
Carmen Marincu
Date:
Hello -

I am using Postgresql 7.1.3 and I have deleted 3000 rows from a table
(with DELETE).
Than I used vacuum <mytable> to actually delete the rows markes as deleted
by the DELETE command..
The trouble is that the "counter" for the serial primary key (ID field)
wasn't reset. So now althought I have only 2 rows in my table they have
the ID 3001 and 3002.

Is this normal ? If not could someone please explain me how could  I reset
the "counter" to ignore the deleted rows ?

Thank you very much
Carmen

Re: vacuum and serial primary keys

From
"D'Arcy J.M. Cain"
Date:
On Saturday 01 February 2003 07:45, Carmen Marincu wrote:
> I am using Postgresql 7.1.3 and I have deleted 3000 rows from a table
> (with DELETE).
> Than I used vacuum <mytable> to actually delete the rows markes as deleted
> by the DELETE command..
> The trouble is that the "counter" for the serial primary key (ID field)
> wasn't reset. So now althought I have only 2 rows in my table they have
> the ID 3001 and 3002.
>
> Is this normal ? If not could someone please explain me how could  I reset
> the "counter" to ignore the deleted rows ?

It is very normal.  The last thing you need is a database engine that changes
your primary key without an explicit command to do so.  In fact, sometimes I
think that the database should enforce the rule that primary keys are
immutable and not even allow it explicitely.  Perhaps a configuration option.

--
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Re: update and IN vs. EXISTS

From
Bruno Wolff III
Date:
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.

Re: update and IN vs. EXISTS

From
pginfo
Date:

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

Re: update and IN vs. EXISTS

From
"Tambet Matiisen"
Date:

> -----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