No, it's not the tree query, it's the UNION. The acs_party_privilege_map UNIONs up the results of SELECTs that return a huge number of rows. Those selects aren't qualified in any way, they're returning a huge combination of various tables in the system.
You use this view in queries that do qualify the result, of course. Often you're just testing a single object, for instance.
But Postgres first builds the huge UNION - millions of rows, frequently - then performs the rest of the query on that result.
You can see that it's going to be slow because the resultset size grows in proportion to the product of the number of rows in various tables.
I've read some Oracle documentation that indicates that Oracle's no smarter for queries like this.
The replacement all_party_privilege_map - added by aD because of the horrible performance of acs_party_privilege_map, BTW - still contains a UNION but as I mentioned above, it is less evil. It only grows linearly in proportion to the rows in the parties and relations tables.
Linear growth we can handle, N^2 or N^3 we can't.
Do you have time to bundle up a patch with your change and bug fixes? If so I'll get it into the 4.5 release ...
