Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 224CA6500D5 for ; Thu, 28 Aug 2008 18:14:07 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 84338-10 for ; Thu, 28 Aug 2008 18:14:00 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.186]) by postgresql.org (Postfix) with ESMTP id 6C8A964FF9E for ; Thu, 28 Aug 2008 18:14:03 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id c7so154695nfi.23 for ; Thu, 28 Aug 2008 14:14:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:in-reply-to:references :mime-version:content-type:message-id:cc:content-transfer-encoding :from:subject:date:to:x-mailer; bh=m/MDtN8ucC3Ap3xGnnopsqwRHP+k9EuZtvKdfSrWk6w=; b=vK75H2oYxnzSQOAk6ETeIOFDIt1Qd4ls75FuBMWeA8hZ7zCUszDNxaLFNc2PU5Dr6Z MpIZEFf89mB5fM9RLCWO+pIzTi5ufmG9oe38iFFbHUIu+KYwr/t1DpgyXaG5BhKdsTyG rcLqY+9lj8Ds4GHm9BK6iw4Gfs/8XriyBmLbo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=in-reply-to:references:mime-version:content-type:message-id:cc :content-transfer-encoding:from:subject:date:to:x-mailer; b=rl3J041Ad0LJRSYirUmAn4gw+iU2CMMvWO+j9VkbpsxbIVmdXXH29BdEEFtV+rIe0l qyj359Rd9lkkxA9ta1z/X7A0EYB2YMZ0rDrexM8ydA67mMwnjCbLDElz0LSunaOMWiOO RsCved+uCGvrlfNpL+o3T9Z9yMTu81LtcwAkM= Received: by 10.103.222.12 with SMTP id z12mr1333587muq.12.1219958040963; Thu, 28 Aug 2008 14:14:00 -0700 (PDT) Received: from ?192.168.3.100? ( [83.14.243.146]) by mx.google.com with ESMTPS id u9sm7152222muf.9.2008.08.28.14.13.58 (version=TLSv1/SSLv3 cipher=RC4-MD5); Thu, 28 Aug 2008 14:13:59 -0700 (PDT) In-Reply-To: <48B6FCF8.3090807@encs.concordia.ca> References: <48B6FCF8.3090807@encs.concordia.ca> Mime-Version: 1.0 (Apple Message framework v753.1) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Cc: pgsql-performance@postgresql.org Content-Transfer-Encoding: 7bit From: paul socha Subject: Re: update - which way quicker? Date: Thu, 28 Aug 2008 23:13:55 +0200 To: emilu@encs.concordia.ca X-Mailer: Apple Mail (2.753.1) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200808/391 X-Sequence-Number: 31272 On 2008-08-28, at 21:31, Emi Lu wrote: > Good morning, > > Tried to compare Table1 based on Table2 > > . update table1.col = false > if table1.pk_cols not in table2.pk_cols > > > > For the following two ways, (2) always performs better than (1) right, > and I need your inputs. > ====================================================================== > == > (1) update table1 > set col = false > where table1.pk_co1 || table1.pk_col2.... || table1.pk_colN > > NOT IN > > (select pk_co1 || pk_col2.... || pk_colN > from table2 > ) > > (2) ResultSet(rs) = > select pk_col1||pk_col2... || pk_colN > from table1 > left join table2 using (pk_col1..., pk_colN) > where table2.pk_col1 is null > > Then for each rs record, do: > update table1 > set col = false > where col1||... colN in rs.value > > Thanks a lot! > > -- > Sent via pgsql-performance mailing list (pgsql- > performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance Check EXISTS http://www.postgresql.org/docs/8.3/interactive/functions-subquery.html Serdecznie pozdrawiam Pawel Socha pawel.socha@gmail.com programista/administrator perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`- { a%%s%%$_%ee'