From valenca@campusvirtual.br Wed Apr 6 16:45:57 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 89600534B4 for ; Wed, 6 Apr 2005 17:45:57 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20625-01 for ; Wed, 6 Apr 2005 16:45:56 +0000 (GMT) Received: from uead1.campusvirtual.br (unknown [200.222.48.40]) by svr1.postgresql.org (Postfix) with ESMTP id 3F3F753245 for ; Wed, 6 Apr 2005 17:45:52 +0100 (BST) Received: from silvio (unknown [10.40.0.27]) by uead1.campusvirtual.br (Postfix) with SMTP id 56B65192736 for ; Wed, 6 Apr 2005 16:27:57 +0000 (UTC) Message-ID: <006701c53ac8$014b27e0$1b00280a@ugf.br> From: =?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= To: Subject: delete to slow Date: Wed, 6 Apr 2005 13:45:13 -0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0064_01C53AAE.DBE585E0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Campus_Virtual-UGF-MailScanner-Information: Please contact the ISP for more information X-Campus_Virtual-UGF-MailScanner: Found to be clean X-MailScanner-From: valenca@campusvirtual.br X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.525 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE, RCVD_IN_XBL, UPPERCASE_25_50 X-Spam-Level: * X-Archive-Number: 200504/76 X-Sequence-Number: 17089 This is a multi-part message in MIME format. ------=_NextPart_000_0064_01C53AAE.DBE585E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello Everybody! I=B4m 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=E7a UEAD - UGF ------=_NextPart_000_0064_01C53AAE.DBE585E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello Everybody!
 
    I=B4m trying to use = delete to=20 remove data from one table based on another. The query is = this:
 
    DELETE FROM table1 = WHERE column1=20 IN (SELECT column2 FROM table2);
 
    but my table is big, = so it takes=20 a lot o time...
    Is there a way to = use DELETE=20 with INNER JOIN in PostGreSQL?
 
Thanks!
 
