pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.



view thread (14+ messages)  latest in thread

Message-ID: <d08inr$2fjb$1@news.hub.org>
Permalink:  ../d08inr$2fjb$1@news.hub.org/
Also on:    postgresql.org/message-id/d08inr$2fjb$1@news.hub.org

 · 

reply

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