Обсуждение: delete to slow

Поиск
Список
Период
Сортировка

delete to slow

От
Ricardo Valença de Assis
Дата:
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

Re: delete to slow

От
Bruno Wolff III
Дата:
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;

Re: delete to slow

От
Tom Lane
Дата:
=?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

Re: delete to slow

От
Ricardo Valença de Assis
Дата:
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;


Re: delete to slow

От
"Jim C. Nasby"
Дата:
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?"

Re: delete to slow

От
Tom Lane
Дата:
=?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

Re: delete to slow

От
Ricardo Valença de Assis
Дата:
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?"


Re: delete to slow

От
"Jim C. Nasby"
Дата:
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?"

Re: delete to slow

От
Tom Lane
Дата:
"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

Re: delete to slow

От
Ricardo Valença de Assis
Дата:
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


Re: delete to slow

От
Bruno Wolff III
Дата:
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.

Re: delete to slow

От
Ricardo Valença de Assis
Дата:
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.


Re: delete to slow

От
"Jim C. Nasby"
Дата:
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?"

Re: delete to slow

От
"Jim C. Nasby"
Дата:
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?"

Re: delete to slow

От
Tom Lane
Дата:
"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

Re: delete to slow

От
"Jim C. Nasby"
Дата:
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?"

Re: delete to slow

От
"Spiegelberg, Greg"
Дата:
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)

Re: delete to slow

От
Tom Lane
Дата:
"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