Re: Double query (limit and offset)

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