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 F0427D1B488 for ; Tue, 3 Aug 2004 12:58:13 -0300 (ADT) 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 67696-09 for ; Tue, 3 Aug 2004 15:57:58 +0000 (GMT) Received: from mta2.rdslink.ro (mta2.rdslink.ro [193.231.236.124]) by svr1.postgresql.org (Postfix) with ESMTP id 56F5AD1B541 for ; Tue, 3 Aug 2004 12:54:36 -0300 (ADT) Received: (qmail 16166 invoked from network); 3 Aug 2004 18:49:20 -0000 Received: from unknown (HELO mail.rdslink.ro) (193.231.236.20) by mta2.rdslink.ro with DES-CBC3-SHA encrypted SMTP; 3 Aug 2004 18:49:20 -0000 Received: (qmail 7674 invoked from network); 3 Aug 2004 15:53:42 -0000 Received: from unknown (HELO ?81.196.83.211?) (81.196.83.211) by mail.rdslink.ro with SMTP; 3 Aug 2004 15:53:42 -0000 Subject: Re: NOT IN query takes forever From: Marius Andreiana To: pgsql-performance@postgresql.org In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB34101AF1A@Herge.rcsinc.local> References: <6EE64EF3AB31D5448D0007DD34EEB34101AF1A@Herge.rcsinc.local> Content-Type: text/plain Organization: Galuna S.R.L. Date: Tue, 03 Aug 2004 19:02:42 +0300 Message-Id: <1091548963.6915.2.camel@marte.biciclete.ro> Mime-Version: 1.0 X-Mailer: Evolution 1.5.9.1 (1.5.9.1-2) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200408/32 X-Sequence-Number: 7668 On Tue, 2004-08-03 at 08:05 -0400, Merlin Moncure wrote: > > Trying to run this query: > > EXPLAIN ANALYSE > > select * FROM trans > > WHERE query_id NOT IN (select query_id FROM query) > > > > but it will remain like that forever (cancelled after 30 min). > > explain analyze actually runs the query to do timings. Just run explain > and see what you come up with. More than likely there is a nestloop in > there which is causing the long query time. > > Try bumping up shared buffers some and sort mem as much as you safely > can. Thank you, that did it! With shared_buffers = 3000 # min 16, at least max_connections*2, 8KB each sort_mem = 128000 # min 64, size in KB it takes <3 seconds (my hardware is not server-class). -- Marius Andreiana Galuna - Solutii Linux in Romania http://www.galuna.ro