Обсуждение: Double query (limit and offset)

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 ? 

Thanks, 

/mich

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

Michael Landin Hostbaek wrote:
> 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).

The best way to do it is to have a layer between your application and 
the database that can cache the results of your query. Unfortunately PHP 
is not ideal for this - you might want to google for "php memcache" and 
"pgmemcache" though.

Once you can cache the query, you run it once, use the row-count and 
then fetch the rows from cache.
--  Richard Huxton  Archonet Ltd