Forum OpenACS Development: OpenACS Permissions System - Performance Improvement Work
I started by reading the documentation and some existing code and came to the conclusion that the preferred method within sql up to now has been:
select 1 from acs_object_party_privilege_map ppm where ppm.party_id=x and ppm.privilege='foo' and ppm.object_id=y;
I read a forum post in which it was noted that the PostgreSQL optimiser seems to be making different decisions in PostgreSQL 7.4.x from those made in earlier versions which have resulted in slower performance during permissions lookups.
For the purposes of this document I have performed all of the following queries on an x686 650Mhz laptop. The absolute times reported therefore do not reflect the real world performance of an OpenACS instance on an industrial grade server platform but rather provide an indication of relative speed. My intention here is to float some ideas which I hope will stimulate discussion and improve performance. I expect there to be flaws in some of my assumptions and I suspect that there may be some errors/bugs in my queries - please shoot me down in flames if I deserve it! 😊
I began by running a benchmark:
TEST A-1)
explain analyse select 1 from acs_object_party_privilege_map ppm where ppm.party_id=448 and ppm.privilege='admin' and ppm.object_id=16631;
Total runtime: 459.076 ms
[The party_id 448 is site-wide-admin and so has many descendent privileges. This impacts performace because of the larger number of rows returned from the acs_object_party_privilege_map view.]
TEST B-1)
explain analyse select 1 from acs_object_party_privilege_map ppm where ppm.party_id=-1 and ppm.privilege='admin' and ppm.object_id=16631;
Total runtime: 173.728 ms
[The party_id -1 is 'the public' and has no aggregated permissions on the system. There are also no explicit permissions set for this party_id.]
As things stand it seems that the use of the acs_object_party_privilege_map view works best when there are simple cascading permissions. This is well suited to sites with a high volume of unregistered visitors but may be less than ideal in a tightly controlled corporate application where many explicit permissions are to be granted.
Based on this conclusion I became comcerned that if I proceed to implement permissions in the Project Manager at the level of projects and tasks, because of the potential number of explicitly granted permissions, I might create a significant performance issue.
I was interested in the design decision that led to the creation of acs_object_party_privilege_map as a view rather than as a table maintained by triggers. I have come to the conclusion that the reason that this works well is that the PostgreSQL optimiser is smart enough to only create enough of the total dataset to satisfy the requirements of the query so that the entire view does not ever have to be created or stored. However it is the behaviour of the optimiser that we do not have control over and this has led to frustrations in the past. I wondered whether we could change the structure of our request such that we guide the optimiser more firmly towards the execution plan that we believe is appropriate.
So I decided to attempt to replicate the functionality of the above without using the acs_object_party_privilege view at all. I decided that the following structure might be appropriate:
select 1 from acs_permissions
where object_id in (...)
and grantee_id in (...)
and privilege in (...);
The sub-selects would each return a small dataset (ideally by index scanning) to the main query which would then only need to join a modest number of rows. Gone would be the need for dynamically creating portions of the enormous flattened combination hierarchy.
Here is the full experimental query:
select 1 from acs_permissions
where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin')
and object_id in (select ancestor_id from acs_object_context_index where object_id=16631)
and (grantee_id in (select group_id from group_member_index where member_id=448) or grantee_id=448);
[The 'or' clause is required because the group_member_index does not hold an object as an ancestor or itself as do the other flattened hierarchies.]
Here are the test results to solve the same problem as benchmarked above:
TEST A-2)
explain analyse select 1 from acs_permissions where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin') and object_id in (select ancestor_id from acs_object_context_index where object_id=16631) and (grantee_id in (select group_id from group_member_index where member_id=448) or grantee_id=448);
Total runtime: 444.026 ms
TEST B-2)
explain analyse select 1 from acs_permissions where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin') and object_id in (select ancestor_id from acs_object_context_index where object_id=16631) and (grantee_id in (select group_id from group_member_index where member_id=-1) or grantee_id=-1);
Total runtime: 104.635 ms
The result is a similar TEST A result but a significantly faster TESTB at a little over half the time.
I was disappointed by this but since it is the handling of views that seems to give rise to the variance between versions of PostgreSQL I was keen to find a way to improve things.
Since we only ever want a true or false result from a permissions request, it ocurred to me that we might make use of the 'LIMIT 1' construct. Perhaps the optimiser will be clever enough to do what Perl does when executing boolean statements and break of execution as soon as the condition has been fulfilled.
Here are the results using limit with my experimental query:
TEST A-3)
explain analyse select 1 from acs_permissions where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin') and object_id in (select ancestor_id from acs_object_context_index where object_id=16631) and (grantee_id in (select group_id from group_member_index where member_id=448) or grantee_id=448) limit 1;
Total runtime: 11.749 ms
TEST B-3)
explain analyse select 1 from acs_permissions where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin') and object_id in (select ancestor_id from acs_object_context_index where object_id=16631) and (grantee_id in (select group_id from group_member_index where member_id=-1) or grantee_id=-1) limit 1;
Total runtime: 104.808 ms
Aha! 😊
No change to the time taken for a search when there are no permissions entries for obvious reasons but a very significant improvement for queries involving users with lots of aggregated permissions.
So now the results for using 'LIMIT 1' with the original query:
TEST A-4)
explain analyse select 1 from acs_object_party_privilege_map ppm where ppm.party_id=448 and ppm.privilege='admin' and ppm.object_id=16631 limit 1;
Total runtime: 0.891 ms
TEST B-4)
explain analyse select 1 from acs_object_party_privilege_map ppm where ppm.party_id=-1 and ppm.privilege='admin' and ppm.object_id=16631 limit 1;
Total runtime: 393.342 ms
Double WOW! 😊 ...the query for party_id 448 is another 10x faster, however the query for 'the public' is of course no quicker.
This seems to throw up the following thoughts which I have summarised below and would welcome comments on.
1) Perhaps we could employ these different query structures depending upon the anticipated situation. For example, in admin pages or in the project manager application (where documents are to be restricted to a small number of parties) we could use the query shown in example TEST 4-A, whilst for nodes more likely to be viewed publically and edited infrequently (such as a news item) it may be more appropriate to use the structure proposed in TEST B-2.
2) Analysis of the query execution plans reveals an opportunity for performance improvement by recreating the privilege hierarchy table to utilise integer keys. Because the acs_privilege_descendent_map table is currently in plain english, the query plan calls for a 'text filter' comparison on every row in the nested loop. In the documentation for PostgreSQL it states that Unicode support significantly impacts performance. Since the internationalisation support in recent versions of OpenACS requires Unicode to be compiled into PostgreSQL, it could be that there are gains to be made here by eliminating the need for unicode supporting text filters in the permissions infrastructure. The query execution result seems to support this conclusion.
3) At the moment there is no index on the privilege column of the acs_privilege_descendent_map table. Addition of an index on this field may provide an incremental improvement over 2) above.
If I think of anything else I will post it.
I'd be very grateful for input from others.
Regards
Richard
select 1 from dual
where exists
( select 1
from acs_object_party_privilege_map ppm
where ppm.object_id = :object_id and ppm.party_id = :party_id and ppm.privilege = :privilege
it should be enough to check any permission.
actually, I think is the same at least in pg, I just c/p from permission::permission_p_not_cached.select_permission_p, and guess that the "dual" part was still there because it came from previous ways when the acs_object_party_privilege_map wan't available yet and the dual use was needed.
select exists(select 1 from ...)
in PG we don't need the "select 1 from dual where exists (select 1 ... " form because "exists()" is implemented as a generalized function rather than a special WHERE-clause operator as is true in Oracle.
The reason you see "from dual" queries in our code or as Roc did in his example is that our "dual" view only has one row, so performance shouldn't vary noticably (feel free to test that presumption if you have time!). Using "from dual" allows us to write a single query that works in both PG and ORACLE, and single queries rather than two RDBMS-depended queries are much easier to maintain (exactly twice as easy, actually!) when datamodels change for some reason.
Exists - in theory, at least, and HOPEFULLY in practice - stops when the subquery returns its first row. In other words, exists() and LIMIT are the same. That's why we use exists() in our queries.
If you have time, it would be interesting to see your various tests but modified to surround your existing "select 1" queries with "select 1 where exists (select 1 ...)" queries. If adding LIMIT 1 decreases the execution time, then something broke in PG.
BTW Jeff's tested permissions queries in PG 8.0 and they're running fast as expected. Also we don't see permissions queries running slowly on OpenACS with its thousands of users. I think there was something messed up in the testing done earlier, in the thread you refer to. Lingering PG handles in AOLserver after ANALYZE was run, something along those lines.
But more testing is good, it's been over two years since I did the testing that led to the current strategy.
Oh, the main reason for not exploding the entire view into a trigger-maintained table is size. I chose to explode the party privilege member map instead. It's quite huge, but not nearly as huge, and doing this rather than explode the main view seemed to give the best bang for the buck without causing table size to go through the roof. Running out of shared buffer space or disk cache space on a large installation would wreak havoc on performance ...
Also ... the current plan for increasing performance is to start weeding out custom permissions where they aren't needed. The ability to define custom permissions is very useful, but aD's paradigm of doing so for every package is expensive, adds no functionality in most cases, nor readability. In other words "forums_create" is the same as "create" (and indeed is a child of "create"). Adds no functionality, just adds a row to the privileges table. I estimate that for .LRN we can cut down the rows in the privileges table (which is sequentially scanned because it has something like 60 rows in .LRN) down to maybe a dozen or at worst two rows. That would lead to a 3x-4x increase in performance on average ...
Thanks for looking that over and posting comments. I will re-run the benchmark tests wrapping the query in exists() and will post the results. I took the sample query from existing code and it neither selected from 'Dual' nor used 'Exists' so it looks as if I chose a bad example.
In between my initial posting and my reading the replies I did some more work which I have posted below. Just to re-iterate, here are my reasons for looking at this again:
1) Although the permissions system works acceptably fast, as it is central to everything, the faster it goes the better.
2) I am going to modify the project manager to have permissions at project and maybe even at task level. That could mean upwards of 20 permissions checks per page or even more. I want to avoid creating performance issues.
3) I understand fully why the heirarchies were not exploded fully into a table, and why a view was used and whilst I fully understand the rationale behind the existing approach, I think that there MAY still be mileage in exploring the alternatives. In particular the method I show below avoids the need for the view altogether - the query refers to the seperate hierarchies without needing a huge de-normalised presentation of the information.
Incidentally, I completely agree that the permissions subtyping is an unnecessary complication. It slows down the permissions queries, adds complexity without adding much of use and confuses users! I would be very keen to eliminate that complexity.
Here is the work I did earlier which I think is still valid since I use 'Limit' in all cases:
#######
STAGE 2
#######
I have done some additional work on this and decided to work on the areas of the query that either still depend on views or show up as relatively expensive in the query plan.
First of all I experimented with basing the query on the table group_element_index instead of the view group_member_index. This in itself did not improve matters which suggests that using a view in this context does not creat any performance issues.
So I then decided to create a table that would hold exactly the information required for a permissions query and nothing else. This table is intended to allow us to ask for a distinct list of group_ids which includes the id of the entity for which permission is sought. Including the party_id of the party itself in the group_id column simplifies the permission query by eliminating the need for an additional 'or' statement.
Creating the table:
select distinct element_id as group_id, element_id into rh_group_struct from group_element_index union select group_id, element_id from group_element_index;
create index rh_group_struct_element_id_idx on rh_group_struct (element_id);
create index rh_group_struct_group_id_idx on rh_group_struct (group_id);
Now the table can be queried using this:
select group_id from rh_group_struct where element_id=1070;
This returns a de-duped list of ancestor ids for the party 1070. It is obviously better to have a de-duped and therefore shorter list for the outer main permissions query to join to.
Now I thought I would try this in the main query:
TEST A-5
explain analyse select 1 from acs_permissions where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin') and object_id in (select ancestor_id from acs_object_context_index where object_id=16631) and grantee_id in (select group_id from rh_group_struct where element_id=448) limit 1;
Total runtime: 1.925 ms
TEST B-5
explain analyse select 1 from acs_permissions where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin') and object_id in (select ancestor_id from acs_object_context_index where object_id=16631) and grantee_id in (select group_id from rh_group_struct where element_id=-1) limit 1;
Total runtime: 1.186 ms
OK, so now we seem to have one query that performs equally quickly in both situations. I think that the main reason for the performance improvement is that the table can now store far more rows on each data page since the only fields are integer key fields. There are no unicode enabled text fields to manage.
I propose that the best way to create this table would be to use the same triggers that initiate the maintenance of the group_element_index table. I plan to experiment with the idea noted above of replacing the text based permissions hierarchy with integer keys to see if further gains in performance can be realised.
For now, whereas before I was concerned that 20 permission controlled projects on a page could have taken 20x500ms (or 20x130ms with Exists or Limit) to check, under this alternative method this would be reduced on this test system to 40ms. I reckon that deserves further exploration.
Further thoughts?
Regards
Richard
Thanks for the reassurance - I think project level permissions will be good. However the recommended permission check (using limit which should be equivalent to using Exists) takes 400ms for 'the public' on this test box.
In contrast the alternative method that I am experimenting with takes just less than 2ms. That is a 200 fold improvement. I can't see why that could be a bad thing.
Surely if we are interested in scaleability we should be interested in ways to improve performance - even if the status quo is really good already! 😊
Maybe I have got it wrong and there is a flaw in what I have done (in which case I welcome the inevitable corrections!), but I do think that it deserves scrutiny - rather than see it simply rejected as unnecessary.
R.
1. I've never claimed my permissions work for 4.6 was the be-all and end-all in possible speedups. Previously, queries against the view in PG (always) and Oracle (sometimes) would runaway and take literally tens of minutes to run. Now we're down to worst cases taking a large fraction of a second. At that point I stopped. Just want to make clear that further improvements are welcome, of course.
2. until either version 7.3 or 7.4, PG implemented the construct "id in (select id from ...)" by building the entire rowset for the subquery, then sequentially searching it of the left operand ("id"). That was horribly slow and made such constructs unusable. Such constructs can be implemented as a form of "join", using the appropriate index. PG does that now (Oracle always did) so we can consider using constructs of this sort where before we could not. In many places in OpenACS, not just permissions, you'll see our queries bend over backwards to avoid "id in (select id from ...)" clauses because of this earlier flaw in PG. As time goes on and people work on the code we can take advantage of PG's enhanced intelligence and use this form.
3. Here are the two main problems with not using the view:
a. If the underlying datamodel changes again (as I changed it for 4.6) for enhanced performance, you need to chase down EVERY QUERY doing such permissions checks and rewrite them. Using the VIEW, we only have to drop and create the VIEW.
b. It's a constant struggle to get people to use the VIEW rather than call acs_permission__permission_p in their WHERE clauses. If you call the proc, in both Oracle and PG at times the proc will be called for EACH ROW being examined, rather than only rows that are qualified by other clauses in the WHERE clause. In general, calling PL/[pg]SQL functions in WHERE clauses is begging for a scalability disaster (as opposed to calling such functions in the rowset designator, i.e. the columns you specify following the SELECT keyword, PL/[pg]SQL is fine there). Getting people to write more complex WHERE clauses would be even harder, I'm afraid, especially when one considers point "a" above.
c. And point "b" is why we can't just bury your changes into permission_p and maintain abstraction by calling the function rather than use the view or do the query bits you're discussing manually. Oracle and PG will both build disasterous execution plans for queries with the permission_p call in the WHERE clause unpredictably.
So I think we need to concentrate on the VIEW. BTW both PG and Oracle are pretty good at generating the same plan for VIEWs as for the components, so you might consider trying to rewrite the VIEW using the "in (select ...)" approach and see if that helps.
Regarding your 2-integer table ... I'm surprised it helps so much. First of all, PG has a per-row (not table) overhead of 40+ bytes, so the size difference of a row compared to the group_element_index shouldn't be as large as you're probably imagining. Unlike Oracle, BTW, which has a very low per-row overhead (maybe even zero bytes).
So can you post EXPLAIN ANALYZE output on your 400ms query using LIMIT above? I want to see exactly what's going on in terms of execution, not just the plan but the measured times for the various bits of the query. Also the EXPLAIN ANALYZE output for your super-fast query.
Lastly, where the hell is that original query from, that doesn't do "exists()"? We need to fix it!
In the benchmarking you are doing how many users and how many objects exist?
I tried to reproduce some of your results and got substantially diferent times possibly because of differences in how we have the db tuned, caching issues, or differences in our base dataset.
One coment on your first post is that creating an index on privilege is probably not helpful in postgres as it is of such low ordinality and I have seen places where this sort of index either induces the optimizer to produce an extremely bad plan or, at best, has no real impact.
On oracle you can do this with bitmap indexes and it can be a win but iirc bitmap indexes are an EE feature.
On other thing we might want to do is consider creating partial indexes in postgres for some things (eg. for "the public" or for read permissions on packages as they are the most common things to show up in the permission checks. Also we should investigate whether changing the statistics stuff can improve the quality of the produced plans.
Oh, and what Don said, we need to see real plan outputs.
Also you might try your experiments with setting the pg parameter geqo_threshold = 22 (or at least something bigger than 11 which is the default) which impacts how postgres flattens views in queries (it will only collapse views if the resulting number of tables in the from clause is < geqo_threshold/2). In pg 8 they gave this it's own parameter from_collapse_limit which is a good place to read about what impact this should have.
Thanks for the replies. Before reading them I had already done some more work on this so what you see below was done before responding to your posts.
I knew that you would need to see the query plans in full, and initially when I drafted the first post I had them in, but it was all a bit too much at once! 😊 I thought I should narrow the choices a little first. You’ll find them below.
Firstly, to cover Jeff’s specific comments and questions:
- How many users and objects? Around 15000 objects, only about 7 discreet users but some groups and a hierarchical set of compositions of the above. It is a test system.
- When you say that you get different results do you mean in absolute terms (execution time) or in relative terms? I am working with PostgreSQL 7.4.2. I accept that database tuning can make enormous differences to execution times, as can summary tables, de-normalisations and differences in query planning schemes, but in the end one factor is how hard the problem is that the DB is being asked to solve. I have done no specific tuning work on this DB beyond increasing the shared memory buffers and other ‘standard’ openacs things. I have not run analyse very much to take advantage of stats – but then on a test system that is less useful since there are not hordes of people trying to prise the data out at the same time. 😊
- I suggested the additional index on acs_permissions primarily with an eye on the possibility of guiding the planner to go to the index rather than to the data pages. It was an afterthought really. I remember that putting indices on columns with a small number of discreet values is often a bad idea. Perhaps that suggestion is only half baked!
- I don’t know anything about partial indices. I can guess what they are but have never seen a live one, and I am sure that regular running of analyse would speed things up. I am testing relative times here since I don’t know of a better way to compare options.
- I am not really sure that I fully understand your reasoning in relation to the planner settings. The default value for the geqo_threshold is 12 and none of the queries refer to more than 4 tables. For that reason I believe that these query plans are all fully deterministically produced, so changing the parameter that determines when the planner goes to genetic planning seems illogical. Even the from_collapse_limit (default value 8) will not be reached by our test queries.
- One final point, one of the reasons for looking at the new query structure as an option is that it may reduce the number of possible execution plans that the planner must explore. This article describes the process although the example is not identical in structure http://jamesthornton.com/postgres/7.1/postgres/explicit-joins.html
Ok, here is the work that I did yesterday:
####### STAGE 3 #######
Here are the benchmarks for three variants of the recommended query structure against the acs_object_party_privilege_map view. The first variant of the query is as presented in the original benchmark TEST A-1).
Original Test:
explain analyse select 1 from acs_object_party_privilege_map ppm where ppm.party_id=-1 and ppm.privilege='admin' and ppm.object_id=16631;
Total runtime: 223.259 ms
i) explain analyse select exists(select 1 from acs_object_party_privilege_map ppm where ppm.party_id=-1 and ppm.privilege='admin' and ppm.object_id=16631);
Total runtime: 501.860 ms
ii) explain analyse select 1 where exists(select 1 from acs_object_party_privilege_map ppm where ppm.party_id=-1 and ppm.privilege='admin' and ppm.object_id=16631);
Total runtime: 501.337 ms
iii) explain analyse select 1 from dual where exists(select 1 from acs_object_party_privilege_map ppm where ppm.party_id=-1 and ppm.privilege='admin' and ppm.object_id=16631);
Total runtime: 501.718 ms
The obvious conclusion here is that with the optimiser in PostgreSQL 7.4.2. there is no advantage in selecting from dual, or using exists with this query structure. In fact, the use of exists actually doubles the execution time. Hopefully this collection of tests will provide a satisfactory baseline from which we can work.
Remember that TEST B-5 took 1.186ms to produce the same result. My personal view is that this represents an improvement (unless I have made some mistake(s) somewhere!).
I fully understand the issues with changing the existing method and data model, however if we can in fact realise a significant performance gain I would think that it would be worth phasing in a change over time.
I also understand your concern about the consequences of abstracting this into the function acs_permission__permission_p however developers must surely retain responsibility for understanding the performance consequences of various query structures and selecting accordingly. In any event we should perhaps test this rather than assume that there will be a problem (maybe the query planner does a better job now?). If we remain concerned about existing code then we could retain the view for driving acs_permission__permission_p calls for legacy code.
In any event, I agree with your point 3b, nesting a permission call in such a way that it is called for every row in the outer loop is asking for trouble. That is another reason why I think this other structure will work well because it allows us to guide the planner a little more firmly to the execution plan that we know will work well. It reduces the planner’s options!
To re-iterate - the biggest advantages of using the table I have created are (I think):
1) The table is structured so that a simple query returns the party_id and all its ancestor_ids with no duplicates.
2) This is achieved (I think) with reference to the index.
3) The de-duplication occurs when the rows are inserted, which only happens when group memberships are changed. This ensures that the work is done once only and it takes place during a non time critical activity.
4) There are no unicode enabled text fields to slow things down.
The advantages of the alternative query structure are (I think):
1) It avoids ever having to de-normalise portions of the underlying triple hierarchy into a single dataset.
2) Instead it extracts the relevant rows from each hierarchy into three duplicate free datasets (which will tend to be a small number of rows) and then only has to join the acs_permissions table to those three small datasets.
For completenes, this is what happens if I make a view that has only the two integer key fields as per rh_group_struct thereby avoiding the need to maintain the table with triggers.
Create the view:
create view rh_group_struct_view (group_id, element_id) as select distinct element_id as group_id, element_id from group_element_index union select group_id, element_id from group_element_index;
\d rh_group_struct_view
View "public.rh_group_struct_view" Column | Type | Modifiers ------------+---------+----------- group_id | integer | element_id | integer | View definition: SELECT DISTINCT group_element_index.element_id AS group_id, group_element_index.element_id FROM group_element_index ORDER BY group_element_index.element_id, group_element_index.element_id UNION SELECT group_element_index.group_id, group_element_index.element_id FROM group_element_index;Here is a re-run of the tests for the alternative query structure using rh_group_struct_view instead of the rh_group_struct table with indices:
TEST A-6
explain analyse select 1 from acs_permissions where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin') and object_id in (select ancestor_id from acs_object_context_index where object_id=16631) and grantee_id in (select group_id from rh_group_struct_view where element_id=448) limit 1; Total runtime: 16.717 msTEST B-6
explain analyse select 1 from acs_permissions where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin') and object_id in (select ancestor_id from acs_object_context_index where object_id=16631) and grantee_id in (select group_id from rh_group_struct_view where element_id=-1) limit 1;
Total runtime: 236.622 ms
So, that is significanty worse in both cases! My conclusion here is that (subject to tuning wizardry beyond my knowledge!) it would be better if we avoid using a view. The reason given for not fully denormalising the triple hierarchy was resource usage, so we were left with little choice but to use a view. But if now the query planner can cope with queries of the structure that I have tested here, then perhaps it would be wise to take advantage of that improvement since then we would not need to de-normalise the whole triple hierarchy anyway. I agree that we would need to test the effects of an abstraction into permission::permission_p and usage in both select and where clauses (perhaps the PostgreSQL team have sorted this planning issue as well?). I wasn't aware of the original constraints that you were working around and so these suggestions may be borne of 'constructive naivety', but maybe the constraints are no longer there.😊
For completeness here are the complete execution plans for the original benchmark query, the recommended query using 'exists' and finally the fastest of the alternative queries for you to see:
Original benchmark query: ========================= explain analyse select 1 from acs_object_party_privilege_map ppm where ppm.party_id=-1 and ppm.privilege='admin' and ppm.object_id=16631;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=51.32..268.34 rows=8 width=0) (actual time=219.645..219.645 rows=0 loops=1)
-> Hash Join (cost=51.32..220.65 rows=6 width=4) (actual time=219.640..219.640 rows=0 loops=1)
Hash Cond: ("outer".grantee_id = "inner".party_id)
-> Nested Loop (cost=0.00..168.84 rows=57 width=8) (actual time=0.336..193.543 rows=10781 loops=1)
-> Seq Scan on acs_privilege_descendant_map pdm (cost=0.00..2.05 rows=1 width=68) (actual time=0.197..0.311 rows=1 loops=1)
Filter: ((descendant)::text = admin'::text)
-> Index Scan using acs_permissions_privilege_idx on acs_permissions p (cost=0.00..166.08 rows=57 width=76) (actual time=0.124..137.987 rows=10781 loops=1)
Index Cond: (("outer".privilege)::text = (p.privilege)::text)
-> Hash (cost=51.27..51.27 rows=18 width=4) (actual time=0.096..0.096 rows=0 loops=1)
-> Index Scan using party_member_member_idx on party_approved_member_map pamm (cost=0.00..51.27 rows=18 width=4) (actual time=0.080..0.086 rows=1 loops=1)
Index Cond: (member_id = -1)
-> Index Scan using acs_object_context_index_pk on acs_object_context_index c (cost=0.00..7.93 rows=1 width=4) (never executed)
Index Cond: ((c.object_id = 16631) AND (c.ancestor_id = "outer".object_id))
Total runtime: 219.995 ms
(14 rows)
The recommended query:
======================
explain analyse select exists(select 1 from acs_object_party_privilege_map ppm where ppm.party_id=-1 and ppm.privilege='admin' and ppm.object_id=16631);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=554.75..554.76 rows=1 width=0) (actual time=499.768..499.770 rows=1 loops=1)
InitPlan
-> Nested Loop (cost=0.00..554.75 rows=8 width=0) (actual time=499.757..499.757 rows=0 loops=1)
-> Nested Loop (cost=0.00..507.05 rows=6 width=4) (actual time=499.753..499.753 rows=0 loops=1)
-> Nested Loop (cost=0.00..168.84 rows=57 width=8) (actual time=0.359..207.760 rows=10781 loops=1)
-> Seq Scan on acs_privilege_descendant_map pdm (cost=0.00..2.05 rows=1 width=68) (actual time=0.206..0.322 rows=1 loops=1)
Filter: ((descendant)::text = 'admin'::text)
-> Index Scan using acs_permissions_privilege_idx on acs_permissions p (cost=0.00..166.08 rows=57 width=76) (actual time=0.136..144.241 rows=10781 loops=1)
Index Cond: (("outer".privilege)::text = (p.privilege)::text)
-> Index Scan using party_approved_member_map_pk on party_approved_member_map pamm (cost=0.00..5.92 rows=1 width=4) (actual time=0.021..0.021 rows=0 loops=10781)
Index Cond: ((pamm.party_id = "outer".grantee_id) AND (pamm.member_id = -1))
-> Index Scan using acs_object_context_index_pk on acs_object_context_index c (cost=0.00..7.93 rows=1 width=4) (never executed)
Index Cond: ((c.object_id = 16631) AND (c.ancestor_id = "outer".object_id))
Total runtime: 500.130 ms
(14 rows)
The suggested alternative query
===============================
explain analyse select 1 from acs_permissions where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin') and object_id in (select ancestor_id from acs_object_context_index where object_id=16631) and grantee_id in (select group_id from rh_group_struct where element_id=-1) limit 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3.58..174.65 rows=1 width=0) (actual time=0.811..0.811 rows=0 loops=1)
-> Nested Loop IN Join (cost=3.58..174.65 rows=1 width=0) (actual time=0.806..0.806 rows=0 loops=1)
-> Hash IN Join (cost=3.58..170.67 rows=1 width=4) (actual time=0.802..0.802 rows=0 loops=1)
Hash Cond: (("outer".privilege)::text = ("inner".descendant)::text)
-> Nested Loop (cost=1.53..168.32 rows=57 width=72) (actual time=0.121..0.121 rows=0 loops=1)
-> HashAggregate (cost=1.53..1.53 rows=1 width=4) (actual time=0.119..0.119 rows=0 loops=1)
-> Seq Scan on rh_group_struct (cost=0.00..1.52 rows=1 width=4) (actual time=0.114..0.114 rows=0 loops=1)
Filter: (element_id = -1)
-> Index Scan using acs_permissions_grantee_idx on acs_permissions (cost=0.00..166.08 rows=57 width=76) (never executed)
Index Cond: (acs_permissions.grantee_id = "outer".group_id)
-> Hash (cost=2.05..2.05 rows=1 width=68) (actual time=0.654..0.654 rows=0 loops=1)
-> Seq Scan on acs_privilege_descendant_map (cost=0.00..2.05 rows=1 width=68) (actual time=0.182..0.555 rows=27 loops=1)
Filter: ((privilege)::text = 'admin'::text)
-> Index Scan using acs_object_context_index_pk on acs_object_context_index (cost=0.00..7.93 rows=1 width=4) (never executed)
Index Cond: ((acs_object_context_index.object_id = 16631) AND ("outer".object_id = acs_object_context_index.ancestor_id))
Total runtime: 1.176 ms
(16 rows)
[Now you’re going to tell me that my query plans reveal my ignorance! J]
I really hope that this proves to be useful. I have spent quite a few hours producing this data so I hope that we will be able to derive some benefit from it. Perhaps once the inevitable debate has been concluded we could decide what action is to be taken (if any). That would enable me to progress with the work that I am planning.
I also agree strongly that stripping out the unnecessary complexity of the ‘privileges’ hierarchy will lead to further significant performance improvements. Any reduction in the number of rows returned by the subselect will produce a reduction in the size of the joined dataset.
My personal view is that since the number of permissions calls grows directly in proportion to the number of objects (recognising a party as an object also) in the system and the granularity of permissioning required, and that the number of rows in the de-normalised hierarchy is polynomial with respect to the depth and complexity of the hierarchical relationships between objects, we should do everything we can to reduce the unit cost of any permission request. Doing so can only increase the scaleability of our system and defer the point of final meltdown!
All of this comes with the caveat that I may not have implemented something correctly so please continue to be critical in your appraisal and let me know if my query doesn't do what I think it does!
I hope very much that we can overcome the practical issues associated with making a change.
Regards Richard.
(And we've only been repeatedly fixing and re-breaking this obvious feature for years now, people...)
In 7.4 geqo_threshold is used for two things, one is the threshold for the geqo optimizer but the other is whether or not to flatten the tables in a subquery or view when optimizing (in pg 8 that semi-secret function was broken out into an explicit parameter). And yes, it should not matter if there are only four tables in the query.
Your faster query does not return the correct answer it seems (member -1 does not show up in the rh_group_struct_view):
openacs.org=# select * from acs_permissions where
privilege in (select descendant from acs_privilege_descendant_map where privilege='read')
and object_id in (select ancestor_id from acs_object_context_index where object_id=179199)
and grantee_id in (select group_id from rh_group_struct_view where element_id=-1);
object_id | grantee_id | privilege
-----------+------------+-----------
(0 rows)
openacs.org=# select * from acs_object_party_privilege_map ppm where
ppm.party_id=-1 and ppm.privilege='read' and ppm.object_id=179199;
object_id | privilege | party_id
-----------+-----------+----------
179199 | read | -1
(1 row)
And for reference here is the plan on openacs.org for a check for admin for public on a site node (which returns false):
openacs.org=# explain analyze select exists (select * from acs_object_party_privilege_map ppm where ppm.party_id=-1 and ppm.privilege='admin' and ppm.object_id=179199);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=119.70..119.71 rows=1 width=0) (actual time=1.332..1.333 rows=1 loops=1)
InitPlan
-> Hash Join (cost=15.82..119.70 rows=2 width=26) (actual time=1.322..1.322 rows=0 loops=1)
Hash Cond: ("outer".grantee_id = "inner".party_id)
-> Hash Join (cost=3.61..107.33 rows=11 width=26) (actual time=0.373..1.159 rows=30 loops=1)
Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
-> Nested Loop (cost=0.00..102.88 rows=74 width=17) (actual time=0.070..0.542 rows=38 loops=1)
-> Index Scan using acs_object_context_index_pk on acs_object_context_index c (cost=0.00..22.95 rows=7 width=8) (actual time=0.031..0.054 rows=5 loops=1)
Index Cond: (object_id = 179199)
-> Index Scan using acs_permissions_pk on acs_permissions p (cost=0.00..11.39 rows=3 width=17) (actual time=0.017..0.053 rows=8 loops=5)
Index Cond: ("outer".ancestor_id = p.object_id)
-> Hash (cost=3.60..3.60 rows=3 width=31) (actual time=0.247..0.247 rows=0 loops=1)
-> Seq Scan on acs_privilege_descendant_map pdm (cost=0.00..3.60 rows=3 width=31) (actual time=0.074..0.240 rows=1 loops=1)
Filter: ((descendant)::text = 'admin'::text)
-> Hash (cost=12.20..12.20 rows=4 width=8) (actual time=0.061..0.061 rows=0 loops=1)
-> Index Scan using party_member_member_idx on party_approved_member_map pamm (cost=0.00..12.20 rows=4 width=8) (actual time=0.047..0.053 rows=1 loops=1)
Index Cond: (member_id = -1)
Total runtime: 1.582 ms
I think given this is <2ms for 8600 users and 140k objects I
don't think it needs to be fixed. The plan produced here is
quite a bit different than what you get so I think either
you need to run vacuumdb -f -z YOURDB or your postgres conf
is seriously out of whack.
I do NOT understand how "exists()" can run slower than the non "exists()" query. Something very evil is going on. Something very evil that we do NOT see on production systems that are properly set up.
As Jeff says ... run vacuumdb with the proper switches to do an analysis as get rid of dead rows. Your comment that "analyze isn't really necessary for a test system" is false ... no benchmarking without a recent ANALYZE is meaningless. The statistical data directly impacts the plan that is generated. Without stats on a table, PG *will* generate poor plans.
At this point let me summarize our state of knowledge:
1. As Jeff says, the problem with extremely slow permissions queries that you're seeing on your system are not seen by people with properly set-up and recently ANALYZE'd production sites. That doesn't mean that improvements aren't possible, but it does mean that we're going to be suspicious of your numbers because we simply don't see these results in the real world ... where the numbers really count.
2. I'm mystified by the numbers you get, especially with the "exists()" form of the queries. But without a recent ANALYZE on your database the benchmarks aren't very useful.
3. It is possible that you've found a "sour spot" (as opposed to "sweet spot") for the permissions queries with your low number of users and relatively low number of objects, that PG will make bad choices for such a combination. But ... until I see this after you run ANALYZE I'm going to have a hard time believing it ...
Mmmm...............False will bells on I think!
After running a vacuumdb with full and analyse (four times for good measure!) the standard query returns results for both 448 and -1 in approximately 1.2ms - which is great news.
The alternative query runs slightly slower now at just under 2ms for both tests which clearly demonstrates that the current method is best. Thank you for your help - sorry to soak up your precious time.
Now I can get on with working on the PM without worrying about permissions calls!
😊
I am now tryiung different ways to put permissions on projects at the query level. I would value your opinion as to which of these is the preferred method (or any other you can devise!).
A) QUERY WITH PERMISSIONS CALL IN SELECT STATEMENT
Reqires tcl code to control display of data. I tried adding:
and permission='t'to the where clause but postgres will not allow me to refer to the column using the affiliation name. Is there a way to do this (i.e. with column number as with an aggregate function).
explain analyse SELECT p.project_id, p.parent_id as folder_id, p.object_type as content_type, p.title as project_name, p.project_code, (select exists(select 1 from acs_object_party_privilege_map ppm where ppm.object_id = p.project_id and ppm.party_id = -1 and ppm.privilege = 'admin')) as permission
FROM pm_projectsx p
LEFT JOIN pm_project_assignment pa
ON p.item_id = pa.project_id
LEFT JOIN organizations o ON p.customer_id =
o.organization_id
LEFT JOIN (
select
om.category_id,
om.object_id,
t.name as category_name
from
category_object_map om,
category_translations t,
categories ctg
where
om.category_id = t.category_id and
ctg.category_id = t.category_id and
ctg.deprecated_p = 'f')
c ON p.item_id = c.object_id,
cr_items i,
pm_project_status s
WHERE
p.project_id = i.live_revision and
s.status_id = p.status_id;
tradeadev# QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=4010.03..4129.91 rows=2 width=65) (actual time=37.805..37.805 rows=0 loops=1)
-> Hash Join (cost=4010.03..4016.69 rows=2 width=73) (actual time=37.799..37.799 rows=0 loops=1)
Hash Cond: ("outer".status_id = "inner".status_id)
-> Hash Left Join (cost=4009.01..4015.63 rows=2 width=77) (actual time=37.680..37.680 rows=0 loops=1)
Hash Cond: ("outer".item_id = "inner".object_id)
-> Nested Loop Left Join (cost=4008.98..4015.59 rows=2 width=81) (actual time=37.621..37.621 rows=0 loops=1)
-> Merge Left Join (cost=4008.98..4009.22 rows=2 width=85) (actual time=37.618..37.618 rows=0 loops=1)
Merge Cond: ("outer".item_id = "inner".project_id)
-> Sort (cost=4006.38..4006.39 rows=2 width=85) (actual time=37.613..37.613 rows=0 loops=1)
Sort Key: cr.item_id
-> Nested Loop (cost=0.00..4006.37 rows=2 width=85) (actual time=37.583..37.583 rows=0 loops=1)
-> Seq Scan on cr_text (cost=0.00..1.01 rows=1 width=0) (actual time=0.009..0.013 rows=1 loops=1)
-> Nested Loop (cost=0.00..4005.34 rows=2 width=85) (actual time=37.550..37.550 rows=0 loops=1)
-> Nested Loop (cost=0.00..4001.84 rows=1 width=81) (actual time=37.548..37.548 rows=0 loops=1)
-> Nested Loop (cost=0.00..3996.09 rows=1 width=61) (actual time=37.545..37.545 rows=0 loops=1)
-> Seq Scan on pm_projects (cost=0.00..3992.51 rows=1 width=26) (actual time=37.543..37.543 rows=0 loops=1)
Filter: ((subplan) = true)
SubPlan
-> Result (cost=53.18..53.19 rows=1 width=0) (actual time=0.490..0.491 rows=1 loops=75)
InitPlan
-> Hash Join (cost=51.11..53.18 rows=1 width=0) (actual time=0.480..0.480 rows=0 loops=75)
Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
-> Seq Scan on acs_privilege_descendant_map pdm (cost=0.00..2.05 rows=3 width=13) (actual time=0.122..0.194 rows=1 loops=75)
Filter: ((descendant)::text = 'admin'::text)
-> Hash (cost=51.11..51.11 rows=1 width=9) (actual time=0.266..0.266 rows=0 loops=75)
-> Hash Join (cost=3.46..51.11 rows=1 width=9) (actual time=0.263..0.263 rows=0 loops=75)
Hash Cond: ("outer".grantee_id = "inner".party_id)
-> Nested Loop (cost=0.00..47.59 rows=6 width=13) (actual time=0.049..0.242 rows=8 loops=75)
-> Index Scan using acs_object_context_index_pk on acs_object_context_index c (cost=0.00..17.93 rows=5 width=4) (actual time=0.021..0.048 rows=6 loops=75)
Index Cond: (object_id = $0)
-> Index Scan using acs_permissions_pk on acs_permissions p (cost=0.00..5.92 rows=1 width=17) (actual time=0.014..0.020 rows=1 loops=450)
Index Cond: ("outer".ancestor_id = p.object_id)
-> Hash (cost=3.45..3.45 rows=2 width=4) (actual time=0.083..0.083 rows=0 loops=1)
-> Index Scan using party_member_member_idx on party_approved_member_map pamm (cost=0.00..3.45 rows=2 width=4) (actual time=0.057..0.063 rows=1 loops=1)
Index Cond: (member_id = -1)
-> Index Scan using cr_revisions_pk on cr_revisions cr (cost=0.00..3.57 rows=1 width=35) (never executed)
Index Cond: ("outer".project_id = cr.revision_id)
-> Index Scan using acs_objects_pk on acs_objects (cost=0.00..5.74 rows=1 width=20) (never executed)
Index Cond: (acs_objects.object_id = "outer".project_id)
-> Index Scan using cr_items_pk on cr_items i (cost=0.00..3.49 rows=1 width=8) (never executed)
Index Cond: ("outer".item_id = i.item_id)
-> Sort (cost=2.60..2.70 rows=43 width=4) (never executed)
Sort Key: pa.project_id
-> Seq Scan on pm_project_assignment pa (cost=0.00..1.43 rows=43 width=4) (never executed)
-> Index Scan using organization_id_pk on organizations o (cost=0.00..3.17 rows=1 width=4) (never executed)
Index Cond: ("outer".customer_id = o.organization_id)
-> Hash (cost=0.03..0.03 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)
-> Nested Loop (cost=0.00..0.03 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
Join Filter: ("inner".category_id = "outer".category_id)
-> Nested Loop (cost=0.00..0.01 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)
Join Filter: ("inner".category_id = "outer".category_id)
-> Seq Scan on category_translations t (cost=0.00..0.00 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1)
-> Seq Scan on categories ctg (cost=0.00..0.00 rows=1 width=4) (never executed)
Filter: (deprecated_p = 'f'::bpchar)
-> Seq Scan on category_object_map om (cost=0.00..0.00 rows=1 width=8) (never executed)
-> Hash (cost=1.02..1.02 rows=2 width=4) (actual time=0.060..0.060 rows=0 loops=1)
-> Seq Scan on pm_project_status s (cost=0.00..1.02 rows=2 width=4) (actual time=0.028..0.036 rows=2 loops=1)
-> Index Scan using cr_items_by_live_revision on cr_items i (cost=0.00..3.40 rows=1 width=4) (never executed)
Index Cond: (i.live_revision = "outer".revision_id)
SubPlan
-> Result (cost=53.18..53.19 rows=1 width=0) (never executed)
InitPlan
-> Hash Join (cost=51.11..53.18 rows=1 width=0) (never executed)
Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
-> Seq Scan on acs_privilege_descendant_map pdm (cost=0.00..2.05 rows=3 width=13) (never executed)
Filter: ((descendant)::text = 'admin'::text)
-> Hash (cost=51.11..51.11 rows=1 width=9) (never executed)
-> Hash Join (cost=3.46..51.11 rows=1 width=9) (never executed)
Hash Cond: ("outer".grantee_id = "inner".party_id)
-> Nested Loop (cost=0.00..47.59 rows=6 width=13) (never executed)
-> Index Scan using acs_object_context_index_pk on acs_object_context_index c (cost=0.00..17.93 rows=5 width=4) (never executed)
Index Cond: (object_id = $0)
-> Index Scan using acs_permissions_pk on acs_permissions p (cost=0.00..5.92 rows=1 width=17) (never executed)
Index Cond: ("outer".ancestor_id = p.object_id)
-> Hash (cost=3.45..3.45 rows=2 width=4) (never executed)
-> Index Scan using party_member_member_idx on party_approved_member_map pamm (cost=0.00..3.45 rows=2 width=4) (never executed)
Index Cond: (member_id = -1)
Total runtime: 39.795 ms
(78 rows)
B) WRAP ABOVE IN AND OUTER SELECT TO EXTRACT ONLY REQUIRED ROWS
explain analyse SELECT * from (SELECT p.project_id, p.parent_id as folder_id, p.object_type as content_type, p.title as project_name, p.project_code, (select exists(select 1 from acs_object_party_privilege_map ppm where ppm.object_id = p.project_id and ppm.party_id = -1 and ppm.privilege = 'admin')) as permission
FROM pm_projectsx p
LEFT JOIN pm_project_assignment pa
ON p.item_id = pa.project_id
LEFT JOIN organizations o ON p.customer_id =
o.organization_id
LEFT JOIN (
select
om.category_id,
om.object_id,
t.name as category_name
from
category_object_map om,
category_translations t,
categories ctg
where
om.category_id = t.category_id and
ctg.category_id = t.category_id and
ctg.deprecated_p = 'f')
c ON p.item_id = c.object_id,
cr_items i,
pm_project_status s
WHERE
p.project_id = i.live_revision and
s.status_id = p.status_id) as project_list where permission='t';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=4010.03..4129.91 rows=2 width=65) (actual time=45.897..45.897 rows=0 loops=1)
-> Hash Join (cost=4010.03..4016.69 rows=2 width=73) (actual time=45.891..45.891 rows=0 loops=1)
Hash Cond: ("outer".status_id = "inner".status_id)
-> Hash Left Join (cost=4009.01..4015.63 rows=2 width=77) (actual time=45.768..45.768 rows=0 loops=1)
Hash Cond: ("outer".item_id = "inner".object_id)
-> Nested Loop Left Join (cost=4008.98..4015.59 rows=2 width=81) (actual time=45.710..45.710 rows=0 loops=1)
-> Merge Left Join (cost=4008.98..4009.22 rows=2 width=85) (actual time=45.708..45.708 rows=0 loops=1)
Merge Cond: ("outer".item_id = "inner".project_id)
-> Sort (cost=4006.38..4006.39 rows=2 width=85) (actual time=45.703..45.703 rows=0 loops=1)
Sort Key: cr.item_id
-> Nested Loop (cost=0.00..4006.37 rows=2 width=85) (actual time=45.669..45.669 rows=0 loops=1)
-> Seq Scan on cr_text (cost=0.00..1.01 rows=1 width=0) (actual time=0.009..0.013 rows=1 loops=1)
-> Nested Loop (cost=0.00..4005.34 rows=2 width=85) (actual time=45.637..45.637 rows=0 loops=1)
-> Nested Loop (cost=0.00..4001.84 rows=1 width=81) (actual time=45.634..45.634 rows=0 loops=1)
-> Nested Loop (cost=0.00..3996.09 rows=1 width=61) (actual time=45.632..45.632 rows=0 loops=1)
-> Seq Scan on pm_projects (cost=0.00..3992.51 rows=1 width=26) (actual time=45.629..45.629 rows=0 loops=1)
Filter: ((subplan) = true)
SubPlan
-> Result (cost=53.18..53.19 rows=1 width=0) (actual time=0.495..0.496 rows=1 loops=75)
InitPlan
-> Hash Join (cost=51.11..53.18 rows=1 width=0) (actual time=0.484..0.484 rows=0 loops=75)
Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
-> Seq Scan on acs_privilege_descendant_map pdm (cost=0.00..2.05 rows=3 width=13) (actual time=0.123..0.195 rows=1 loops=75)
Filter: ((descendant)::text = 'admin'::text)
-> Hash (cost=51.11..51.11 rows=1 width=9) (actual time=0.269..0.269 rows=0 loops=75)
-> Hash Join (cost=3.46..51.11 rows=1 width=9) (actual time=0.266..0.266 rows=0 loops=75)
Hash Cond: ("outer".grantee_id = "inner".party_id)
-> Nested Loop (cost=0.00..47.59 rows=6 width=13) (actual time=0.052..0.244 rows=8 loops=75)
-> Index Scan using acs_object_context_index_pk on acs_object_context_index c (cost=0.00..17.93 rows=5 width=4) (actual time=0.023..0.050 rows=6 loops=75)
Index Cond: (object_id = $0)
-> Index Scan using acs_permissions_pk on acs_permissions p (cost=0.00..5.92 rows=1 width=17) (actual time=0.014..0.020 rows=1 loops=450)
Index Cond: ("outer".ancestor_id = p.object_id)
-> Hash (cost=3.45..3.45 rows=2 width=4) (actual time=0.085..0.085 rows=0 loops=1)
-> Index Scan using party_member_member_idx on party_approved_member_map pamm (cost=0.00..3.45 rows=2 width=4) (actual time=0.060..0.067 rows=1 loops=1)
Index Cond: (member_id = -1)
-> Index Scan using cr_revisions_pk on cr_revisions cr (cost=0.00..3.57 rows=1 width=35) (never executed)
Index Cond: ("outer".project_id = cr.revision_id)
-> Index Scan using acs_objects_pk on acs_objects (cost=0.00..5.74 rows=1 width=20) (never executed)
Index Cond: (acs_objects.object_id = "outer".project_id)
-> Index Scan using cr_items_pk on cr_items i (cost=0.00..3.49 rows=1 width=8) (never executed)
Index Cond: ("outer".item_id = i.item_id)
-> Sort (cost=2.60..2.70 rows=43 width=4) (never executed)
Sort Key: pa.project_id
-> Seq Scan on pm_project_assignment pa (cost=0.00..1.43 rows=43 width=4) (never executed)
-> Index Scan using organization_id_pk on organizations o (cost=0.00..3.17 rows=1 width=4) (never executed)
Index Cond: ("outer".customer_id = o.organization_id)
-> Hash (cost=0.03..0.03 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)
-> Nested Loop (cost=0.00..0.03 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
Join Filter: ("inner".category_id = "outer".category_id)
-> Nested Loop (cost=0.00..0.01 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)
Join Filter: ("inner".category_id = "outer".category_id)
-> Seq Scan on category_translations t (cost=0.00..0.00 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
-> Seq Scan on categories ctg (cost=0.00..0.00 rows=1 width=4) (never executed)
Filter: (deprecated_p = 'f'::bpchar)
-> Seq Scan on category_object_map om (cost=0.00..0.00 rows=1 width=8) (never executed)
-> Hash (cost=1.02..1.02 rows=2 width=4) (actual time=0.061..0.061 rows=0 loops=1)
-> Seq Scan on pm_project_status s (cost=0.00..1.02 rows=2 width=4) (actual time=0.030..0.039 rows=2 loops=1)
-> Index Scan using cr_items_by_live_revision on cr_items i (cost=0.00..3.40 rows=1 width=4) (never executed)
Index Cond: (i.live_revision = "outer".revision_id)
SubPlan
-> Result (cost=53.18..53.19 rows=1 width=0) (never executed)
InitPlan
-> Hash Join (cost=51.11..53.18 rows=1 width=0) (never executed)
Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
-> Seq Scan on acs_privilege_descendant_map pdm (cost=0.00..2.05 rows=3 width=13) (never executed)
Filter: ((descendant)::text = 'admin'::text)
-> Hash (cost=51.11..51.11 rows=1 width=9) (never executed)
-> Hash Join (cost=3.46..51.11 rows=1 width=9) (never executed)
Hash Cond: ("outer".grantee_id = "inner".party_id)
-> Nested Loop (cost=0.00..47.59 rows=6 width=13) (never executed)
-> Index Scan using acs_object_context_index_pk on acs_object_context_index c (cost=0.00..17.93 rows=5 width=4) (never executed)
Index Cond: (object_id = $0)
-> Index Scan using acs_permissions_pk on acs_permissions p (cost=0.00..5.92 rows=1 width=17) (never executed)
Index Cond: ("outer".ancestor_id = p.object_id)
-> Hash (cost=3.45..3.45 rows=2 width=4) (never executed)
-> Index Scan using party_member_member_idx on party_approved_member_map pamm (cost=0.00..3.45 rows=2 width=4) (never executed)
Index Cond: (member_id = -1)
Total runtime: 47.817 ms
(78 rows)
C)
tradeadev=# explain analyse SELECT p.project_id, p.parent_id as folder_id, p.object_type as content_type, p.title as project_name, p.project_code
FROM pm_projectsx p
LEFT JOIN pm_project_assignment pa
ON p.item_id = pa.project_id
LEFT JOIN organizations o ON p.customer_id =
o.organization_id
LEFT JOIN (
select
om.category_id,
om.object_id,
t.name as category_name
from
category_object_map om,
category_translations t,
categories ctg
where
om.category_id = t.category_id and
ctg.category_id = t.category_id and
ctg.deprecated_p = 'f')
c ON p.item_id = c.object_id,
cr_items i,
pm_project_status s
WHERE
p.project_id = i.live_revision and
s.status_id = p.status_id and (select exists(select 1 from acs_object_party_privilege_map ppm where ppm.object_id = p.project_id and ppm.party_id = -1 and ppm.privilege = 'admin'));
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=4289.71..4304.48 rows=32 width=65) (actual time=41.532..41.532 rows=0 loops=1)
Hash Cond: ("outer".status_id = "inner".status_id)
-> Hash Join (cost=4288.68..4302.97 rows=32 width=69) (actual time=41.410..41.410 rows=0 loops=1)
Hash Cond: ("outer".live_revision = "inner".revision_id)
-> Seq Scan on cr_items i (cost=0.00..11.98 rows=398 width=4) (actual time=0.006..0.856 rows=398 loops=1)
-> Hash (cost=4288.58..4288.58 rows=39 width=77) (actual time=39.722..39.722 rows=0 loops=1)
-> Hash Left Join (cost=4255.16..4288.58 rows=39 width=77) (actual time=39.719..39.719 rows=0 loops=1)
Hash Cond: ("outer".item_id = "inner".object_id)
-> Merge Right Join (cost=4255.13..4288.35 rows=39 width=81) (actual time=39.667..39.667 rows=0 loops=1)
Merge Cond: ("outer".organization_id = "inner".customer_id)
-> Index Scan using organization_id_pk on organizations o (cost=0.00..29.92 rows=1201 width=4) (actual time=0.033..0.033 rows=1 loops=1)
-> Sort (cost=4255.13..4255.23 rows=39 width=85) (actual time=39.630..39.630 rows=0 loops=1)
Sort Key: pm_projects.customer_id
-> Merge Left Join (cost=4027.44..4254.10 rows=39 width=85) (actual time=39.588..39.588 rows=0 loops=1)
Merge Cond: ("outer".item_id = "inner".project_id)
-> Nested Loop (cost=4024.84..4270.01 rows=39 width=85) (actual time=39.585..39.585 rows=0 loops=1)
-> Nested Loop (cost=4023.83..4268.22 rows=39 width=85) (actual time=39.583..39.583 rows=0 loops=1)
-> Merge Join (cost=4023.83..4043.93 rows=39 width=65) (actual time=39.579..39.579 rows=0 loops=1)
Merge Cond: ("outer".item_id = "inner".item_id)
-> Index Scan using cr_items_pk on cr_items i (cost=0.00..18.52 rows=398 width=8) (actual time=0.016..0.016 rows=1 loops=1)
-> Sort (cost=4023.83..4023.93 rows=38 width=61) (actual time=39.559..39.559 rows=0 loops=1)
Sort Key: cr.item_id
-> Hash Join (cost=3992.42..4022.83 rows=38 width=61) (actual time=39.510..39.510 rows=0 loops=1)
Hash Cond: ("outer".revision_id = "inner".project_id)
-> Seq Scan on cr_revisions cr (cost=0.00..27.69 rows=469 width=35) (actual time=0.007..1.031 rows=469 loops=1)
-> Hash (cost=3992.32..3992.32 rows=38 width=26) (actual time=37.637..37.637 rows=0 loops=1)
-> Seq Scan on pm_projects (cost=0.00..3992.32 rows=38 width=26) (actual time=37.634..37.634 rows=0 loops=1)
Filter: (subplan)
SubPlan
-> Result (cost=53.18..53.19 rows=1 width=0) (actual time=0.492..0.494 rows=1 loops=75)
InitPlan
-> Hash Join (cost=51.11..53.18 rows=1 width=0) (actual time=0.482..0.482 rows=0 loops=75)
Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
-> Seq Scan on acs_privilege_descendant_map pdm (cost=0.00..2.05 rows=3 width=13) (actual time=0.122..0.195 rows=1 loops=75)
Filter: ((descendant)::text = 'admin'::text)
-> Hash (cost=51.11..51.11 rows=1 width=9) (actual time=0.268..0.268 rows=0 loops=75)
-> Hash Join (cost=3.46..51.11 rows=1 width=9) (actual time=0.264..0.264 rows=0 loops=75)
Hash Cond: ("outer".grantee_id = "inner".party_id)
-> Nested Loop (cost=0.00..47.59 rows=6 width=13) (actual time=0.052..0.243 rows=8 loops=75)
-> Index Scan using acs_object_context_index_pk on acs_object_context_index c (cost=0.00..17.93 rows=5 width=4) (actual time=0.023..0.050 rows=6 loops=75)
Index Cond: (object_id = $0)
-> Index Scan using acs_permissions_pk on acs_permissions p (cost=0.00..5.92 rows=1 width=17) (actual time=0.014..0.020 rows=1 loops=450)
Index Cond: ("outer".ancestor_id = p.object_id)
-> Hash (cost=3.45..3.45 rows=2 width=4) (actual time=0.078..0.078 rows=0 loops=1)
-> Index Scan using party_member_member_idx on party_approved_member_map pamm (cost=0.00..3.45 rows=2 width=4) (actual time=0.051..0.057 rows=1 loops=1)
Index Cond: (member_id = -1)
-> Index Scan using acs_objects_pk on acs_objects (cost=0.00..5.74 rows=1 width=20) (never executed)
Index Cond: (acs_objects.object_id = "outer".project_id)
-> Materialize (cost=1.01..1.02 rows=1 width=0) (never executed)
-> Seq Scan on cr_text (cost=0.00..1.01 rows=1 width=0) (never executed)
-> Sort (cost=2.60..2.70 rows=43 width=4) (never executed)
Sort Key: pa.project_id
-> Seq Scan on pm_project_assignment pa (cost=0.00..1.43 rows=43 width=4) (never executed)
-> Hash (cost=0.03..0.03 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)
-> Nested Loop (cost=0.00..0.03 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
Join Filter: ("inner".category_id = "outer".category_id)
-> Nested Loop (cost=0.00..0.01 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)
Join Filter: ("inner".category_id = "outer".category_id)
-> Seq Scan on category_translations t (cost=0.00..0.00 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
-> Seq Scan on categories ctg (cost=0.00..0.00 rows=1 width=4) (never executed)
Filter: (deprecated_p = 'f'::bpchar)
-> Seq Scan on category_object_map om (cost=0.00..0.00 rows=1 width=8) (never executed)
-> Hash (cost=1.02..1.02 rows=2 width=4) (actual time=0.057..0.057 rows=0 loops=1)
-> Seq Scan on pm_project_status s (cost=0.00..1.02 rows=2 width=4) (actual time=0.024..0.032 rows=2 loops=1)
Total runtime: 43.013 ms
(65 rows)
tradeadev=#
WHERE
p.project_id = i.live_revision and
s.status_id = p.status_id
and exists(select 1 from acs_object_party_privilege_map ppm where ppm.object_id = p.project_id and ppm.party_id = -1 and ppm.privilege = 'admin');
i.e. no reason for the select there.
The SELECT was a stupid mistake!
Are there any plans to re-write the code in acs_permission__permission_p to utilise the acs_object_party_privilege_map view rather than do the table joins?
Are there any plans to re-write the code in acs_permission__permission_p to utilise the acs_object_party_privilege_map view rather than do the table joins?Not really. the view and the query in the function are pretty much the same -- does not matter much one way or the other...
p { max-width: 55em; }
td div { max-width: 55em; }
which improves things a bit (although I also edited the posts
some). Unfortunately since its all tabled I don't see a
quick way to really fix it. Andrew would you like access to fix it?
What solution was used on the openacs.org Forums before the recent upgrade? If I recall right, it used a (separate) table to make the the headers of each post look nice, but did not have this "one wide post forces all the rest to be super wide also" bug, and it looked nicer overall anyway.
I am ignorant of the details of how the current Forums HTML has been hacked up. I know simply that this bug has been fixed before, therefore it can be fixed again. I also suggest that any graphic design changes which cause this bug, are by definition bad graphic design changes.
Jeff, did you try to replace the CENTER-tag with a DIV. AFAIK CENTER is depreciated.
So if non-preformatted HTML wraps properly the current style's fine with me (I know before Jeff made his change that the non-preformatted HTML was spilling over the right margin, too, and that did suck).
I think Dave's modified forum output to use DIVs on HEAD, where the java-script driven collapse/expand stuff is, also.
If he hasn't done it yet, it's in the plan.
In fact any one post, regardless of whether a particular post is well or poorly formatted, must never break the display or formatting of all the other posts in a thread.
IMO this is an important correctness and usability criteria, the sort of thing that OpenACS is usually good about getting right, and much more important than getting the particular background color or whatever that someone prefers. But people making look and feel changes to OpenACS appear to sometimes completely ignore all such UI correctness concerns, and repeatedly re-introduce this bug. I'd say that's rather embarassing to the project.
If a user mis-formats a post, it would be very nice to have any easy way for a Forums admin (or better yet, any one of many trusted and designated "power users" or "sub-admins" in the community) to fix that one post. But that's a separate issue.
What I have done in the past is take the content out of the big table that surrounds all the posts. I have done this just in the local checkout for openacs.org. Since we want to run OpenACS.org on the same code everyone else has, it should be fixed in OpenACS CVS.
Forum admins DO have an easy way to edit badly formatted posts. They click on the "edit" link attached to each forum post including every reply in every thread. The fact that you don't see it doesn't mean it's not there, it simply means you're not a forum admin.
Jeff's tweak makes things much, much better than before, and is good enough that a real fix is no longer critical, but it's not really the right fix. The old openacs.org site had a correct fix.
Well, I don't see any "edit" link myself. But if it's made available to Admins, that's good.
What works in IE6 and FF1 is another inline style.
pre { white-space: normal }
Does that work for other browsers?
Do you have an account on openacs yet or is anybody else fixing this? Many people use IE.
analyze output then.
In fact, if anything, this combination of fixes now looks even better than the old pre-upgrade openacs.org Forums behavior, which you can see by looking at the problematic poorly-formatted 12th post above - not only does it no longer screw up the display of the rest of the thread, but at least in Mozilla-derived browsers, it itself is now much more readable. (In IE 6.0.x, the text at the top of the 12th post is still too wide, but that's the fault of the way that user submitted that post; it is not a Forums bug. All the other posts look look good in IE.)
So, thank you very much, Jeff! (Hopefully this bug has now been finally slain for good, and won't come back again.)
I don't know if you have seem them, but we had quite some issues with OpenACS permissions when designing the Project/Open permission system. Everything could basicly be boiled down to the fact that we didn't need permission on a per-user level for most of the objects in P/O. Such a per-user and per-object granularity directly leads to a N*M storage complexity (number of users * number of objects) if the matrix matrix is more or less filled (i.e. if it's not a particularly sparse matrix).
So we decided to "tweak" the OpenACS permissions system in order to reduce the storage complexity to N, using a single "magic" object and a number of "privileges". Then we use a fixed number of groups to make the management of the privileges more easy. That did work out very well. We simply don't have to consider optimizing the performance.
The problem are the objects which _do_ need per-user access control, such as projects. These objects required us to introduce a N * M type of permissions. However, we didn't need inheritance here (neither hierarchical groups nor security contexts), so we can use plain optimized query with a _time_ complexity of log(N * M).
Finally, we had to qualify the relationships between member and projects a bit more, because a project manager should have more rights then a plain project member, particularly in terms of filestorage and discussion groups. So we used the acs_rels table to store the N * M matrix and added a custom relationship holding a relationship qualifier such as "project_member" or "full_member" or "software_tester" etc. so that we didn't have to multiply the N * M matrix with a constant factor (for various possible categories).
I know that this design isn't 100% compliant with the best programming practices @ OpenACS, but atleast we're not having any trouble with performance...
Good luck!
Frank
Using groups as you discuss is fine, too.
Your last bit, using ad hoc roles which extend acs-rels, could easily be solved in the permissions system IF object types themselves were objects. You'd then declare each role as an object type, have each acs-rel object of that object type inherit perms from the object type itself. You wouldn't have N*M permission rows to check in this case ...
I don't know if the above is clear but we've talked about "objectifying" object types because various kinds of things can be solved by allowing permissions to be assigned to object types themselves if the code then sets context_id to the object type. Very lightweight in principle...
(making object types objects would help solve other problems too)
thanks for the blessing!
using ad hoc roles which extend acs-rels, could
easily be solved in the permissions system IF object
types themselves were objects.
Well, that would be a bit more difficult, because these ad-hoc roles depend on the project. So one guy can be a Project Manager of one project (and see everything) and be a tester in another one.
There are two other aspects that proved useful:
- We have integrated these ad-hoc roles with our filestorage
module. So we can set file access permissions for a
"tester" or a "full member".
- We also use acs_rels for connecting other business
objects, such companies with 1 or more offices, users
with their companies, invoices with the customer and
the provider etc. So it's a uniform way of connecting
objects, and one guy at La Salle is working on a
graphical object browser (a la "The Brain"):
http://www.kurzweilai.net/brain/frame.html?startThought=Artificial%20Intelligence%20(AI)
Bests,
Frank
So you'd just declare new object types as necessary ...
It might not be as simple as what you're doing, I've have to look more closely, but the point is that the difficulty in scaling a permissions solution doesn't lie in the permissions system itself in this case, but rather flaws in the object model...
