From: William Yu <wyu@talisys.com>
To: pgsql-performance@postgresql.org
Subject: Re: name search query speed
Date: Thu, 03 Mar 2005 18:55:55 -0800
Message-ID: <d08inr$2fjb$1@news.hub.org> (raw)
In-Reply-To: <1109867909.16326.58.camel@bluejay.goodinassociates.com>
References: <1109867909.16326.58.camel@bluejay.goodinassociates.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: wyu@talisys.com
Subject: Re: name search query speed
In-Reply-To: <d08inr$2fjb$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox