A few things that I don't understand about this query (I did not try it in file-storage, just from looking at it here):
Isn't the and r.item_id = i.item_id redundant? The query will at most select one row anyway, because of the
and r.item_id = '7416'
and r.revision_id = i.live_revision
Does it bring any secret performance benefits, or is it just oversight?
Jun, the reason why the query is slow is propably because it executes the acs_permission__permission_p for much more rows than necessary. If the optimizer would choose to first narrow the rows using the other conditions in the where clause then it would be much faster, because the stored procedure would be executed only once at maximum.
My only suggestion for improving this is to extract most of the query into a subquery and wrap the query part with acs_permission__permission_p around it. Maybe there are other ways that I don't know of.
Throwing away rows at the tcl level is bad practice and I would vote against adding something like that to the toolkit, although in this particular case it is only one row that gets thrown away so not a big deal. Also note that it might work fast on oracle as it is.
