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 C5385D1B51E for ; Sat, 25 Oct 2003 15:08:41 +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 35022-03 for ; Sat, 25 Oct 2003 12:08:10 -0300 (ADT) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 7A60BD1B53B for ; Sat, 25 Oct 2003 12:08:09 -0300 (ADT) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 6BC4B3721F; Sat, 25 Oct 2003 11:08:04 -0400 (EDT) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 1ADQ1M-0006dV-00; Sat, 25 Oct 2003 11:08:04 -0400 To: Cc: , Subject: Re: Performance Concern References: In-Reply-To: From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 25 Oct 2003 11:08:03 -0400 Message-ID: <87wuattkx8.fsf@stark.dyndns.tv> Lines: 64 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/649 X-Sequence-Number: 4397 "John Pagakis" writes: > UPDATE baz SET customer_id = '1234' WHERE baz_key IN( SELECT baz_key FROM > baz WHERE customer_id IS NULL LIMIT 1000 ); Do an "explain analyze" on this query. I bet it's doing two sequential scans. Unfortunately in 7.3 the WHERE IN type of clause is poorly handled. If you're still in development perhaps you should move to the 7.4 beta as it should handle this much better: test74=> explain UPDATE test SET customer_id = 1 WHERE a IN (SELECT a FROM test WHERE customer_id IS NULL LIMIT 1000 ); QUERY PLAN --------------------------------------------------------------------------------- Nested Loop (cost=1447.26..2069.43 rows=201 width=10) -> HashAggregate (cost=1447.26..1447.26 rows=200 width=4) -> Subquery Scan "IN_subquery" (cost=0.00..1446.01 rows=501 width=4) -> Limit (cost=0.00..1441.00 rows=501 width=4) -> Seq Scan on test (cost=0.00..1441.00 rows=501 width=4) Filter: (customer_id IS NULL) -> Index Scan using ii on test (cost=0.00..3.10 rows=1 width=10) Index Cond: (test.a = "outer".a) (8 rows) However notice you still get at the one sequential scan. One way to help the situation would be to create a partial index WHERE customer_id IS NULL. This would especially help when things are almost completely sold out and available slots are sparse. slo=> explain UPDATE test SET customer_id = 1 WHERE a IN (SELECT a FROM test WHERE customer_id IS NULL LIMIT 1000 ); QUERY PLAN ------------------------------------------------------------------------------------------ Nested Loop (cost=181.01..803.18 rows=201 width=10) -> HashAggregate (cost=181.01..181.01 rows=200 width=4) -> Subquery Scan "IN_subquery" (cost=0.00..179.76 rows=501 width=4) -> Limit (cost=0.00..174.75 rows=501 width=4) -> Index Scan using i on test (cost=0.00..174.75 rows=501 width=4) Filter: (customer_id IS NULL) -> Index Scan using ii on test (cost=0.00..3.10 rows=1 width=10) Index Cond: (test.a = "outer".a) (8 rows) Notice the both sequential scans are gone and replaced by index scans. I kind of worry you might still have a race condition with the above query. Two clients could do the subquery and pick up the same records, then both run and update them. The database would lock the records until the first one commits but I don't think that would stop the second one from updating them a second time. Perhaps moving to serializable transactions would help this, I'm not sure. It's too bad the LIMIT clause doesn't work on UPDATEs. Then you could simply do: UPDATE baz SET customer_id = '1234' where customer_id IS NULL LIMIT 1000 Which shouldn't have to scan the table twice at all and I don't think suffer from any race conditions. -- greg