Forum OpenACS Development: Re: OpenACS Permissions System - Performance Improvement Work

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!