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

query with results and also results size

From: Michael Swierczek <mike(dot)swierczek(at)gmail(dot)com>
To: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: query with results and also results size
Date: 2010-08-17 22:08:40
Message-ID: AANLkTimq0Xhi4=zWcx1sTmG9e6_TWuoGMhcAbcfhEED6@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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?

Thanks,
-Mike

Responses

pgsql-novice by date

Next:From: Thom BrownDate: 2010-08-17 22:26:00
Subject: Re: query with results and also results size
Previous:From: Charles Holleran Date: 2010-08-17 19:41:20
Subject: Re: update a table from a temp table

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