From: Jeremiah Jahn <jeremiah@cs.earlham.edu>
To: Josh Berkus <josh@agliodbs.com>
Cc: postgres performance <pgsql-performance@postgresql.org>
Subject: Re: name search query speed
Date: Thu, 03 Mar 2005 14:19:17 -0600
Message-ID: <1109881157.16326.91.camel@bluejay.goodinassociates.com> (raw)
In-Reply-To: <200503030944.37743.josh@agliodbs.com>
References: <1109867909.16326.58.camel@bluejay.goodinassociates.com>
<200503030944.37743.josh@agliodbs.com>
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"
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: jeremiah@cs.earlham.edu, josh@agliodbs.com
Subject: Re: name search query speed
In-Reply-To: <1109881157.16326.91.camel@bluejay.goodinassociates.com>
* 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