X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7F14FD1B503 for ; Sat, 25 Oct 2003 21:16:24 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 80239-10 for ; Sat, 25 Oct 2003 18:15:54 -0300 (ADT) Received: from fuji.krosing.net (217-159-136-226-dsl.kt.estpak.ee [217.159.136.226]) by svr1.postgresql.org (Postfix) with ESMTP id 5F2D5D1B52B for ; Sat, 25 Oct 2003 18:15:50 -0300 (ADT) Received: from fuji.krosing.net (localhost.localdomain [127.0.0.1]) by fuji.krosing.net (8.12.8/8.12.8) with ESMTP id h9PLDheN004066; Sun, 26 Oct 2003 00:13:44 +0300 Received: (from hannu@localhost) by fuji.krosing.net (8.12.8/8.12.8/Submit) id h9PLDak0004064; Sun, 26 Oct 2003 00:13:36 +0300 X-Authentication-Warning: fuji.krosing.net: hannu set sender to hannu@tm.ee using -f Subject: Re: Performance Concern From: Hannu Krosing To: thebfh@toolsmythe.com Cc: john@pagakis.com, pgsql-performance@postgresql.org In-Reply-To: References: Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1067116415.3991.4.camel@fuji.krosing.net> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Sun, 26 Oct 2003 00:13:36 +0300 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/651 X-Sequence-Number: 4399 John Pagakis kirjutas L, 25.10.2003 kell 12:56: > I wrote a JAVA simulation of the above that did 1000 updates in 37 seconds. > That left me scratching my head because in psql when I did the > semi-equivalent: > > UPDATE baz SET customer_id = '1234' WHERE baz_key IN( SELECT baz_key FROM > baz WHERE customer_id IS NULL LIMIT 1000 ); try it this way, maybe it will start using an index : UPDATE baz SET customer_id = '1234' WHERE baz_key IN ( SELECT baz_key FROM baz innerbaz WHERE customer_id IS NULL and innerbaz.baz_key = baz.baz_key LIMIT 1000 ); you may also try to add a conditional index to baz: CREATE INDEX baz_key_with_null_custid_nxd ON baz WHERE customer_id IS NULL; to make the index access more efficient. ---------------- Hannu