Forum OpenACS Q&A: acs_object_party_privilege_map replacement

Collapse
1: acs_object_party_privilege_map replacement
Posted by Tom Jackson on 10/02/02 06:17 PM

When you look at attributes supplied by the acs_object_party_privilege_map view, you get the idea that it was designed for certain queries: like figuring out what privileges exist on a certain object for a certain party. However, I ran the following query: 

  select privilege from acs_object_party_privilege_map where 
  object_id = 6282 and party_id = 2581;
and got the following query plan (top lines only): 
  Subquery Scan acs_object_party_privilege_map 
(cost=685515.52..696024.06 rows=140114 width=148)
    ->  Unique  (cost=685515.52..696024.06 rows=140114 width=148)
          ->  Sort  (cost=685515.52..685515.52 rows=1401139 width=148)
which seems to indicate a very expensive query. With just the default installation of OACS, this query takes several minutes to complete.

My question is: can this seemingly important query be replaced with something along the lines of the way ad_permission_p was replaced? This query is, I guess, building a huge table and doing sequential scans of many tables that have indicies. Just looking for advice before I plow into this mess.

Collapse
2: Response to acs_object_party_privilege_map replacement (response to 1)
Posted by Don Baccus on 10/02/02 10:07 PM
It is expensive.  In some contexts the all_party_privilege_map can result in much better query plans (almost always in the PostgreSQL case).

This has to do with the use of UNIONs in the two views.  In general, UNIONs in subselects will hose the Oracle and PG optimizers in many cases.  Consider these two views definitive proof of that.  As you've noticed indexes are ignored.  There's no index on the *UNION* of the tables that themselves are indexed and the two RDBMSs seem incapable of "dipping down" into the UNION clauses and refactoring the subselect in a way that would make use of the indexes.

Whacking away at the permissions system is something I've put on my plate for 4.7.  I've got a bunch of ideas floating around but it's going to take a lot of playing with real datasets and query plans to get much traction on the problem - we can't just toss the permissions system overboard.

If you want to start whacking away at it I say "good luck" and "hooray" in the same breath.

Collapse
3: Response to acs_object_party_privilege_map replacement (response to 1)
Posted by Tom Jackson on 10/03/02 12:20 AM

Thanks Don. Jeff Davis emailed me with the same advice: use the all_object_party_privilege_map: 

> select privilege from all_object_party_privilege_map where
>  object_id = 6282 and party_id = 2581;

Nested Loop  (cost=0.00..30.35 rows=1 width=52)
  ->  Nested Loop  (cost=0.00..17.08 rows=1 width=40)
        ->  Nested Loop  (cost=0.00..15.05 rows=1 width=28)

I wasn't suggesting throwing the permission system out, not even! I just haven't in this particular case found the reason for the existence of this view, other than to confuse and baffle. If you can't inexpensively select this query, what can this view be used for? I tried specifying the exact privilege as well, but it still had the same cost.

Collapse
4: Response to acs_object_party_privilege_map replacement (response to 1)
Posted by Don Baccus on 10/03/02 01:25 AM
The all_party_privilege_map queries can be expensive, too.  "nested loop" means just what it says, and when you have nested loops nested a few deep you're far, far from the O(log2(N)) comfort zone.  We've been zapped with this on SSV2 ...

So unfortunately, with things as they are one must experiment.