Jun, I think the bottleneck is the call to acs_permission__permission_p (see the query fs::get_folder_contents in file-storage/tcl/file-storage-procs-{postgresql,oracle}.xql).
As for why package_id column in cr_folders is not used, there are various reasons. One is historical: the package_id column is a relatively recent addition, which did not exist in the original aD datamodel and therefore was not in the file-storage code (and port). The second is the way file-storage uses CR: a file-storage instance is an entire tree, identified by its root folder. Not all file-storage folders belong to separate packages (unlike the way ETP uses CR, for example), with the sole exception being the root folder. Since you only need to know the package_id for each root folder, this relationship is mapped in fs_root_folders table and all package_id entries in cr_folders are set to null (i.e., just not use or be aware that it is there). It should be easy to modify the code if you want to store package_id in cr_folders (perhaps to improve performance, but I doubt it). It seems to me though that using the fs_root_folders mapping is a cleaner way to do it (data normality, too?). How to use CR properly certainly qualifies as one area where the community should identify "best practices".
Finally, for file-storage to clean up (and in the future, archive smartly) the CR tree properly whenever APM deletes the file-storage instance, I put a trigger on fs_root_folders that gets activated whenever package_id is deleted. Yes, this could have been done for the package_id column in cr_folders, but when I did the port package_id was not part of cr_folders yet, and even if it was I still wouldn't do it because I feel file-storage should not be putting triggers on the CR datamodel (package isolation). Luke Pond pointed out that it would be nice if CR could do this (clean up/archive) for file-storage and ETP, but right now, not yet.
