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




view thread (14+ messages)  latest in thread

Message-ID: <1109881157.16326.91.camel@bluejay.goodinassociates.com>
Permalink:  ../1109881157.16326.91.camel@bluejay.goodinassociates.com/
Also on:    postgresql.org/message-id/1109881157.16326.91.camel@bluejay.goodinassociates.com

 · 

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: 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