X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 09B1E32AB6A for ; Thu, 21 Oct 2004 18:15:07 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 92384-08 for ; Thu, 21 Oct 2004 17:15:04 +0000 (GMT) Received: from davinci.ethosmedia.com (server226.ethosmedia.com [209.128.84.226]) by svr1.postgresql.org (Postfix) with ESMTP id 0A60B32AA72 for ; Thu, 21 Oct 2004 18:15:04 +0100 (BST) Received: from [63.195.55.98] (account josh@agliodbs.com HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 6538430; Thu, 21 Oct 2004 10:16:27 -0700 From: Josh Berkus Organization: Aglio Database Solutions To: pgsql-performance@postgresql.org, Victor Ciurus Subject: Re: Simple machine-killing query! Date: Thu, 21 Oct 2004 10:14:39 -0700 User-Agent: KMail/1.6.2 References: In-Reply-To: MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-Id: <200410211014.39916.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/360 X-Sequence-Number: 8836 Victor, > [explain] select * from BIGMA where string not in (select * from DIRTY); > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY PLAN > ------------------------------------------------------------------------ > =A0Seq Scan on bigma =A0(cost=3D0.00..24582291.25 rows=3D500 width=3D145) > =A0 =A0Filter: (NOT (subplan)) > =A0 =A0SubPlan > =A0 =A0 =A0-> =A0Seq Scan on dirty =A0(cost=3D0.00..42904.63 rows=3D25039= 63 width=3D82) This is what you call an "evil query". I'm not surprised it takes forever= ;=20 you're telling the database "Compare every value in 2.7 million rows of tex= t=20 against 2.5 million rows of text and give me those that don't match." The= re=20 is simply no way, on ANY RDBMS, for this query to execute and not eat all o= f=20 your RAM and CPU for a long time. You're simply going to have to allocate shared_buffers and sort_mem (about = 2GB=20 of sort_mem would be good) to the query, and turn the computer over to the= =20 task until it's done. And, for the sake of sanity, when you find the=20 200,000 rows that don't match, flag them so that you don't have to do this= =20 again. --=20 Josh Berkus Aglio Database Solutions San Francisco