From: Janning Vygen <vygen@gmx.de>
To: pgsql-sql@postgresql.org
Cc: Michael Landin Hostbaek <mich@freebsdcluster.org>
Subject: Re: Double query (limit and offset)
Date: Wed, 19 Oct 2005 09:34:22 +0200
Message-ID: <200510190934.22905.vygen@gmx.de> (raw)
In-Reply-To: <20051011151105.GA29943@mich2.itxmarket.com>
References: <20051011151105.GA29943@mich2.itxmarket.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: vygen@gmx.de, mich@freebsdcluster.org
Subject: Re: Double query (limit and offset)
In-Reply-To: <200510190934.22905.vygen@gmx.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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