From: Joel Fradkin <jfradkin@wazagua.com>
To: pgsql-sql@postgresql.org
Subject: getting count for a specific querry
Date: Fri, 08 Apr 2005 09:29:13 -0400
Message-ID: <000001c53c3e$f4f5b730$797ba8c0@jfradkin> (raw)
Per a thread a while back the discussion was along the lines of serving data
up to the web quick.
Our app currently pulls a bunch of data to several query pages.
I have tried and not gotten the queries to return as fast as they do now
which is a huge disappointment as the hardware is twice as powerful as our
current production. I can get it pretty close on some thing but others are
just plain slower.
My idea is to use the limit and offset to return just the first 50 records,
if they hit next I can set the offset.
My understanding was this gets slower as you move further into the data, but
we have several options to modify the search, and I do not believe our
clients will page very far intro a dataset.
One problem I think I will have though is they currently have count of the
records matching their request and I would like to keep that as a display
field
So given a table of associates my default query will be something like
Select * from tblassoc where clientnum = 'WAZ' and isdeleted is false
The user could add stuff like and where first name like 'Joel'
Currently it returns all records with a count and a display of the records
your viewing like 1-50 of 470, next page is 51-100 etc.
Is there a fast way to get the count? Will this concept fly? Also I am
getting heat that my search is now case sensitive. What is the best way to
get a case insensitive search? I could use ~* or perhaps do an
UPPER(firstname) in the select etc?
Thanks for any ideas here. I have tried playing with various settings and
have not seen my times change much, I will persue this on the performance
mailing list.
Joel Fradkin
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-sql@postgresql.org
Cc: jfradkin@wazagua.com
Subject: Re: getting count for a specific querry
In-Reply-To: <000001c53c3e$f4f5b730$797ba8c0@jfradkin>
* 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