X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D1DD59DCAA6 for ; Fri, 10 Feb 2006 17:32:59 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 16210-02 for ; Fri, 10 Feb 2006 17:33:01 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 7FEC69DC86D for ; Fri, 10 Feb 2006 17:32:57 -0400 (AST) 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 k1ALWufO001683; Fri, 10 Feb 2006 16:32:57 -0500 (EST) To: Bryce Nesbitt cc: pgsql-sql@postgresql.org Subject: Re: Very slow updates when using IN syntax subselect In-reply-to: <43ECFF0B.80905@obviously.com> References: <43ECFF0B.80905@obviously.com> Comments: In-reply-to Bryce Nesbitt message dated "Fri, 10 Feb 2006 13:00:59 -0800" Date: Fri, 10 Feb 2006 16:32:56 -0500 Message-ID: <1682.1139607176@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.108 required=5 tests=[AWL=0.108] X-Spam-Score: 0.108 X-Spam-Level: X-Archive-Number: 200602/121 X-Sequence-Number: 24131 Bryce Nesbitt writes: > update event set reconciled=true where event_id in > (select event_id from event join token using (token_number) > where token_status=50 and reconciled=false LIMIT 1); > On a 4 CPU machine, 2 CPU's peg at 100%, and the request just eats CPU > forever. What does EXPLAIN show for this and for the base query? > PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 This may be your problem right here. You are urgently in need of an update in any case --- there are a lot of nasty bugs fixed since 7.4.1: http://developer.postgresql.org/docs/postgres/release.html regards, tom lane