Обсуждение: delete to slow
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
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;
=?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
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;
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?"
=?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
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?"
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?"
"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
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
On Wed, Apr 06, 2005 at 15:23:57 -0300, Ricardo Valença de Assis <valenca@campusvirtual.br> wrote: > My question was: Can I use INNER JOIN with DELETE? If yes how? I gave you an example of how.
Yes. I saw. Thanks. But the both explanations takes a lot of time. ----- Original Message ----- From: "Bruno Wolff III" <bruno@wolff.to> To: "Ricardo Valença de Assis" <valenca@campusvirtual.br> Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; <pgsql-admin@postgresql.org> Sent: Wednesday, April 06, 2005 3:51 PM Subject: Re: delete to slow > On Wed, Apr 06, 2005 at 15:23:57 -0300, > Ricardo Valença de Assis <valenca@campusvirtual.br> wrote: > > My question was: Can I use INNER JOIN with DELETE? If yes how? > > I gave you an example of how.
On Wed, Apr 06, 2005 at 02:15:39PM -0400, Tom Lane wrote: > "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. Hmm... it would be pretty handy if there was a way to make triggers show up in explain somehow, maybe as a pseudo query node. -- 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?"
Are you going to do an explain analyze at some point? -- 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?"
"Jim C. Nasby" <decibel@decibel.org> writes:
> Hmm... it would be pretty handy if there was a way to make triggers show
> up in explain somehow, maybe as a pseudo query node.
Been there, done that ...
2005-03-25 16:57 tgl
* doc/src/sgml/perform.sgml, src/backend/catalog/pg_constraint.c,
src/backend/commands/copy.c, src/backend/commands/explain.c,
src/backend/commands/portalcmds.c, src/backend/commands/trigger.c,
src/backend/executor/execMain.c,
src/backend/executor/execProcnode.c,
src/backend/executor/functions.c,
src/backend/executor/instrument.c, src/backend/executor/spi.c,
src/backend/tcop/pquery.c, src/include/catalog/pg_constraint.h,
src/include/commands/trigger.h, src/include/executor/instrument.h,
src/include/nodes/execnodes.h: Improve EXPLAIN ANALYZE to show the
time spent in each trigger when executing a statement that fires
triggers. Formerly this time was included in "Total runtime" but
not otherwise accounted for. As a side benefit, we avoid
re-opening relations when firing non-deferred AFTER triggers,
because the trigger code can re-use the main executor's
ResultRelInfo data structure.
It's too big a change to consider back-patching into 8.0, unfortunately.
regards, tom lane
On Wed, Apr 06, 2005 at 06:28:26PM -0400, Tom Lane wrote: > It's too big a change to consider back-patching into 8.0, unfortunately. I'll happily settle for 8.1 over nothing... :) -- 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?"
Catching up on my email.
Would it be possible to perform a DELETE FROM table WHERE CURRENT OF mycursor?
Is this implemented in Postgres? I'm not seeing in in the manual for 7.4 or 8.
Greg
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Bruno Wolff III
Sent: Wednesday, April 06, 2005 1:32 PM
To: Ricardo Valença de Assis
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 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 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
"Spiegelberg, Greg" <gspiegelberg@cranel.com> writes:
> Would it be possible to perform a DELETE FROM table WHERE CURRENT OF mycursor?
> Is this implemented in Postgres? I'm not seeing in in the manual for 7.4 or 8.
It is (or at least ought to be) on the TODO list, but it's not done yet
and I don't think anyone's working on it.
A reasonably efficient way to fake it is to include CTID in the cursor
readout and issue a delete-by-ctid instead.
DECLARE c CURSOR FOR SELECT ctid, ... FROM mytable WHERE ...;
FETCH FROM c;
DELETE FROM mytable WHERE ctid = 'whatever';
This is more or less what would have to happen behind the scenes for
WHERE CURRENT OF to be implemented.
regards, tom lane