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 3250D54FDF for ; Thu, 3 Mar 2005 20:19:31 +0000 (GMT) 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 84220-07 for ; Thu, 3 Mar 2005 20:19:19 +0000 (GMT) Received: from penguin.goodinassociates.com (penguin.goodinassociates.com [206.80.71.229]) by svr1.postgresql.org (Postfix) with ESMTP id 35B7352A6A for ; Thu, 3 Mar 2005 20:19:18 +0000 (GMT) Received: from [206.80.71.242] ([206.80.71.242]) by penguin.goodinassociates.com (8.12.11/8.12.11) with ESMTP id j23KJGsa010518; Thu, 3 Mar 2005 14:19:16 -0600 Subject: Re: name search query speed From: Jeremiah Jahn To: Josh Berkus Cc: postgres performance In-Reply-To: <200503030944.37743.josh@agliodbs.com> References: <1109867909.16326.58.camel@bluejay.goodinassociates.com> <200503030944.37743.josh@agliodbs.com> Content-Type: text/plain Date: Thu, 03 Mar 2005 14:19:17 -0600 Message-Id: <1109881157.16326.91.camel@bluejay.goodinassociates.com> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-1) Content-Transfer-Encoding: 7bit X-RAVMilter-Version: 8.4.4(snapshot 20030410) (penguin.goodinassociates.com) Received-SPF: none (penguin.goodinassociates.com: 206.80.71.242 is neither permitted nor denied by domain of cs.earlham.edu) client-ip=206.80.71.242; envelope-from=jeremiah@cs.earlham.edu; helo=[206.80.71.242]; X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/62 X-Sequence-Number: 10767 On Thu, 2005-03-03 at 09:44 -0800, Josh Berkus wrote: > Jeremiah, > > > I have about 5M names stored on my DB. Currently the searches are very > > quick unless, they are on a very common last name ie. SMITH. The Index > > is always used, but I still hit 10-20 seconds on a SMITH or Jones > > search, and I average about 6 searches a second and max out at about > > 30/s. Any suggestions on how I could arrange things to make this search > > quicker? I have 4gb of mem on a raid 5 w/ 3 drives. I'm hoping that I > > can increase this speed w/o a HW upgrade. > > First off, see http://www.powerpostgresql.com/PerfList about your > configuration settings. > > The problem you're running into with SMITH is that, if your query is going to > return a substantial number of rows (variable, but generally anything over 5% > of the table and 1000 rows) is not able to make effective use of an index. > This makes it fall back on a sequential scan, and based on you execution > time, I'd guess that the table is a bit too large to fit in memory. > > AFTER you've made the configuration changes above, AND run VACUUM ANALYZE on > your database, if you're still having problems post an EXPLAIN ANALYZE of the > query to this list. > ie. throw more hardware at it. All of the other things on the list, except for effective_cache_size have always been done. I bumped it up from the default to 2600000. Will see if that makes a difference. thanx, -jj- -- "A power so great, it can only be used for Good or Evil!" -- Firesign Theatre, "The Giant Rat of Summatra"