Thanks for this post...
One thing I see often is people using the acs_permission.permission_p in where clauses. Oracle does not optimize this either (I also don't know about
other databases). This is very bad; if you must have this in a where clause wrap the rest of the query in a view on the fly with this call on the outside (to
minimize the number of rows you have to execute this on).
Postgres will not optimize these either.
Ironically, DanW and I have been discussing subqueries in from clauses, which are new for Postgres, just introduced in the current 7.1 version. I mentioned that I'd manage to greatly speed up a query in a personal project of mine that used PL/pgSQL functions by doing something similar to what you suggest - creating a "view on the fly" by selecting a slice of rows in a from-subquery, so the where clause calls the PL/pgSQL functions only for the relatively small number of rows that are returned.
So, yes, this technique can help.
