At the risk of posting out-of-path, I'll mention a recent experience with a production site that runs on classic ACS4. We have about 100,000 acs_objects in our system and this caused the main file-storage page to become quite slow. As expected, the cost was due to the row permission query ("does the user have permission to read this directory and file?"). Our optimization was to force the permission query to run after reducing the number of rows with the other parts of the WHERE clase. I've posted further detail to the OpenACS4 design forum. The thread is here:
https://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=0003n5&topic_id=12&topic=OpenACS%204%2e0%20Design.
