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 5941F52A88 for ; Fri, 4 Mar 2005 02:58:20 +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 46215-08 for ; Fri, 4 Mar 2005 02:58:11 +0000 (GMT) Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.250.190.2]) by svr1.postgresql.org (Postfix) with ESMTP id 4504852A72 for ; Fri, 4 Mar 2005 02:58:10 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 0EBDC31DA7; Fri, 4 Mar 2005 03:58:15 +0100 (MET) From: William Yu X-Newsgroups: pgsql.performance Subject: Re: name search query speed Date: Thu, 03 Mar 2005 18:55:55 -0800 Organization: Hub.Org Networking Services Lines: 21 Message-ID: References: <1109867909.16326.58.camel@bluejay.goodinassociates.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en In-Reply-To: <1109867909.16326.58.camel@bluejay.goodinassociates.com> To: pgsql-performance@postgresql.org 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/69 X-Sequence-Number: 10774 Jeremiah Jahn wrote: > 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. If it's just "SMITH", the only fix is to throw more hardware at the problem. I've got my own database of medical providers & facilities in the millions and anytime somebody tries to search for MEDICAL FACILITY, it takes forever. I've tried every optimization possible but when you have 500K records with the word "MEDICAL" in it, what can you do? You've got to check all 500K records to see if it matches your criteria. For multi-word searches, what I've found does work is to periodically generate stats on work frequencies and use those stats to search the least common words first. For example, if somebody enters "ALTABATES MEDICAL HOSPITAL", I can get the ~50 providers with ALTABATES in the name and then do a 2nd and 3rd pass to filter against MEDICAL and HOSPITAL.