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

Richard,

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.