From: Janning Vygen <vygen@gmx.de>
To: pgsql-sql@postgresql.org
Subject: Re: Double query (limit and offset)
Date: Wed, 19 Oct 2005 09:34:22 +0200
User-Agent: KMail/1.7.2
Cc: Michael Landin Hostbaek <mich@freebsdcluster.org>
References: <20051011151105.GA29943@mich2.itxmarket.com>
In-Reply-To: <20051011151105.GA29943@mich2.itxmarket.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200510190934.22905.vygen@gmx.de>

Am Dienstag, 11. Oktober 2005 17:11 schrieb Michael Landin Hostbaek:
> List,
>
> I'm using the OFFSET / LIMIT combo in order to split up my query, so it
> only parses 20 rows at a time (for my php-scripted webpage).
>
> I'm using two queries; the first basically doing a select count(*) from
> [bla bla]; the second grabbing the actual data while setting LIMIT and
> OFFSET.
>
> In addition, I'm using the first query plus some calculations to parse
> total hits to the query, and number of pages etc etc.
>
> Now, my problem is this, the first query is simply counting all rows
> from the main table, whereas the second query has plenty of JOINS, and a
> GROUB BY statement - it's a fairly heavy query. The total (reported by
> the first query), it not at all the same as the amount of rows returned
> by the second query. I'd like to avoid having to run the "heavy" query
> twice, just in order to get the number of rows.
>
> Is there a smarter way of doing it ?

There is a smarter way of asking: Show us the queries!  
But it also depends on what you expect the user to do. 

Some hints:

In generell if you count table A and afterwards you join and group your tables 
A,B,C,D the number of rows in the resultset may vary, of course. 

- You could fetch ALL rows with the second query, count them (pg_numrows), 
show the first ten results and keep all other results in cache for the next 
webpage. (if we are talking about a smal set of rows not if we are talking 
about 1 billion rows, of course)

- You can rewrite your first query to return the correct number and see if it 
has a real performance impact. Optimize our query and you will be fine. 
Postgresql is very fast. 

- You can show the user an estimated count, if the correct number isn't of any 
interest (like google) 

- If you ever look at the CURSOR thing in postgresql and it looks attractive 
to you ( http://www.postgresql.org/docs/8.0/static/sql-fetch.html ): I think 
it isn't useful in a normal web environment, but it could be nice together 
with AJAX scripting.

kind regards,
janning