Your benchmarks are meaningless unless you use "exists", in other words ...
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 ...
