Re: name search query speed

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"