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 37F4CD1B374 for ; Tue, 3 Aug 2004 15:25:22 -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 27583-09 for ; Tue, 3 Aug 2004 18:25:17 +0000 (GMT) Received: from mpls-qmqp-04.inet.qwest.net (mpls-qmqp-04.inet.qwest.net [63.231.195.115]) by svr1.postgresql.org (Postfix) with SMTP id 1C7DDD1B344 for ; Tue, 3 Aug 2004 15:25:14 -0300 (ADT) Received: (qmail 6619 invoked by uid 0); 3 Aug 2004 18:25:13 -0000 Received: from mpls-pop-12.inet.qwest.net (63.231.195.12) by mpls-qmqp-04.inet.qwest.net with QMQP; 3 Aug 2004 18:25:13 -0000 Received: from 63-227-127-37.dnvr.qwest.net (HELO ?10.0.0.2?) (63.227.127.37) by mpls-pop-12.inet.qwest.net with SMTP; 3 Aug 2004 18:25:12 -0000 Date: Tue, 03 Aug 2004 12:26:28 -0600 Message-Id: <1091557588.27166.94.camel@localhost.localdomain> From: "Scott Marlowe" To: "Merlin Moncure" Cc: "Marius Andreiana" , pgsql-performance@postgresql.org Subject: Re: NOT IN query takes forever In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB34101AF1C@Herge.rcsinc.local> References: <6EE64EF3AB31D5448D0007DD34EEB34101AF1C@Herge.rcsinc.local> Content-Type: text/plain Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-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/36 X-Sequence-Number: 7672 On Tue, 2004-08-03 at 10:10, Merlin Moncure wrote: > > > 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). > > Be careful...sort_mem applies to each connection and (IIRC) in some > cases more than once to a connection. Of all the configuration > parameters, sort_mem (IMO) is the most important and the hardest to get > right. 128k (or 128MB) is awfully high unless you have a ton of memory > (you don't) or you are running in single connection scenarios. Do some > experimentation by lowering the value until you get a good balance > between potential memory consumption and speed. Minor nit, sort_mem actually applies to EACH sort individually, so a query that had to run three sorts could use 3 x sort_mem. Note that one can set sort_mem per backend connection with set sort_mem=128000 if need be so as not to use up all the memory with other backends.