Re: [PERFORM] COUNT & Pagination
On Sunday 11 January 2004 18:10, David Shadovitz wrote: > 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. If you only need the count when you've got the results, most PG client interfaces will tell you how many rows you've got. What language is your app in? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] annoying query/planner choice
Probably my best solution is to find a better way to produce the information, or cache it on the application side, as it doesn't actually change that much across client sessions. Clustering it occurred to me - it would have to be done on a frequent basis, as the contents of the table change constantly. What I am getting out of it with this operation doesn't change much, so caching in a separate table, in the application layer, or both would probably shortcut the whole problem. Always amazing what occurs to you when you sleep on it...if only I could take a good nap in the middle of the afternoon I would have no problems at all. On Jan 12, 2004, at 12:40 AM, Tom Lane wrote: Andrew Rawnsley <[EMAIL PROTECTED]> writes: I have a situation that is giving me small fits, and would like to see if anyone can shed any light on it. In general, pulling 10% of a table *should* be faster as a seqscan than an indexscan, except under the most extreme assumptions about clustering (is the table clustered on site_id, by any chance?). What I suspect is that the table is a bit larger than your available RAM, so that a seqscan ends up flushing all of the kernel's cache and forcing a lot of I/O, whereas an indexscan avoids the cache flush by not touching (quite) all of the table. The trouble with this is that the index only looks that good under test conditions, ie, when you repeat it just after an identical query that pulled all of the needed pages into RAM. Under realistic load conditions where different site_ids are being hit, the indexscan is not going to be as good as you think, because it will incur substantial I/O. You should try setting up a realistic test load hitting different random site_ids, and see whether it's really a win to force seqscan off for this query or not. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] COUNT & Pagination
> If you only need the count when you've got the results, most PG client > interfaces will tell you how many rows you've got. What language is your app > in? PHP. But I have only a subset of the results, retrieved via a query with a "LIMIT " clause, so $pg_numrows is m. And retrieving all results (i.e. no LIMIT) is at least as expensive as COUNT(*). -David ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] failures on machines using jfs
Spiegelberg, Greg kirjutas P, 11.01.2004 kell 18:21: > It would seem we're experiencing somthing similiar with our scratch > volume (JFS mounted with noatime). Which files/directories do you keep on "scratch" volume ? All postgres files or just some (WAL, tmp) ? - Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] failures on machines using jfs
Hannu Krosing wrote: Spiegelberg, Greg kirjutas P, 11.01.2004 kell 18:21: It would seem we're experiencing somthing similiar with our scratch volume (JFS mounted with noatime). Which files/directories do you keep on "scratch" volume ? All postgres files or just some (WAL, tmp) ? No Postgres files are kept in scratch only the files being loaded into the database via COPY or lo_import. My WAL logs are kept on a separate ext3 file system. Greg -- Greg Spiegelberg Sr. Product Development Engineer Cranel, Incorporated. Phone: 614.318.4314 Fax: 614.431.8388 Email: [EMAIL PROTECTED] Cranel. Technology. Integrity. Focus. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] COUNT & Pagination
On Mon, 2004-01-12 at 10:37, David Shadovitz wrote:
> > If you only need the count when you've got the results, most PG client
> > interfaces will tell you how many rows you've got. What language is your app
> > in?
>
> PHP.
> But I have only a subset of the results, retrieved via a query with a "LIMIT
> " clause, so $pg_numrows is m.
> And retrieving all results (i.e. no LIMIT) is at least as expensive as
> COUNT(*).
>
Depending on frequency of updates and need for real time info, you could
cache the count in session as long as the user stays within the given
piece of your app.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[PERFORM] Ignore Some Updates
Dear developers, I wonder it happens to systems where inefficient update SQL's are used like this: UPDATE MyTable SET MyColumn=1234 Question arises when the value of MyColumn is already 1234 before the update. If I am right, even when the to-be-updated column values equal to the new values, the core never hates to update that row anyway. If so, is it wise or not to adjust the core for lazy SQL users to ignore such "meaningless" updates in order to reduce some disk load and prevent some "holes" resulted from the delete (a consequence of update) in that table? Regards, CN ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Ignore Some Updates
"cnliou" <[EMAIL PROTECTED]> writes: > I wonder it happens to systems where inefficient update > SQL's are used like this: > UPDATE MyTable SET MyColumn=1234 > Question arises when the value of MyColumn is already 1234 > before the update. We have to fire UPDATE triggers in any case. > If I am right, even when the to-be-updated column values > equal to the new values, the core never hates to update that > row anyway. If so, is it wise or not to adjust the core for > lazy SQL users to ignore such "meaningless" updates in order Seems like penalizing the intelligent people (by adding useless comparisons) in order to reward the "lazy" ones. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
