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
