Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id E7C0B2E00AD for ; Sat, 8 Mar 2008 13:31:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 88666-01 for ; Sat, 8 Mar 2008 13:31:32 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.183]) by postgresql.org (Postfix) with ESMTP id A5BEA2E00A3 for ; Sat, 8 Mar 2008 13:31:31 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so1404467waf.5 for ; Sat, 08 Mar 2008 09:31:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=3Q9o7YOLdDeTk8qy2cEwOOV6nhpdQcoW5sIuLV/5Jmo=; b=Y14z3u+ILrp+ylIB5Lh2axIJxzCLb0cUKe+sOHXjs4VXeaMzslPRr1Co9wHxxj6SORy+DDIFduyuIAGwuS0BnctSk6e+9aTE7fILOZUNuUgEFXFQGO05QfTT0lA+qJApYdRu8J8dyRSLwLsNdc/8YCsc9Z+USYgz+QNlS/JjoCE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=EPQUhpHnNXpgrIsa/HcHuz3PnHeCFeesCiDGVxpxiKPrF3PqOsn5CSsBzITMWgUK4OezfcgNylCT0HoIg87FIhG/RRWVt+IKahPf2VUdMmjtvIZjB3VRd3nvrB7RFXjiZ5BFzl01xHObRz2QIXVAE8dV2B3CMAtp4PgoV2OGUPI= Received: by 10.114.168.1 with SMTP id q1mr863318wae.74.1204997490976; Sat, 08 Mar 2008 09:31:30 -0800 (PST) Received: by 10.114.109.16 with HTTP; Sat, 8 Mar 2008 09:31:30 -0800 (PST) Message-ID: Date: Sat, 8 Mar 2008 12:31:30 -0500 From: "Kynn Jones" To: pgsql-performance@postgresql.org Subject: Joins and DELETE FROM MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_19365_21367109.1204997490980" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/182 X-Sequence-Number: 29136 ------=_Part_19365_21367109.1204997490980 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi! As part of a data warehousing project, I need to pre-process data downloaded from an external source, in the form of several large flat files. This preprocessing entails checking the validity of various data items, and discarding those that fail to pass all the checks. Currently, the code that performs the checks generates intermediate temporary tables of those bits of data that are invalid in some way. (This simplifies the process of generating various quality-control reports about the incoming data). The next step is to weed out the bad data from the main tables, and here's where I begin to get lost. To be concrete, suppose I have a table T consisting of 20 million rows, keyed on some column K. (There are no formal constrains on T at the moment, but one could define column K as T's primary key.) Suppose also that I have a second table B (for "bad") consisting of 20 thousand rows, and also keyed on some column K. For each value of B.K there is exactly one row in T such that T.K = B.K, and the task is to delete all these rows from T as efficiently as possible. My naive approach would something like DELETE FROM T WHERE T.K IN ( SELECT K FROM B ); ...which, according to EXPLAIN, is a terrible idea, because it involves sequentially scanning all 20 million rows of T just to delete about only 0.1% of them. It seems to me better to sequentially scan B and rely on an index on T to zero-in the few rows in T that must be deleted. Is this strategy something that can be done with plain SQL (even if to do this I must produce additional helper tables, indices, etc.), or must I write a stored procedure to implement it? TIA! Kynn ------=_Part_19365_21367109.1204997490980 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hi!

As part of a data warehousing project, I need to pre-process data downloaded from an external source, in the form of several large flat files.  This preprocessing entails checking the validity of various data items, and discarding those that fail to pass all the checks.

Currently, the code that performs the checks generates intermediate temporary tables of those bits of data that are invalid in some way.  (This simplifies the process of generating various quality-control reports about the incoming data).

The next step is to weed out the bad data from the main tables, and here's where I begin to get lost.

To be concrete, suppose I have a table T consisting of 20 million rows, keyed on some column K.  (There are no formal constrains on T at the moment, but one could define column K as T's primary key.)  Suppose also that I have a second table B (for "bad") consisting of 20 thousand rows, and also keyed on some column K.  For each value of B.K there is exactly one row in T such that T.K = B.K, and the task is to delete all these rows from T as efficiently as possible.

My naive approach would something like

DELETE FROM T WHERE T.K IN ( SELECT K FROM B );

...which, according to EXPLAIN, is a terrible idea, because it involves sequentially scanning all 20 million rows of T just to delete about only 0.1% of them.

It seems to me better to sequentially scan B and rely on an index on T to zero-in the few rows in T that must be deleted.

Is this strategy something that can be done with plain SQL (even if to do this I must produce additional helper tables, indices, etc.), or must I write a stored procedure to implement it?


TIA!

Kynn

------=_Part_19365_21367109.1204997490980--