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 D93615656C for ; Thu, 3 Mar 2005 17:45:30 +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 42923-08 for ; Thu, 3 Mar 2005 17:45:28 +0000 (GMT) Received: from davinci.ethosmedia.com (server227.ethosmedia.com [209.128.84.227]) by svr1.postgresql.org (Postfix) with ESMTP id 85A3D56463 for ; Thu, 3 Mar 2005 17:45:28 +0000 (GMT) X-EthosMedia-Virus-Scanned: no infections found 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 7088345; Thu, 03 Mar 2005 09:47:17 -0800 From: Josh Berkus Organization: Aglio Database Solutions To: Jeremiah Jahn Subject: Re: name search query speed Date: Thu, 3 Mar 2005 09:44:37 -0800 User-Agent: KMail/1.6.2 Cc: postgres performance References: <1109867909.16326.58.camel@bluejay.goodinassociates.com> In-Reply-To: <1109867909.16326.58.camel@bluejay.goodinassociates.com> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Message-Id: <200503030944.37743.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.011 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/58 X-Sequence-Number: 10763 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. -- Josh Berkus Aglio Database Solutions San Francisco