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 69492D1B4E5 for ; Tue, 3 Aug 2004 19:20:50 -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 13972-06 for ; Tue, 3 Aug 2004 22:20:47 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id E361BD1CEDC for ; Tue, 3 Aug 2004 14:48:25 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i73HmOqb047824 for ; Tue, 3 Aug 2004 17:48:24 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i73HSw54045070 for pgsql-performance@postgresql.org; Tue, 3 Aug 2004 17:28:58 GMT From: Gaetano Mendola X-Newsgroups: comp.databases.postgresql.performance Subject: Re: NOT IN query takes forever Date: Tue, 03 Aug 2004 19:28:27 +0200 Organization: PYRENET Midi-pyrenees Provider Lines: 48 Message-ID: <410FCB3B.5050300@bigfoot.com> References: <6EE64EF3AB31D5448D0007DD34EEB34101AF1A@Herge.rcsinc.local> <1091548963.6915.2.camel@marte.biciclete.ro> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@pyrenet.fr To: Marius Andreiana User-Agent: Mozilla Thunderbird 0.7.1 (Windows/20040626) X-Accept-Language: en-us, en In-Reply-To: <1091548963.6915.2.camel@marte.biciclete.ro> X-Enigmail-Version: 0.84.2.0 X-Enigmail-Supports: pgp-inline, pgp-mime To: pgsql-performance@postgresql.org 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: 200408/39 X-Sequence-Number: 7675 Marius Andreiana wrote: > 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 128 MB for sort_mem is too much, consider that in this way each backend can use 128 MB for sort operations... Also shared_buffers = 3000 means 24MB that is not balanced with the 128MB needed for sort... Try to bump up 128 MB for shared_buffer ( may be you need to instruct your OS to allow that ammount of shared memory usage ) and 24MB for sort_mem. Regards Gaetano Mendola