Hi, Tom. You wrote:
Hi, everyone. I've been using PostgreSQL for a decade, and it hasn't
failed me yet. But I've been having some serious performance problems
on a database that I've been using in my grad-school research group, and
it's clear that I need help from some more experienced hands.
What PG version are you using?
I've been using 8.0, 8.1, and 8.2 at various points, depending on which machine I've been using. My main machine is currently using 8.2.0. (I wish that I had control over which version was being used, but my sysadmin powers are inversely proportional to the computer power made available to me.
I'd try to think of a way to eliminate the function altogether in favor
of a single UPDATE command. In general, row-at-a-time thinking isn't
the way to win in SQL.
Well, I've tried to do massive UPDATEs as much as possible. But the patterns that we're looking for are basically of the variety, "If the user clicks on X and then clicks on Y, but without Z between the two of them, and if these are all part of the same simulation run, then we tag action X as being of interest to us." So it's oodles of keeping track of back-and-forth for each of the rows in the table, and looking forward and backward in the table.
I agree that row-at-a-time thinking isn't the best way to work, but I didn't see a good alternative for our purposes. I'm open to any and all suggestions.
Reuven
