pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeremiah Jahn <jeremiah@cs.earlham.edu>
To: John A Meinel <john@arbash-meinel.com>
Cc: postgres performance <pgsql-performance@postgresql.org>
Subject: Re: name search query speed
Date: Thu, 03 Mar 2005 14:14:47 -0600
Message-ID: <1109880887.16326.86.camel@bluejay.goodinassociates.com> (raw)
In-Reply-To: <42274D5A.1070402@arbash-meinel.com>
References: <1109867909.16326.58.camel@bluejay.goodinassociates.com>
	<42274D5A.1070402@arbash-meinel.com>

On Thu, 2005-03-03 at 11:46 -0600, John A Meinel wrote:
> 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.
> >
> >thanx,
> >-jj-
> >
> >
> >
> >  
> >
> It sounds like the problem is just that you have a lot of rows that need 
> to be returned. Can you just put a limit on the query? And then change 
> the client app to recognize when the limit is reached, and either give a 
> link to more results, or refine query, or something like that.
Not really, about 2% of the returned rows are thrown away for security
reasons based on the current user, security groups they belong to and
different flags in the data itself. So the count for this is generated
on the fly needed for pagination in the app which expresses the total
number of finds, but only displays 40 of them. If any one knows a way to
determine the total number of matches without needing to iterate through
them using jdbc, I'm all ears as this would save me huge amounts of time
and limit/offset would become an option. 

> 
> John
> =:->
> 
-- 
"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: <1109880887.16326.86.camel@bluejay.goodinassociates.com>
Permalink:  ../1109880887.16326.86.camel@bluejay.goodinassociates.com/
Also on:    postgresql.org/message-id/1109880887.16326.86.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, john@arbash-meinel.com
  Subject: Re: name search query speed
  In-Reply-To: <1109880887.16326.86.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