From: CoL <col@mportal.hu>
To: pgsql-performance@postgresql.org
Subject: Re: COUNT & Pagination
Date: Sun, 11 Jan 2004 21:01:18 +0100
Message-ID: <bts9p5$2nml$1@news.hub.org> (raw)
In-Reply-To: <01C3D82B.388A7FE0.david@shadovitz.com>
References: <01C3D82B.388A7FE0.david@shadovitz.com>
Hi,
David Shadovitz wrote, On 1/11/2004 7:10 PM:
> I understand that COUNT queries are expensive. So I'm looking for advice on
> displaying paginated query results.
>
> I display my query results like this:
>
> Displaying 1 to 50 of 2905.
> 1-50 | 51-100 | 101-150 | etc.
>
> I do this by executing two queries. One is of the form:
>
> SELECT <select list> FROM <view/table list> WHERE <filter> LIMIT m OFFSET n
>
> The other is identical except that I replace the select list with COUNT(*).
yes, you need 2 query. Or select it from one:
select *, (select count(*) from table) as count from table...
pg will optimize this query, and do the count only once
>
> And an unrelated question:
> I'm running PG 7.2.2 and want to upgrade to 7.4.1. I've never upgraded PG
> before and I'm nervous. Can I simply run pg_dumpall, install 7.4.1, and then
> feed the dump into psql? I'm planning to use pg_dumpall rather than pg_dump
> because I want to preserve the users I've defined. My database is the only one
> on the system.
yes. But check tha faq and the manual for a better explain.
C.
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-performance@postgresql.org
Cc: col@mportal.hu
Subject: Re: COUNT & Pagination
In-Reply-To: <bts9p5$2nml$1@news.hub.org>
* 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