Re: SELECT LIMIT 1 VIEW Performance Issue

[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.