Skip site navigation (1) Skip section navigation (2)

Re: query with results and also results size

From: Thom Brown <thom(at)linux(dot)com>
To: Michael Swierczek <mike(dot)swierczek(at)gmail(dot)com>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: query with results and also results size
Date: 2010-08-17 22:26:00
Message-ID: (view raw or whole thread)
Lists: pgsql-novice
On 17 August 2010 23:08, Michael Swierczek <mike(dot)swierczek(at)gmail(dot)com> wrote:
> I query a view based upon a number of user-input criteria.  The
> results are displayed to the user 10 rows at a time.  In my page view
> for the end user, I want to list "search results, records X through X
> + 9 out of (total)".
> Right now every time a person clicks through a page, the page request
> results in two queries.  One query gets the count of the total number
> of records that meet the search criteria.  The second query gets the
> 10 records for the current page.
> I want to reduce the number of round trips to the database, and if
> possible stop from performing the search twice.
> I can combine the two queries like this:
> select first.*, second.total_count
> (select * from patient_data_view where (...) order by ... offset _
> limit 10) first
> join
> (select count(id) as "total_count" from patient_data_view where (...))
> second  on true;
> Doing it that way moves from two trips between the application and the
> database to one, but that one query still has two searches based upon
> the same criteria in it.
> I cannot store the count associated with each search between page
> views, because there are other people accessing the system
> concurrently and the number of records meeting the search criteria can
> change.
> Is there a way to write the query so that it does not perform the search twice?

How about using LIMIT 11?  That way you can display 10, but if you
count 11, you can provide a "next" link.

Thom Brown
Registered Linux user: #516935

In response to

pgsql-novice by date

Next:From: STADate: 2010-08-18 03:39:14
Subject: List of User Defined Types?
Previous:From: Michael SwierczekDate: 2010-08-17 22:08:40
Subject: query with results and also results size

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group