Att.
Ricardo Valen=E7a
UEAD -=20 UGF
------=_NextPart_000_0064_01C53AAE.DBE585E0-- From tgl@sss.pgh.pa.us Wed Apr 6 17:27:49 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D1E5753541 for ; Wed, 6 Apr 2005 18:27:49 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 31090-10 for ; Wed, 6 Apr 2005 17:27:45 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 8A25F5353F for ; Wed, 6 Apr 2005 18:27:44 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j36HRgp5028472; Wed, 6 Apr 2005 13:27:43 -0400 (EDT) To: =?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= Cc: pgsql-admin@postgresql.org Subject: Re: delete to slow In-reply-to: <006701c53ac8$014b27e0$1b00280a@ugf.br> References: <006701c53ac8$014b27e0$1b00280a@ugf.br> Comments: In-reply-to =?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= message dated "Wed, 06 Apr 2005 13:45:13 -0300" Date: Wed, 06 Apr 2005 13:27:42 -0400 Message-ID: <28471.1112808462@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.11 tagged_above=0 required=5 tests=AWL, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200504/78 X-Sequence-Number: 17091 =?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= 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 From bruno@wolff.to Wed Apr 6 17:14:47 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5703F5353A for ; Wed, 6 Apr 2005 18:14:47 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 28761-02 for ; Wed, 6 Apr 2005 17:14:43 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id D729453245 for ; Wed, 6 Apr 2005 18:14:42 +0100 (BST) Received: (qmail 20048 invoked by uid 500); 6 Apr 2005 17:31:37 -0000 Date: Wed, 6 Apr 2005 12:31:37 -0500 From: Bruno Wolff III To: Ricardo =?iso-8859-1?Q?Valen=E7a?= de Assis Cc: pgsql-admin@postgresql.org Subject: Re: delete to slow Message-ID: <20050406173137.GA19739@wolff.to> Mail-Followup-To: Bruno Wolff III , Ricardo =?iso-8859-1?Q?Valen=E7a?= de Assis , pgsql-admin@postgresql.org References: <006701c53ac8$014b27e0$1b00280a@ugf.br> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <006701c53ac8$014b27e0$1b00280a@ugf.br> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.01 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/77 X-Sequence-Number: 17090 On Wed, Apr 06, 2005 at 13:45:13 -0300, Ricardo Valença de Assis 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; From valenca@campusvirtual.br Wed Apr 6 17:33:38 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 384A353355 for ; Wed, 6 Apr 2005 18:33:38 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32953-06 for ; Wed, 6 Apr 2005 17:33:33 +0000 (GMT) Received: from uead1.campusvirtual.br (unknown [200.222.48.40]) by svr1.postgresql.org (Postfix) with ESMTP id A427352808 for ; Wed, 6 Apr 2005 18:33:31 +0100 (BST) Received: from silvio (unknown [10.40.0.27]) by uead1.campusvirtual.br (Postfix) with SMTP id 76EAE192736; Wed, 6 Apr 2005 17:15:39 +0000 (UTC) Message-ID: <00e301c53ace$ab400260$1b00280a@ugf.br> From: =?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= To: "Bruno Wolff III" Cc: References: <006701c53ac8$014b27e0$1b00280a@ugf.br> <20050406173137.GA19739@wolff.to> Subject: Re: delete to slow Date: Wed, 6 Apr 2005 14:32:55 -0300 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Campus_Virtual-UGF-MailScanner-Information: Please contact the ISP for more information X-Campus_Virtual-UGF-MailScanner: Found to be clean X-MailScanner-From: valenca@campusvirtual.br X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.554 tagged_above=0 required=5 tests=AWL, RCVD_IN_XBL X-Spam-Level: * X-Archive-Number: 200504/79 X-Sequence-Number: 17092 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" To: "Ricardo Valença de Assis" Cc: 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 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; From decibel@decibel.org Wed Apr 6 17:56:52 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B0F9953332 for ; Wed, 6 Apr 2005 18:56:52 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 39254-10 for ; Wed, 6 Apr 2005 17:56:43 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by svr1.postgresql.org (Postfix) with ESMTP id DEDDC52808 for ; Wed, 6 Apr 2005 18:56:42 +0100 (BST) Received: by flake.decibel.org (Postfix, from userid 1001) id 9C33815487; Wed, 6 Apr 2005 12:56:41 -0500 (CDT) Date: Wed, 6 Apr 2005 12:56:41 -0500 From: "Jim C. Nasby" To: Ricardo Valen?a de Assis Cc: Bruno Wolff III , pgsql-admin@postgresql.org Subject: Re: delete to slow Message-ID: <20050406175641.GF93835@decibel.org> References: <006701c53ac8$014b27e0$1b00280a@ugf.br> <20050406173137.GA19739@wolff.to> <00e301c53ace$ab400260$1b00280a@ugf.br> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <00e301c53ace$ab400260$1b00280a@ugf.br> X-Operating-System: FreeBSD 4.11-RELEASE i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.8i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.021 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/80 X-Sequence-Number: 17093 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?" From tgl@sss.pgh.pa.us Wed Apr 6 17:58:04 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C1EAC53541 for ; Wed, 6 Apr 2005 18:58:04 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40699-07 for ; Wed, 6 Apr 2005 17:57:55 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id E60D253655 for ; Wed, 6 Apr 2005 18:57:54 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j36HvosH028664; Wed, 6 Apr 2005 13:57:51 -0400 (EDT) To: =?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= Cc: "Bruno Wolff III" , pgsql-admin@postgresql.org Subject: Re: delete to slow In-reply-to: <00e301c53ace$ab400260$1b00280a@ugf.br> References: <006701c53ac8$014b27e0$1b00280a@ugf.br> <20050406173137.GA19739@wolff.to> <00e301c53ace$ab400260$1b00280a@ugf.br> Comments: In-reply-to =?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= message dated "Wed, 06 Apr 2005 14:32:55 -0300" Date: Wed, 06 Apr 2005 13:57:50 -0400 Message-ID: <28663.1112810270@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/81 X-Sequence-Number: 17094 =?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= 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 From valenca@campusvirtual.br Wed Apr 6 18:00:28 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B69D552808 for ; Wed, 6 Apr 2005 19:00:28 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 42396-04 for ; Wed, 6 Apr 2005 17:59:53 +0000 (GMT) Received: from uead1.campusvirtual.br (unknown [200.222.48.40]) by svr1.postgresql.org (Postfix) with ESMTP id EA5595349F for ; Wed, 6 Apr 2005 18:59:51 +0100 (BST) Received: from silvio (unknown [10.40.0.27]) by uead1.campusvirtual.br (Postfix) with SMTP id A7D44192736; Wed, 6 Apr 2005 17:42:01 +0000 (UTC) Message-ID: <010101c53ad2$5a4d84a0$1b00280a@ugf.br> From: =?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= To: "Jim C. Nasby" Cc: "Bruno Wolff III" , References: <006701c53ac8$014b27e0$1b00280a@ugf.br> <20050406173137.GA19739@wolff.to> <00e301c53ace$ab400260$1b00280a@ugf.br> <20050406175641.GF93835@decibel.org> Subject: Re: delete to slow Date: Wed, 6 Apr 2005 14:59:18 -0300 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Campus_Virtual-UGF-MailScanner-Information: Please contact the ISP for more information X-Campus_Virtual-UGF-MailScanner: Found to be clean X-MailScanner-From: valenca@campusvirtual.br X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.614 tagged_above=0 required=5 tests=AWL, RCVD_IN_XBL X-Spam-Level: * X-Archive-Number: 200504/82 X-Sequence-Number: 17095 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" To: "Ricardo Valen?a de Assis" Cc: "Bruno Wolff III" ; 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?" From decibel@decibel.org Wed Apr 6 18:02:16 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3270253541 for ; Wed, 6 Apr 2005 19:02:16 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 43361-03 for ; Wed, 6 Apr 2005 18:02:05 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by svr1.postgresql.org (Postfix) with ESMTP id 277CB5349F for ; Wed, 6 Apr 2005 19:02:04 +0100 (BST) Received: by flake.decibel.org (Postfix, from userid 1001) id A717F153B1; Wed, 6 Apr 2005 13:02:04 -0500 (CDT) Date: Wed, 6 Apr 2005 13:02:04 -0500 From: "Jim C. Nasby" To: Ricardo Valen?a de Assis Cc: Bruno Wolff III , pgsql-admin@postgresql.org Subject: Re: delete to slow Message-ID: <20050406180204.GG93835@decibel.org> References: <006701c53ac8$014b27e0$1b00280a@ugf.br> <20050406173137.GA19739@wolff.to> <00e301c53ace$ab400260$1b00280a@ugf.br> <20050406175641.GF93835@decibel.org> <010101c53ad2$5a4d84a0$1b00280a@ugf.br> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <010101c53ad2$5a4d84a0$1b00280a@ugf.br> X-Operating-System: FreeBSD 4.11-RELEASE i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.8i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.021 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/84 X-Sequence-Number: 17097 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?" From tgl@sss.pgh.pa.us Wed Apr 6 18:15:45 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6878152D44 for ; Wed, 6 Apr 2005 19:15:45 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 46575-07 for ; Wed, 6 Apr 2005 18:15:43 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id E588E533B8 for ; Wed, 6 Apr 2005 19:15:42 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j36IFdnc028824; Wed, 6 Apr 2005 14:15:39 -0400 (EDT) To: "Jim C. Nasby" Cc: Ricardo Valen?a de Assis , Bruno Wolff III , pgsql-admin@postgresql.org Subject: Re: delete to slow In-reply-to: <20050406180204.GG93835@decibel.org> References: <006701c53ac8$014b27e0$1b00280a@ugf.br> <20050406173137.GA19739@wolff.to> <00e301c53ace$ab400260$1b00280a@ugf.br> <20050406175641.GF93835@decibel.org> <010101c53ad2$5a4d84a0$1b00280a@ugf.br> <20050406180204.GG93835@decibel.org> Comments: In-reply-to "Jim C. Nasby" message dated "Wed, 06 Apr 2005 13:02:04 -0500" Date: Wed, 06 Apr 2005 14:15:39 -0400 Message-ID: <28823.1112811339@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/85 X-Sequence-Number: 17098 "Jim C. Nasby" 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 From valenca@campusvirtual.br Wed Apr 6 18:24:39 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D062353333 for ; Wed, 6 Apr 2005 19:24:39 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 47939-09 for ; Wed, 6 Apr 2005 18:24:32 +0000 (GMT) Received: from uead1.campusvirtual.br (unknown [200.222.48.40]) by svr1.postgresql.org (Postfix) with ESMTP id F359852A3A for ; Wed, 6 Apr 2005 19:24:30 +0100 (BST) Received: from silvio (unknown [10.40.0.27]) by uead1.campusvirtual.br (Postfix) with SMTP id CE76E192736; Wed, 6 Apr 2005 18:06:40 +0000 (UTC) Message-ID: <013c01c53ad5$cbe8bdc0$1b00280a@ugf.br> From: =?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= To: "Tom Lane" Cc: "Bruno Wolff III" , References: <006701c53ac8$014b27e0$1b00280a@ugf.br> <20050406173137.GA19739@wolff.to> <00e301c53ace$ab400260$1b00280a@ugf.br> <28663.1112810270@sss.pgh.pa.us> Subject: Re: delete to slow Date: Wed, 6 Apr 2005 15:23:57 -0300 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Campus_Virtual-UGF-MailScanner-Information: Please contact the ISP for more information X-Campus_Virtual-UGF-MailScanner: Found to be clean X-MailScanner-From: valenca@campusvirtual.br X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.584 tagged_above=0 required=5 tests=AWL, RCVD_IN_XBL X-Spam-Level: * X-Archive-Number: 200504/86 X-Sequence-Number: 17099 My question was: Can I use INNER JOIN with DELETE? If yes how? ----- Original Message ----- From: "Tom Lane" To: "Ricardo Valença de Assis" Cc: "Bruno Wolff III" ; Sent: Wednesday, April 06, 2005 2:57 PM Subject: Re: [ADMIN] delete to slow > =?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= 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 From valenca@campusvirtual.br Wed Apr 6 18:38:54 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 44087534D9 for ; Wed, 6 Apr 2005 19:38:54 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 52827-07 for ; Wed, 6 Apr 2005 18:38:51 +0000 (GMT) Received: from uead1.campusvirtual.br (unknown [200.222.48.40]) by svr1.postgresql.org (Postfix) with ESMTP id 65A5B534D6 for ; Wed, 6 Apr 2005 19:38:50 +0100 (BST) Received: from silvio (unknown [10.40.0.27]) by uead1.campusvirtual.br (Postfix) with SMTP id 79D36192736; Wed, 6 Apr 2005 18:21:01 +0000 (UTC) Message-ID: <018101c53ad7$ccd1ac40$1b00280a@ugf.br> From: =?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= To: "Bruno Wolff III" Cc: "Tom Lane" , References: <006701c53ac8$014b27e0$1b00280a@ugf.br> <20050406173137.GA19739@wolff.to> <00e301c53ace$ab400260$1b00280a@ugf.br> <28663.1112810270@sss.pgh.pa.us> <013c01c53ad5$cbe8bdc0$1b00280a@ugf.br> <20050406185116.GA21847@wolff.to> Subject: Re: delete to slow Date: Wed, 6 Apr 2005 15:38:17 -0300 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Campus_Virtual-UGF-MailScanner-Information: Please contact the ISP for more information X-Campus_Virtual-UGF-MailScanner: Found to be clean X-MailScanner-From: valenca@campusvirtual.br X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.627 tagged_above=0 required=5 tests=AWL, RCVD_IN_XBL X-Spam-Level: * X-Archive-Number: 200504/88 X-Sequence-Number: 17101 Yes. I saw. Thanks. But the both explanations takes a lot of time. ----- Original Message ----- From: "Bruno Wolff III" To: "Ricardo Valença de Assis" Cc: "Tom Lane" ; 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 wrote: > > My question was: Can I use INNER JOIN with DELETE? If yes how? > > I gave you an example of how. From bruno@wolff.to Wed Apr 6 18:34:23 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D4A18534C8 for ; Wed, 6 Apr 2005 19:34:23 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 52998-04 for ; Wed, 6 Apr 2005 18:34:21 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id D63A6534C7 for ; Wed, 6 Apr 2005 19:34:20 +0100 (BST) Received: (qmail 21872 invoked by uid 500); 6 Apr 2005 18:51:16 -0000 Date: Wed, 6 Apr 2005 13:51:16 -0500 From: Bruno Wolff III To: Ricardo =?iso-8859-1?Q?Valen=E7a?= de Assis Cc: Tom Lane , pgsql-admin@postgresql.org Subject: Re: delete to slow Message-ID: <20050406185116.GA21847@wolff.to> Mail-Followup-To: Bruno Wolff III , Ricardo =?iso-8859-1?Q?Valen=E7a?= de Assis , Tom Lane , pgsql-admin@postgresql.org References: <006701c53ac8$014b27e0$1b00280a@ugf.br> <20050406173137.GA19739@wolff.to> <00e301c53ace$ab400260$1b00280a@ugf.br> <28663.1112810270@sss.pgh.pa.us> <013c01c53ad5$cbe8bdc0$1b00280a@ugf.br> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <013c01c53ad5$cbe8bdc0$1b00280a@ugf.br> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.01 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/87 X-Sequence-Number: 17100 On Wed, Apr 06, 2005 at 15:23:57 -0300, Ricardo Valença de Assis wrote: > My question was: Can I use INNER JOIN with DELETE? If yes how? I gave you an example of how. From decibel@decibel.org Wed Apr 6 22:16:32 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 61C3453A7E for ; Wed, 6 Apr 2005 23:16:32 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22310-09 for ; Wed, 6 Apr 2005 22:16:22 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by svr1.postgresql.org (Postfix) with ESMTP id 8227053A3B for ; Wed, 6 Apr 2005 23:16:18 +0100 (BST) Received: by flake.decibel.org (Postfix, from userid 1001) id 7E0D515487; Wed, 6 Apr 2005 17:16:20 -0500 (CDT) Date: Wed, 6 Apr 2005 17:16:20 -0500 From: "Jim C. Nasby" To: Tom Lane Cc: Ricardo Valen?a de Assis , Bruno Wolff III , pgsql-admin@postgresql.org Subject: Re: delete to slow Message-ID: <20050406221620.GJ93835@decibel.org> References: <006701c53ac8$014b27e0$1b00280a@ugf.br> <20050406173137.GA19739@wolff.to> <00e301c53ace$ab400260$1b00280a@ugf.br> <20050406175641.GF93835@decibel.org> <010101c53ad2$5a4d84a0$1b00280a@ugf.br> <20050406180204.GG93835@decibel.org> <28823.1112811339@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <28823.1112811339@sss.pgh.pa.us> X-Operating-System: FreeBSD 4.11-RELEASE i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.8i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.021 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/89 X-Sequence-Number: 17102 On Wed, Apr 06, 2005 at 02:15:39PM -0400, Tom Lane wrote: > "Jim C. Nasby" 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?" From decibel@decibel.org Wed Apr 6 22:17:23 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E9890539FA for ; Wed, 6 Apr 2005 23:17:23 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22486-09 for ; Wed, 6 Apr 2005 22:17:20 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by svr1.postgresql.org (Postfix) with ESMTP id 4A086533C5 for ; Wed, 6 Apr 2005 23:17:20 +0100 (BST) Received: by flake.decibel.org (Postfix, from userid 1001) id A928315486; Wed, 6 Apr 2005 17:17:22 -0500 (CDT) Date: Wed, 6 Apr 2005 17:17:22 -0500 From: "Jim C. Nasby" To: Ricardo Valen?a de Assis Cc: Bruno Wolff III , Tom Lane , pgsql-admin@postgresql.org Subject: Re: delete to slow Message-ID: <20050406221722.GK93835@decibel.org> References: <006701c53ac8$014b27e0$1b00280a@ugf.br> <20050406173137.GA19739@wolff.to> <00e301c53ace$ab400260$1b00280a@ugf.br> <28663.1112810270@sss.pgh.pa.us> <013c01c53ad5$cbe8bdc0$1b00280a@ugf.br> <20050406185116.GA21847@wolff.to> <018101c53ad7$ccd1ac40$1b00280a@ugf.br> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <018101c53ad7$ccd1ac40$1b00280a@ugf.br> X-Operating-System: FreeBSD 4.11-RELEASE i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.8i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.02 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/90 X-Sequence-Number: 17103 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?" From tgl@sss.pgh.pa.us Wed Apr 6 22:28:41 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9CD3253A6D for ; Wed, 6 Apr 2005 23:28:41 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26343-07 for ; Wed, 6 Apr 2005 22:28:35 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 41F1653ADA for ; Wed, 6 Apr 2005 23:28:33 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j36MSQUP006531; Wed, 6 Apr 2005 18:28:26 -0400 (EDT) To: "Jim C. Nasby" Cc: Ricardo Valen?a de Assis , Bruno Wolff III , pgsql-admin@postgresql.org Subject: Re: delete to slow In-reply-to: <20050406221620.GJ93835@decibel.org> References: <006701c53ac8$014b27e0$1b00280a@ugf.br> <20050406173137.GA19739@wolff.to> <00e301c53ace$ab400260$1b00280a@ugf.br> <20050406175641.GF93835@decibel.org> <010101c53ad2$5a4d84a0$1b00280a@ugf.br> <20050406180204.GG93835@decibel.org> <28823.1112811339@sss.pgh.pa.us> <20050406221620.GJ93835@decibel.org> Comments: In-reply-to "Jim C. Nasby" message dated "Wed, 06 Apr 2005 17:16:20 -0500" Date: Wed, 06 Apr 2005 18:28:26 -0400 Message-ID: <6530.1112826506@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/91 X-Sequence-Number: 17104 "Jim C. Nasby" 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 From decibel@decibel.org Thu Apr 7 05:00:24 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9719053B06 for ; Thu, 7 Apr 2005 06:00:24 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 33280-01 for ; Thu, 7 Apr 2005 05:00:19 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by svr1.postgresql.org (Postfix) with ESMTP id 6C28B5370F for ; Thu, 7 Apr 2005 06:00:17 +0100 (BST) Received: by flake.decibel.org (Postfix, from userid 1001) id BB8C51547C; Thu, 7 Apr 2005 00:00:16 -0500 (CDT) Date: Thu, 7 Apr 2005 00:00:16 -0500 From: "Jim C. Nasby" To: Tom Lane Cc: Ricardo Valen?a de Assis , Bruno Wolff III , pgsql-admin@postgresql.org Subject: Re: delete to slow Message-ID: <20050407050016.GN93835@decibel.org> References: <006701c53ac8$014b27e0$1b00280a@ugf.br> <20050406173137.GA19739@wolff.to> <00e301c53ace$ab400260$1b00280a@ugf.br> <20050406175641.GF93835@decibel.org> <010101c53ad2$5a4d84a0$1b00280a@ugf.br> <20050406180204.GG93835@decibel.org> <28823.1112811339@sss.pgh.pa.us> <20050406221620.GJ93835@decibel.org> <6530.1112826506@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <6530.1112826506@sss.pgh.pa.us> X-Operating-System: FreeBSD 4.11-RELEASE i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.8i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.019 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/95 X-Sequence-Number: 17108 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?" From gspiegelberg@cranel.com Thu Apr 21 13:09:21 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C85BB53920 for ; Thu, 21 Apr 2005 10:09:21 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 71393-09 for ; Thu, 21 Apr 2005 13:09:16 +0000 (GMT) Received: from COLSWEEPER.cranel.com (newmail.cranel.com [66.192.200.227]) by svr1.postgresql.org (Postfix) with ESMTP id AD15E53907 for ; Thu, 21 Apr 2005 10:09:14 -0300 (ADT) Received: from POSTOFFICE.cranel.local (exchsvr02.cranel.local) by COLSWEEPER.cranel.com (Content Technologies SMTPRS 4.3.14) with ESMTP id for ; Thu, 21 Apr 2005 09:07:59 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Subject: Re: delete to slow Date: Thu, 21 Apr 2005 09:09:06 -0400 Message-ID: <82E74D266CB9B44390D3CCE44A781ED9070C90@POSTOFFICE.cranel.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] delete to slow Thread-Index: AcU6zNfnw31IX3XdSi2zkhG5JAKcEALpfK3w From: "Spiegelberg, Greg" To: "Bruno Wolff III" , =?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= Cc: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/296 X-Sequence-Number: 17309 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 =20 -----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=E7a 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=E7a de Assis wrote: > Hello Everybody! >=20 > I=B4m trying to use delete to remove data from one table based on = another. The query is this: >=20 > DELETE FROM table1 WHERE column1 IN (SELECT column2 FROM table2); >=20 > 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 =3D 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) From tgl@sss.pgh.pa.us Sat Apr 23 23:53:45 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2250F537A0 for ; Sat, 23 Apr 2005 20:53:45 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26215-01 for ; Sat, 23 Apr 2005 23:53:39 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 87F7F5379A for ; Sat, 23 Apr 2005 20:53:39 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j3NNrehg023991; Sat, 23 Apr 2005 19:53:40 -0400 (EDT) To: "Spiegelberg, Greg" Cc: "Bruno Wolff III" , =?iso-8859-1?Q?Ricardo_Valen=E7a_de_Assis?= , pgsql-admin@postgresql.org Subject: Re: delete to slow In-reply-to: <82E74D266CB9B44390D3CCE44A781ED9070C90@POSTOFFICE.cranel.local> References: <82E74D266CB9B44390D3CCE44A781ED9070C90@POSTOFFICE.cranel.local> Comments: In-reply-to "Spiegelberg, Greg" message dated "Thu, 21 Apr 2005 09:09:06 -0400" Date: Sat, 23 Apr 2005 19:53:40 -0400 Message-ID: <23990.1114300420@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/349 X-Sequence-Number: 17362 "Spiegelberg, Greg" 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