SEO

Hello Everybody!

I´m trying to use delete to remove data from one table based on another. The query is this:

DELETE FROM table1 WHERE column1 IN (SELECT column2 FROM table2);

but my table is big, so it takes a lot o time...
Is there a way to use DELETE with INNER JOIN in PostGreSQL?

Thanks!

Att.
Ricardo Valença
UEAD - UGF

On Wed, Apr 06, 2005 at 13:45:13 -0300,
Ricardo Valença de Assis <valenca@campusvirtual.br> wrote:
> Hello Everybody!
>
> I´m trying to use delete to remove data from one table based on another. The query is this:
>
> DELETE FROM table1 WHERE column1 IN (SELECT column2 FROM table2);
>
> but my table is big, so it takes a lot o time...
> Is there a way to use DELETE with INNER JOIN in PostGreSQL?

Yes. You should be able to do something like:
DELETE FROM table1 WHERE column1 = table2.column2;

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

=?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= <valenca@campusvirtual.br> writes:
> DELETE FROM table1 WHERE column1 IN (SELECT column2 FROM table2);

What PG version is this, and what does EXPLAIN say about that query?

> Is there a way to use DELETE with INNER JOIN in PostGreSQL?

You could do "DELETE FROM table1 WHERE column1 = table2.column2" but
that is not necessarily better.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

I´ve tried... It takes the same time. I used explain and I saw it... Exactly
the same time.
----- Original Message -----
From: "Bruno Wolff III" <bruno@wolff.to>
To: "Ricardo Valença de Assis" <valenca@campusvirtual.br>
Cc: <pgsql-admin@postgresql.org>
Sent: Wednesday, April 06, 2005 2:31 PM
Subject: Re: delete to slow


> On Wed, Apr 06, 2005 at 13:45:13 -0300,
> Ricardo Valença de Assis <valenca@campusvirtual.br> wrote:
> > Hello Everybody!
> >
> > I´m trying to use delete to remove data from one table based on
another. The query is this:
> >
> > DELETE FROM table1 WHERE column1 IN (SELECT column2 FROM table2);
> >
> > but my table is big, so it takes a lot o time...
> > Is there a way to use DELETE with INNER JOIN in PostGreSQL?
>
> Yes. You should be able to do something like:
> DELETE FROM table1 WHERE column1 = table2.column2;


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Wed, Apr 06, 2005 at 02:32:55PM -0300, Ricardo Valen?a de Assis wrote:
> I?ve tried... It takes the same time. I used explain and I saw it... Exactly
> the same time.

And what does explain show? Are statistics up to date?
--
Jim C. Nasby, Database Consultant decibel@decibel.org
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?"

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

=?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= <valenca@campusvirtual.br> writes:
> I´ve tried... It takes the same time. I used explain and I saw it... Exactly
> the same time.

Uh ... what I was asking for was for you to *show* us the EXPLAIN
output. You didn't answer the question about PG version either.
How do you expect help when you aren't providing any information?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

DATABASE=# explain delete from usuario where
usua_cd_usuario=backup.usua_cd_usuario;
QUERY PLAN
--------------------------------------------------------------------
Hash Join (cost=5.71..644.15 rows=298 width=6)
Hash Cond: ("outer".usua_cd_usuario = "inner".usua_cd_usuario)
-> Seq Scan on usuario (cost=0.00..516.64 rows=23764 width=10)
-> Hash (cost=4.97..4.97 rows=297 width=4)
-> Seq Scan on backup (cost=0.00..4.97 rows=297 width=4)
(5 rows)

DATABASE=# explain delete from usuario where usua_cd_usuario in (select
usua_cd_usuario from backup);
QUERY PLAN
--------------------------------------------------------------------
Hash IN Join (cost=5.71..644.15 rows=298 width=6)
Hash Cond: ("outer".usua_cd_usuario = "inner".usua_cd_usuario)
-> Seq Scan on usuario (cost=0.00..516.64 rows=23764 width=10)
-> Hash (cost=4.97..4.97 rows=297 width=4)
-> Seq Scan on backup (cost=0.00..4.97 rows=297 width=4)
(5 rows)

----- Original Message -----
From: "Jim C. Nasby" <decibel@decibel.org>
To: "Ricardo Valen?a de Assis" <valenca@campusvirtual.br>
Cc: "Bruno Wolff III" <bruno@wolff.to>; <pgsql-admin@postgresql.org>
Sent: Wednesday, April 06, 2005 2:56 PM
Subject: Re: [ADMIN] delete to slow


> On Wed, Apr 06, 2005 at 02:32:55PM -0300, Ricardo Valen?a de Assis wrote:
> > I?ve tried... It takes the same time. I used explain and I saw it...
Exactly
> > the same time.
>
> And what does explain show? Are statistics up to date?
> --
> Jim C. Nasby, Database Consultant decibel@decibel.org
> 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?"


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Wed, Apr 06, 2005 at 02:59:18PM -0300, Ricardo Valen?a de Assis wrote:
> DATABASE=# explain delete from usuario where
> usua_cd_usuario=backup.usua_cd_usuario;
> QUERY PLAN
> --------------------------------------------------------------------
> Hash Join (cost=5.71..644.15 rows=298 width=6)
> Hash Cond: ("outer".usua_cd_usuario = "inner".usua_cd_usuario)
> -> Seq Scan on usuario (cost=0.00..516.64 rows=23764 width=10)
> -> Hash (cost=4.97..4.97 rows=297 width=4)
> -> Seq Scan on backup (cost=0.00..4.97 rows=297 width=4)
> (5 rows)
>
> DATABASE=# explain delete from usuario where usua_cd_usuario in (select
> usua_cd_usuario from backup);
> QUERY PLAN
> --------------------------------------------------------------------
> Hash IN Join (cost=5.71..644.15 rows=298 width=6)
> Hash Cond: ("outer".usua_cd_usuario = "inner".usua_cd_usuario)
> -> Seq Scan on usuario (cost=0.00..516.64 rows=23764 width=10)
> -> Hash (cost=4.97..4.97 rows=297 width=4)
> -> Seq Scan on backup (cost=0.00..4.97 rows=297 width=4)
> (5 rows)

Well, neither of those should take very long at all. What's EXPLAIN
ANALYZE show?
--
Jim C. Nasby, Database Consultant decibel@decibel.org
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?"

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

"Jim C. Nasby" <decibel@decibel.org> writes:
> Well, neither of those should take very long at all. What's EXPLAIN
> ANALYZE show?

Indeed ... now I'm wondering about foreign key checks. Are there any
tables with foreign keys linking to usuario? If so, the problem is
likely unindexed foreign key columns, or maybe a datatype mismatch
between foreign key and referenced column.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

My question was: Can I use INNER JOIN with DELETE? If yes how?

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Ricardo Valença de Assis" <valenca@campusvirtual.br>
Cc: "Bruno Wolff III" <bruno@wolff.to>; <pgsql-admin@postgresql.org>
Sent: Wednesday, April 06, 2005 2:57 PM
Subject: Re: [ADMIN] delete to slow


> =?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= <valenca@campusvirtual.br>
writes:
> > I´ve tried... It takes the same time. I used explain and I saw it...
Exactly
> > the same time.
>
> Uh ... what I was asking for was for you to *show* us the EXPLAIN
> output. You didn't answer the question about PG version either.
> How do you expect help when you aren't providing any information?
>
> regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster