Andrew,
I'm running the ACS 4.2 tarball from the ACS Repository, not OpenACS. To verify, I did a diff on my acs-permissions-create.sql and the one from the stock tarball, and they were identical.
I don't really understand how the permissions via relational segments is supposed to work. I'm not sure how to get rel_seg_approved_member_map.segment_id to get inserted as acs_object_grantee_priv_map.grantee_id so I can't run a similar query to what you have. Everything I do to try to create a relational segment just ends up populating group_approved_member_map instead.
I haven't run analyze or dbms_stats yet. I need to learn how to do that next. In terms of performance, the difference between IN and JOIN is over a second on my system, per call to acs_permission.permission_p. For a bboard thread of 10 messages, it takes over 10 seconds to return the page. Like you said, I don't have much data yet, but my site had gotten progressively worse as what little load I had increased.
I know the cartesian join is the problem. The data model is kind of hairy, but I think the problem lies somewhere in the fact that objects, privileges and grantees (ie. parties) all have a heirarchical structure. We already have to compare a privilege and all its super-privileges to an object and all the contexts that contain it, and when we throw groups into the mix (grantee and all its group memberships) using JOIN, we end up with a cartesian join, but by using IN, we avoid it by explicitly splitting the query in a way that the optimizer is not choosing on its own. That's just my guess, anyway.
