| 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: | Whole Thread | Raw Message | 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
| 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 | 
| 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 |