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: AANLkTin1gqTp6dKbnvjd+ZYcPhy1Cd3HpLqw3=FM6nNe@mail.gmail.com (view raw or flat)
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-2014 The PostgreSQL Global Development Group