pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Find original number of rows before applied LIMIT/OFFSET?
2+ messages / 2 participants
[nested] [flat]

* Find original number of rows before applied LIMIT/OFFSET?
@ 2004-01-07 16:57  D. Dante Lorenso <dante@lorenso.com>
  0 siblings, 1 reply; 2+ messages in thread

From: D. Dante Lorenso @ 2004-01-07 16:57 UTC (permalink / raw)
  To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; 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@lorenso.com




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [PERFORM] Find original number of rows before applied LIMIT/OFFSET?
@ 2004-01-07 19:41  Greg Stark <gsstark@mit.edu>
  parent: D. Dante Lorenso <dante@lorenso.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Greg Stark @ 2004-01-07 19:41 UTC (permalink / raw)
  To: D. Dante Lorenso <dante@lorenso.com>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-performance


"D. Dante Lorenso" <dante@lorenso.com> writes:

> 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?

I tend to do it using a separate select count(*). My thinking is that the
count(*) query can be simplified and exclude things like the ORDER BY clause
and any select list entries that require extra work. It can often even exclude
whole joins.

By doing a separate query I can do that extra work only for the rows that i
actually need for display. Hopefully using an index to pull up those rows. And
do the count(*) in the most efficient way possible, probably a sequential scan
with no joins for foreign keys etc.

But I suspect the two methods both work out to suck about equally.

-- 
greg




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-01-07 19:41 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-07 16:57 Find original number of rows before applied LIMIT/OFFSET? D. Dante Lorenso <dante@lorenso.com>
2004-01-07 19:41 ` Greg Stark <gsstark@mit.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox