[to K C:] sorry, was out on vactation all last week. I was visualizing the problem incorrectly anyways...
Jim wrote: > That function is not immutable, it should be defined as stable. That is 100% correct: however now and then I declare stable functions as immutable in some cases because the planner treats them differently with no side effects...this is a hack of course...see my earlier suggestion to try both immutable and stable versions. I can give a pretty good example of when this can make a big difference. > PostgreSQL doesn't pre-compile functions, at least not until 8.1 (and > I'm not sure how much those are pre-compiled, though they are > syntax-checked at creation). Do you get the same result time when you > run it a second time? What time do you get from running just the > function versus the SQL in the function? plpgsql functions are at least partially compiled (sql functions afaik are not), in that a internal state is generated following the first execution. This is the cause of all those infernal 'invalid table oid' errors. > Also, remember that every layer you add to the cake means more work for > the database. If speed is that highly critical you'll probably want to > not wrap things in functions, and possibly not use views either. The overhead of the function/view is totally inconsequential next to the planner choosing a suboptimal plan. The purpose of the function is to coerce the planner into choosing the correct plan. > Also, keep in mind that getting below 1ms doesn't automatically mean > you'll be able to scale to 1000TPS. Things will definately change when > you load the system down, so if performance is that critical you should > start testing with the system under load if you're not already. ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org