Forum OpenACS Development: Response to ACS 4.x won't scale (I hope I am wrong)

Collapse
Posted by Dave Hwang on

I've posted a trace file on three different queries at http://www.goliath.org/acs/trace-permissions.txt. The three queries you'll see are:

  1. group permission check using IN (ie. my version) (232 rows fetched)
  2. group permission check using JOIN (ie. Jon's version) (335512 rows fetched)
  3. group permission check using JOIN, without specifying privilege (ie. multirow select of all privileges of :grantee_id on :object_id) (561 rows fetched)

An interesting result is that query #3 is much faster than #2, which leads me to believe the real problem isn't with JOIN vs IN, but is with the way that "privilege" is indexed (or not). That's why I didn't include the COUNT(*) vs SELECT 1 FROM DUAL wrapper in the queries, because it doesn't seem relevant to the performance issue.

However, if someone could take a look at the trace file and help explain the results, I'd appreciate it, as I just learned how to do this since Don asked for it. =) Looking at the data model, I can't figure out where the problem is. It looks like privilege should an indexed column of acs_privileges, but since acs_object_grantee_priv_map is a view of a view, I can't really tell what's going on with privilege.