Forum OpenACS Q&A: Big performance problem or very large numbers :-) ?

If that does not help, please post some details on which pages and queries as slow. Install developer support to get the database query timings and page request times.

PS: We are too detecting that in some case swapping the predicate "exists (subselect)" with "in (subselect)" is many times better. ????

This is an interesting observation. This is often true in Oracle.

But ... in versions of Postgres older than 7.4, the in (subselect) construct was unoptimized. The subselect would be fully evaluated, and then searched linearly to check if the in was satisifed. As you can imagine, this was very very slow for subselects returning many rows. exists was orders of magnitude faster.

AFAIK you're the first person to compare the execution speed of the new optimization in 7.4 with exists on a large set of real data. Can you post some actual numbers for us to look at?

Unfortunately we're still supporting 7.3 (which does not have the optimization) so will be in no hurry to switch from exists to the subselect form but when PG 7.5 comes out, we'll drop 7.3 support and may want to go rewrite some of the more important queries.

Ok.

Here is the difference between two selects called
from admin/site-map with Postgres 7.4.3

This is the original from CVS, using exists
in one subselect
----------------------------------------------
select package_id,
      package_key,
      pretty_name as package_pretty_name,
      apm_package_type__num_parameters(package_key) as parameter_count,
      node_id, url, parent_url, name, root_p, mylevel, object_id,
      directory_p, parent_id, n_children,
      p.instance_name as object_name,
      acs_permission__permission_p(object_id, '3901770', 'admin') as object_admin_p
from apm_packages p join apm_package_types using (package_key) right outer join
    (select n.node_id,
            site_node__url(n.node_id) as url,
            site_node__url(n.parent_id) as parent_url,
            n.name,
            case when exists (select 1 from site_nodes where parent_id = n.node_id) then 1 else 0 end as n_children,
            case when n.node_id = (select site_node__node_id('/', null)) then 1 else 0 end as root_p,
            (tree_level(n.tree_sortkey) - (select tree_level(n2.tree_sortkey) from site_nodes n2 where n2.node_id = (select coalesce('436', site_node__node_id('/', null))))) as mylevel,
            n.object_id,
            n.directory_p,
            n.parent_id
      from site_nodes n, site_nodes n2
      where (n.object_id is null
            or exists (
                select 1 from acs_object_party_privilege_map ppm
                where ppm.object_id = n.object_id
                  and ppm.party_id = '3901770'
                  and ppm.privilege = 'read'))
      and n2.node_id = (select coalesce('436', site_node__node_id('/', null)))
      and n.tree_sortkey between n2.tree_sortkey and tree_right(n2.tree_sortkey)
      and (n.parent_id is null or n.parent_id in (436))) site_map
on site_map.object_id = p.package_id
order by url
;

This is modified by me, only replacing "exists"
by "1 in" inthe same subselect:
----------------------------------------------
select package_id,
      package_key,
      pretty_name as package_pretty_name,
      apm_package_type__num_parameters(package_key) as parameter_count,
      node_id, url, parent_url, name, root_p, mylevel, object_id,
      directory_p, parent_id, n_children,
      p.instance_name as object_name,
      acs_permission__permission_p(object_id, '3901770', 'admin') as object_admin_p
from apm_packages p join apm_package_types using (package_key) right outer join
    (select n.node_id,
            site_node__url(n.node_id) as url,
            site_node__url(n.parent_id) as parent_url,
            n.name,
            case when exists (select 1 from site_nodes where parent_id = n.node_id) then 1 else 0 end as n_children,
            case when n.node_id = (select site_node__node_id('/', null)) then 1 else 0 end as root_p,
            (tree_level(n.tree_sortkey) - (select tree_level(n2.tree_sortkey) from site_nodes n2 where n2.node_id = (select coalesce('436', site_node__node_id('/', null))))) as mylevel,
            n.object_id,
            n.directory_p,
            n.parent_id
      from site_nodes n, site_nodes n2
      where (n.object_id is null
            or 1 in (
                select 1 from acs_object_party_privilege_map ppm
                where ppm.object_id = n.object_id
                  and ppm.party_id = '3901770'
                  and ppm.privilege = 'read'))
      and n2.node_id = (select coalesce('436', site_node__node_id('/', null)))
      and n.tree_sortkey between n2.tree_sortkey and tree_right(n2.tree_sortkey)
      and (n.parent_id is null or n.parent_id in (436))) site_map
on site_map.object_id = p.package_id
order by url
;

Regards,
Agustin

From the additional info Dario posted (one query which itself is very slow), I think bad AOLserver thread settings couldn't possibly be causing all of your problems, but they could be a contributing factor, so you definitely want to double check them, if you haven't already.

select i2.name, tree_level(i2.tree_sortkey) as tree_level
from cr_items i1, cr_items i2
where i2.parent_id <> 0
  and i1.item_id = get_path__item_id
  and i1.tree_sortkey between i2.tree_sortkey and  tree_right(i2.tree_sortkey)
order by tree_level

Could someone who knows approximately what would be reasonable values for thread settings either change the default config file or file a bug and a patch for it?