Find original number of rows before applied LIMIT/OFFSET?

From: "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>, pgsql-performance(at)postgresql(dot)org
Subject: Find original number of rows before applied LIMIT/OFFSET?
Date: 2004-01-07 16:57:38
Message-ID: 3FFC3A82.4000401@lorenso.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance


I need to know that original number of rows that WOULD have been returned
by a SELECT statement if the LIMIT / OFFSET where not present in the
statement.
Is there a way to get this data from PG ?

SELECT
... ;

----> returns 100,000 rows

but,

SELECT
...
LIMIT x
OFFSET y;

----> returns at most x rows

In order to build a list pager on a web site, I want to select 'pages' of a
result set at a time. However, I need to know the original select
result set
size because I still have to draw the 'page numbers' to display what
pages are
available.

I've done this TWO ways in the past:

1) TWO queries. The first query will perform a SELECT COUNT(*) ...; and
the second query performs the actualy SELECT ... LIMIT x OFFSET y;

2) Using PHP row seek and only selecting the number of rows I need.

Here is an example of method number 2 in PHP:

//----------------------------------------------------------------------
function query_assoc_paged ($sql, $limit=0, $offset=0) {
$this->num_rows = false;

// open a result set for this query...
$result = $this->query($sql);
if (! $result) return (false);

// save the number of rows we are working with
$this->num_rows = @pg_num_rows($result);

// moves the internal row pointer of the result to point to our
// desired offset. The next call to pg_fetch_assoc() would return
// that row.
if (! empty($offset)) {
if (! @pg_result_seek($result, $offset)) {
return (array());
};
}

// gather the results together in an array of arrays...
$data = array();
while (($row = pg_fetch_assoc($result)) !== false) {
$data[] = $row;

// After reading N rows from this result set, free our memory
// and return the rows we fetched...
if (! empty($limit) && count($data) >= $limit) {
pg_free_result($result);
return ($data);
}
}

pg_free_result($result);
return($data);
}

//----------------------------------------------------------------------

In this approach, I am 'emulating' the LIMIT / OFFSET features in PostgreSQL
by just seeking forward in the result set (offset) and only fetching the
number of rows that match my needs (LIMIT).

QUESTION: Is this the best way to do this, or is there a more efficient way
to get at the data I want? Is there a variable set in PG that tells me the
original number of rows in the query? Something like:

SELECT ORIG_RESULT_SIZE, ...
...
LIMIT x
OFFSET y;

Or can I run another select right afterwards...like:

SELECT ...
...
LIMIT x
OFFSET y;

SELECT unfiltered_size_of_last_query();

Any thoughts? Sure, the PHP function I'm using above 'works', but is it
the most efficient? I hope I'm not actually pulling all 100,000 records
across the wire when I only intend to show 10 at a time. See what I'm
getting at?

TIA,

Dante

---------
D. Dante Lorenso
dante(at)lorenso(dot)com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruno Wolff III 2004-01-07 17:10:25 Re: problems with transaction blocks
Previous Message Ericson Smith 2004-01-07 16:55:42 64 Bit Postgresql ports

Browse pgsql-performance by date

  From Date Subject
Next Message Eric Jain 2004-01-07 17:08:06 Index creation
Previous Message Tom Lane 2004-01-06 23:19:55 Re: Select max(foo) and select count(*) optimization