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(*).
I'm looking for suggestions to replace that COUNT query. I cannot use the method of storing the number of records in a separate table because my queries (a) involve joins, and (b) have a WHERE clause.
Well, on all my sites, I do what you do and just live with it :P You can investigate using cursors however (DECLARE, MOVE & FETCH)
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.
I recommend something like this:
-- disable access to your database to make sure you have a complete dump
-- run dump as database owner account su pgsql (or whatever your postgres user is)
-- do compressed dump pg_dumpall > backup.sql
-- backup old data dir mv /usr/local/pgsql/data /usr/local/pgsql/data.7.2
-- remove old postgres, install new -- run NEW initdb. replace latin1 with your encoding -- -W specifies a superuser password initdb -D /usr/local/pgsql/data -E LATIN1 -W
-- restore dump, watching output VERY CAREFULLY: -- (run as pgsql user again) psql template1 < backup.sql > log.txt -- Watch stderr very carefully to check any errors that might occur.
-- If restore fails, re-initdb and re-restore
Chris
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
