Reviving the dead again -- in calendar code, query get_viewable_calendar called in /calendar/admin/calendar-preferences is a killer (aside from a strange state in which calendar code is, as there are some unported queries, etc.). It uses calendar__readable_p finction that is basically this:
CREATE FUNCTION calendar__readable_p(
integer,
integer
)
RETURNS boolean
AS 'declare
readable_p__calendar_id alias for $1;
readable_p__party_id alias for $1;
v_readable_p boolean;
begin
select (case count(*)
when 1 then true
else false
) into v_readable_p
from acs_object_party_privilege_map
where party_id = readable_p__party_id
and object_id = readable_p__calendar_id
and privilege = ''calendar_read'';
return v_readable_p;
end;'
LANGUAGE 'plpgsql';
First of all, readable_p__party_id seems better be aliased to $2, not $1. case must be re-written, as this syntax is incorrect. After my wimpy changes it looks thusly:
CREATE FUNCTION calendar__readable_p (
integer,
integer
)
RETURNS boolean AS '
DECLARE
readable_p__calendar_id alias for $1;
readable_p__party_id alias for $2;
v_readable_p boolean;
BEGIN
/*
*
* Using acs_object_party_privilege_map is EVIL -- it takes AGES!
* We only need objects that are labeled as cal_item, hence...
*
*/
select case
when count(*) = 1 then ''t''
else ''f''
end into v_readable_p
from dual where exists (
select 1
from acs_object_party_privilege_map
where party_id = readable_p__party_id
and object_id = readable_p__calendar_id
and privilege = ''calendar_read''
);
RETURN v_readable_p;
END;
' LANGUAGE 'plpgsql';
...and kills OACS outright. A simple select calendar__readable_p(o.object_id, 4683) as readable_p from acs_objects o; (where 4683 is my id) has been running for some 10-20 minutes already and there are only 400 or so objects in acs_objects. FWIW, analyze has just been run...
I'l try to re-write acs_object_party_privilege_map to not use a cascade of UNIONs on views on views on views, and see whether this helps...
